CODEWITHSUNDEEP
javaswingjdbcjtable
Herb21
Herb21

Reputation: 365

Is to iterate through a jTable and pass column values to an sql query in java

I have a jTable with 4 columns and 6 rows. i want to iterate thru the rows picking up the values of column index0 which is my ID column and passing it to a count sql query. i have written the below code which is not working because i haven't figured out how to pass the columns values after iterating through the table.

can someone please let me know what am doing wrong on my code please.

for (int row = 0; row > jTable2.getRowCount(); row++){
        for (int col =0; col > jTable2.getColumnCount(); col ++)
        try{
            DefaultTableModel model = (DefaultTableModel)jTable2.getModel();
            String selected = model.getValueAt(row, col+1).toString();
            String sql = "select COUNT(COURSEBOOKED) from APP.BOOKCOURSE where COURSEBOOKED = '"+selected+"'"; 
            try(Connection con = DriverManager.getConnection("jdbc:derby:MTD","herbert","elsie1*#");
                PreparedStatement pst = con.prepareStatement(sql);) {
                    ResultSet rs = pst.executeQuery();
                    while(rs.next()){
                        String Sum = rs.getString("COUNT(COURSEBOOKED)");
                        System.out.println(Sum);
                        if (rs.wasNull()){
                            System.out.println("No record found");
                        }
                    }
            }
            catch(SQLException e){
            }
        }
        catch(Exception e){
        }
    }

This is the final code that came up with after the changes.

        String sql = "select COUNT(COURSEBOOKED) as count from APP.BOOKCOURSE where COURSEBOOKED =?";
    try(Connection con = DriverManager.getConnection("jdbc:derby:MTD","herbert","elsie1*#");
            PreparedStatement pst = con.prepareStatement(sql);){
                 for(int row = 0; row < jTable2.getRowCount(); row++){
                        DefaultTableModel model = (DefaultTableModel)jTable2.getModel();
                        String selected = model.getValueAt(row, 1).toString();
                        pst.setString(1, selected);
                        try(ResultSet rs = pst.executeQuery();){
                             while (rs.next()){
                                String Sum = rs.getString("count");
                                System.out.println(Sum);
                             }
                        }
                    }
                }
    catch(SQLException e){
        JOptionPane.showMessageDialog(this, e);
    }

Which brings me to my next question. AM not sure whether i should start a new thread for it or continue on this one. My challenge is that i now want to append an addition column to the existing 4 columns on the current jTable2 and display the values of the above query. to add a new column i have used this code,

TableColumn c = new TableColumn();
c.setHeaderValue("Training accomplished");
model.addColumn(c);

This adds the column but populates its with values from column index0. how do i get the new added column to be populated by the values held in Sum from the query above.

Upvotes: 1

Views: 5321

Answers (1)

Aakash
Aakash

Reputation: 2109

You should use something like below. Note that I haven't tested this code so far, so you might need to debug it. Also check the comments on your question.

String sql = "select COUNT(COURSEBOOKED) as count from APP.BOOKCOURSE where COURSEBOOKED = ?"; 
try(
   Connection con = DriverManager.getConnection("jdbc:derby:MTD","herbert","elsie1*#");
   PreparedStatement pst = con.prepareStatement(sql);){
   for (int row = 0; row < jTable2.getRowCount(); row++){
        DefaultTableModel model = (DefaultTableModel)jTable2.getModel();
        String selected = model.getValueAt(row, 0).toString();
        pst.setString(1, selected);            
        ResultSet rs = pst.executeQuery();
        while(rs.next()){
            String Sum = rs.getString("count");
            System.out.println(Sum);
            if (rs.wasNull()){
                System.out.println("No record found");
            }
        }
    }
}
catch(SQLException e){
}
catch(Exception e){
}

Upvotes: 1

Related Questions