Unable to query database correctly using JDBC

Question

I am trying to update a database using input from user and saving it in jtable, then using jtable I am updating the database, but I am not able to get fetch and update 2nd row in database.

please suggest a solution, Thanks in advance.

        try {

            Class.forName("com.mysql.jdbc.Driver");
            con = myconnection.getConnection();

            String name;

            for (int i = 0; i < jTable2.getRowCount(); i++) {
                name = (String) jTable2.getModel().getValueAt(i, 0);
                String abcd = "select * from medicine where Name=? ";
                stmt = conn.prepareStatement(abcd);

                stmt.setString(1, name);
                rs = stmt.executeQuery();
                if (rs.next()) {

                    name = (String) jTable2.getModel().getValueAt(i, 0);
                    String stock = rs.getString("qty");
                    int nowstock = Integer.parseInt(stock);
                    int qty1 = Integer.parseInt(jTable2.getValueAt(i, 2).toString());

                    int newstock = nowstock - qty1;//Integer.parseInt(jTable2.getValueAt(i, 2).toString());
                    String sqlupdate = "UPDATE medicine SET qty='" + newstock + "'WHERE Name='" + name + "' ";  //
                    stmt = conn.prepareStatement(sqlupdate);
                    stmt.executeUpdate();

                }

            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Bill.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(Bill.class.getName()).log(Level.SEVERE, null, ex);
        }

Can you mention your problem statement in the title like - how to do CRUD with Jtable? Something like that - It will be useful for others to look and provide solutions. — Dlucidone, May 23 '20 at 09:22

score 0 · Accepted Answer · answered May 23 '20 at 08:40

The select serves no purpose, and you can just iterate all names and update directly:

for (int i=0; i < jTable2.getRowCount(); i++) {
    String name = (String) jTable2.getModel().getValueAt(i, 0);
    int qty1 = Integer.parseInt(jTable2.getValueAt(i, 2).toString());
    String update = "UPDATE medicine SET qty = qty - ? WHERE Name = ?";
    PreparedStatement ps = conn.prepareStatement(update);
    ps.setInt(1, qty1);
    ps.setString(2, name);
    ps.executeUpdate();
}

If your JTable happens to have more than say 10 or so names, then a more efficient way to do this would be to use a single update with a WHERE IN clause containing all names which appear in the table, i.e.

UPDATE medicine SET qty = qty - ? WHERE Name IN (...);

There is no "second" row in your medicine table. If the update is failing to update certain rows which you expect, then you should first check that the name matches. — Tim Biegeleisen, May 23 '20 at 08:49

Unable to query database correctly using JDBC

1 Answers1