Java-Swing: Retrieve and update details in DB

Question

I am a beginner, I am developing a swing desktop application where there are some CRUD functions. While my insertion and deletion are working the update and retrieval is not.

These are the service methods:

  public void select(){
        String sql = "SELECT num_of_working_days FROM working_days_and_hours";
        
         try {
            connection = SQLite_Connection.connect();
            stmt = connection.createStatement();
             resultSet = stmt.executeQuery(sql);
            System.out.println("DB status: "+ resultSet);
        } catch (Exception ex) {
            System.out.println(ex.toString());
            //Logger.getLogger(Services.class.getName()).log(Level.SEVERE, null, ex);
        }finally {      
                 // Services.colsedConnections();
        }  
    }
    
   
     
     public void update(int id, String num) {
        String sql = "UPDATE working_days_and_hours SET num_of_working_days = '"+num+"' WHERE id = '"+id+"'";

        try {
            connection = SQLite_Connection.connect();
            preparedStatement = connection.prepareStatement(sql);
            
            preparedStatement.setInt(1, id);
            preparedStatement.setString(2, num);

            preparedStatement.executeUpdate();
            
            System.out.println("DB status: "+ preparedStatement);
            
        } catch (Exception ex) {
            System.out.println(ex.toString());
            //Logger.getLogger(Services.class.getName()).log(Level.SEVERE, null, ex);
        }finally {      
                 // Services.colsedConnections();
        }  
    }
        

I simply want to retrieve a value to a text field and update the value in a text field.

So far this is how I tried to implement it:

   private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {   
//here I have set id as 1 for the sake of testing                                     
            numberOfDays.update(1, jTextField1.getText());
    }   

   private void jTextField2ActionPerformed(java.awt.event.ActionEvent evt) {                                            
        jTextField2.setText(numberOfDays.select());
    }   

But for the update, I get ArrayIndexOutBounds error. For the retrieval, I get void not allowed.

Answer 1

String sql = "UPDATE working_days_and_hours SET num_of_working_days = '"+num+"' WHERE id = '"+id+"'";

That is not how you build the SQL for a PreparedStatement. The point of the PreparedStatement is add a "?" as a token and then the later replace the token with valid data.

This makes the SQL easier to code and read and will reduce the chance of syntax errors.

The format for a PreparedStatement is something like:

String sql = "UPDATE Page SET Title = ? WHERE Name = ?";

PreparedStatement stmt = connection.prepareStatement(sql);

stmt.setString( 1, title );
stmt.setString( 2, name );
stmt.executeUpdate();
stmt.close();

where "title" and "name" are variables containing your data.

So this is not a Swing issue. First get the SQL working with hardcoded data. Then worry about getting the data from a text field or other Swing component.