CODEWITHSUNDEEP
javasqljdbc
der57
der57

Reputation: 11

JAVA & SQL database save changes

I am working on Java GUI application which connects to SQL database on localhost (I use XAMPP). When I change some entry, for example Age, I click on "Save changes", it is saved and changes are done in SQL database, but when I click on ">" or "<" to view next or previous person and then go back to the person, where I did changes, every entry is without changes in its initial state. But when I close the application and reopen it, all the changes which I made are done. This is part of the code where is mistake, I think. Thank you.

private void jButtonSaveChangesActionPerformed(java.awt.event.ActionEvent evt) {                                                 
            try {                                                 
                Statement stmt = con.createStatement();
                try {
                    String query1 = "UPDATE list1 SET " +
                            "name ='" + jTextFieldName.getText() + "', " +
                            "surname ='" + jTextFieldSurname.getText() + "', " +
                            "age ='" + jTextFieldAge.getText() + "' " +
                            "WHERE ID = " + jLabelActualID.getText();
                    stmt.executeUpdate(query1);
                } catch (Exception e) {
                    System.err.println(e);
                }
            } catch (Exception e) {
              System.err.println(e);
            }
    }

Picture of application:

enter image description here

Upvotes: 1

Views: 847

Answers (1)

Joop Eggen
Joop Eggen

Reputation: 109547

You are not closing, which can be done more safe and automatically with try-with-resources.

This means a commit might not have happened yet. There is an autocommit setting too.

        String query1 = "UPDATE list1 SET " +
                        "name = ?, " +
                        "surname = ?, " +
                        "age = ? " +
                        "WHERE ID = ?";
        try (PreparedStatement stmt = con.prepareStatement(query1)) { // Closes stmt.
            stmt.setString(1, jTextFieldName.getText());
            stmt.setString(2, jTextFieldSurname.getText());
            stmt.setInt(3, Integer.parseInt(jTextFieldAge.getText()));
            stmt.setString(4, jLabelActualID.getText());
            int updateCount = stmt.executeUpdate();
         } catch (SQLException | NumberFormatException e) {
             System.err.println(e);
         }

The same may hold (or may not hold) for the SQL connection.

Also one should use a PreparedStatement for security (SQL injection) and type safeness / escaping of backslash, quote in strings. As you see it is even more readable.

Another case is a second application accessing the database: it can use its own cache, thereby be a bit outdated.

Upvotes: 1

Related Questions