java - updating mysql table using where parameter from jtable cell

Question

I have a MySQL Table with Columns (itemCode, itemCount), and I have a jTable with Columns (itemCode, itemCount, AddItemCount)

I want to update the MySQL table itemCount with the data on the jTable, but I dont know how to use a where Parameter ( itemCode ) that can change based on the value of itemCode in each jTable row.

in other word, I want to match the databse table itemCode with the jTable itemCode in each Row then updating the itemCount of the matching itemCode.

What I have tried ( which definitely not working at all ) :

    int itemCount, addItemCount, totalItemCount; 
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        con.setAutoCommit(false);
        int rows = tabelDetailDO.getRowCount();
        for(int row=0; row<rows; row++) {
            String SQLupdate = "UPDATE tableItem SET ItemCount=? WHERE ItemCode = '"+(String) tabelDetailDO.getValueAt(row, 0)+"' ";
            ps = con.prepareStatement(SQLupdate);
            itemCount = (int) tabelDetailDO.getValueAt(row, 2);
            addItemCount = (int) tabelDetailDO.getValueAt(row, 3);
            totalItemCount = itemCount + addItemCount;
            ps.setInt(1, totalItemCount);
            ps.addBatch();
         }
        ps.executeBatch();
        con.commit();            
    }
    catch (Exception e) {
        JOptionPane.showMessageDialog(rootPane, e);            
    } 

if I put the SQL Command outside the for loop, it wont get the "row" which is needed as parameter,

while if I put the SQL Command inside the for loop, it will only get the last row updated as the Command will just keep getting repeated in each loop.

it works normally if the where parameter just take one value ( such as from jtextfield ).

Answer 1

I dont know how to use a where Parameter ( itemCode ) that can change based on the value of itemCode in each jTable row.

I don't understand the confusion. You can specify the parameter the same way you specify the parameter for the "ItemCount":

String SQLupdate = "UPDATE tableItem SET ItemCount= ? WHERE ItemCode = ?";
ps = con.prepareStatement(SQLupdate);

for(int row=0; row<rows; row++) 
{
    String itemCode = (String)tabelDetailDO.getValueAt(row, 0);
    itemCount = (int) tabelDetailDO.getValueAt(row, 2);
    addItemCount = (int) tabelDetailDO.getValueAt(row, 3);
    totalItemCount = itemCount + addItemCount;
    ps.setInt(1, totalItemCount);
    ps.setString(2, itemCode);
    ps.addBatch();
}

Note, I have never used a batch update so first try getting the logic working without the batch, so you will need to execute the update each time in the loop. Yes I know it is not efficient but you are just testing the SQL.

Then When that works you try using the batch update.