CODEWITHSUNDEEP
javaswingjtable
kegs Production
kegs Production

Reputation: 67

java -update database by editing cells not working?

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);
        }
    }

Upvotes: 5

Views: 313

Answers (4)

mr mcwolf
mr mcwolf

Reputation: 2850

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.

Upvotes: 2

Enjy
Enjy

Reputation: 255

assuming you have multiple row selected, if your id 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 like this example

Upvotes: 1

Jim Newpower
Jim Newpower

Reputation: 116

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;

Upvotes: 1

camickr
camickr

Reputation: 324098

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);

Upvotes: 6

Related Questions