java -update database by editing cells not working?

Question

I'm having trouble updating data from the database by editing / changing cell values ​​and clicking on the update button, but he can not change the database that I changed to the cell. it's still the same into the last value.

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 the SQL for the where clause?

Just use a parameter like to you do for the other values. It will keep the SQL simpler:

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

Answer 2

For CRUD operations on a data set, it is nice to use an intermediate table. This avoids the large number of queries being placed on large data sets.

Before giving my proposal to solve the problem, I would like to point out some remarks I have on the structure of the database:

1. The total field is obviously a calculated field. Such information is not good to put in the database. It is calculated upon request.
2. The whole set of data is obviously part of a document (invoice). So, there must be a field in the database that uniquely identifies the document to which the data relate.

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

This is the DDL of the table on which the bottom code snippet runs

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 makes CRUD operations on a data set using the document id 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 that demonstrates how the code above works.

Answer 3

It may be that you intended to use index instead of row in the WHERE clause?

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