Java -update database editing cells not working?

Question

I am having problems updating data from a database by editing / changing cell values ​​and clicking the refresh button, but it cannot change the database that I changed to a cell. he is the same in the last sense.

here is my code:

public void directlyup() { int col=tablesample.getSelectedColumn(); int row=tablesample.getSelectedRow(); int index; index = Integer.parseInt(tablesample.getModel().getValueAt(row, 0).toString()); try { String sql ="UPDATE invoice SET description = ?,qty = ?,unitprice = ?" + ",total = ? WHERE id ="+row; pst = conn.prepareStatement(sql); pst.setString(1, (String) tablesample.getValueAt(row, 1)); pst.setString(2, (String) tablesample.getValueAt(row, 2)); pst.setString(3, (String) tablesample.getValueAt(row, 3)); pst.setString(4, (String) tablesample.getValueAt(row, 4)); pst.execute(); JOptionPane.showMessageDialog(null, "Successfully Updated"); } catch (Exception e) { JOptionPane.showMessageDialog(null, e); } } 

Answer 1

 String sql ="UPDATE invoice SET description = ?,qty = ?,unitprice = ?" + ",total = ? WHERE id ="+row; 

Why are you trying to embed a variable in SQL for the where clause?

Just use the parameter as for other values. This will simplify SQL:

 String sql = "UPDATE invoice SET description = ?, qty = ?, unitprice = ?, total = ? WHERE id = ?"; ... pst.set???(5, row); 

Answer 2

For CRUD operations in a dataset, it is useful to use a staging table. This avoids the large number of queries placed on large data sets.

Before giving my suggestion to solve the problem, I would like to indicate some comments on the database structure:

• The total field is obviously a computed field. Such information is not suitable for placement in the database. It is calculated on request.
• The entire data set, obviously, is part of the document (account). Thus, the database should have a field that uniquely identifies the document to which the data relates.

In addition, I want to say that such decisions are made for a specific database. In this case, my solution is with mysql .

This is the DDL of the table on which the snippet is running.

 CREATE TABLE `invoice` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `invoice_id` int(10) unsigned NOT NULL, `description` varchar(255) DEFAULT NULL, `qty` double DEFAULT NULL, `unitprice` double DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB 

And this is the code that does CRUD operations in the data set using the document identifier key ( invoce_id ).

 public boolean save(long invoice_id, List<Invoice> list) throws SQLException { try(Connection connection = getConnection()) { try { connection.setAutoCommit(false); String query = "create temporary table if not exists `invoice_tmp` (" + "`id` int(10) unsigned NOT NULL," + "`description` varchar(255) DEFAULT NULL," + "`qty` double DEFAULT NULL," + "`unitprice` double DEFAULT NULL)"; connection.createStatement().executeUpdate(query); query = "insert into `invoice_tmp` values (?, ?, ?, ?)"; PreparedStatement statement = connection.prepareStatement(query); for(Invoice invoice: list) { statement.setLong(1, invoice.getId()); statement.setString(2, invoice.getDescription()); statement.setDouble(3, invoice.getQty()); statement.setDouble(4, invoice.getUnitPrice()); statement.addBatch(); } statement.executeBatch(); statement.close(); query = "delete invoice from invoice " + "left join invoice_tmp on (invoice.id = invoice_tmp.id) " + "where invoice_id = ? and invoice_tmp.id is null"; statement = connection.prepareStatement(query); statement.setLong(1, invoice_id); statement.executeUpdate(); statement.close(); query = "update `invoice` " + "join `invoice_tmp` using (`id`) " + "set " + "`invoice`.description = `invoice_tmp`.description, " + "`invoice`.qty = `invoice_tmp`.qty, " + "`invoice`.unitprice = `invoice_tmp`.unitprice"; connection.createStatement().executeUpdate(query); query = "insert into `invoice` (`invoice_id`, `description`, `qty`, `unitprice`) " + "select ? as `invoice_id`, `description`, `qty`, `unitprice` from `invoice_tmp` where `id` = 0"; statement = connection.prepareStatement(query); statement.setLong(1, invoice_id); statement.executeUpdate(); statement.close(); connection.createStatement().executeUpdate("drop table if exists `invoice_tmp`"); connection.commit(); return true; } catch (Exception e) { connection.rollback(); throw e; } } } 

this is a test project demonstrating how this code works.

Answer 3

Perhaps you used an index instead of a row in a WHERE clause?

 String sql ="UPDATE invoice SET description = ?,qty = ?,unitprice = ?" + ",total = ? WHERE id ="+index; 

Answer 4

provided that you select multiple lines if your identifier is int:

  int[]rows=tablesample.getSelectedRows(); String sql="UPDATE invoice SET description = ?,qty = ?,unitprice = ?" + ",total = ? WHERE id =?"; try{ PreparedStatement pst = conn.prepareStatement(sql); for(int currentRow:rows){ pst.setString(1, (String) tablesample.getValueAt(currentRow, 1)); pst.setString(2, (String) tablesample.getValueAt(currentRow, 2)); pst.setString(3, (String) tablesample.getValueAt(currentRow, 3)); pst.setString(4, (String) tablesample.getValueAt(currentRow, 4)); pst.setInt(5, currentRow); pst.executeUpdate(); } JOptionPane.showMessageDialog(null, "Successfully Updated"); } catch (Exception e) { JOptionPane.showMessageDialog(null, e); } finally{ try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } 

for best performance you can use executeBatch, for example example