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 ).
See Question&Answers more detail:os