Why when I clicked the update button, all data in database changes?

I am making a Library System where the user can filter the data in database (using SQLite). As of now, I am having a problem updating my database. When I select one item in the database then update it, all the data changes. How can I change only the selected item?

Here is my code:

package librarySystemPackage;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.swing.JOptionPane;
import net.proteanit.sql.DbUtils;

public class LibrarySystem extends javax.swing.JFrame {

    Connection conn = null;
    PreparedStatement pst = null;
    ResultSet rs = null;

    public LibrarySystem() {
        initComponents();
        conn = DbConnection.ConnecrDb();
        updateTable();
    }

    private void updateTable() {
        String sql = "select * from LibrarySystemDatabase";
    try {
        pst = conn.prepareStatement (sql);
        rs = pst.executeQuery();
        tblTable.setModel(DbUtils.resultSetToTableModel (rs));
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    } finally {
        try {
            rs.close();
            pst.close();
        } catch (Exception e) {   
        }
    }
} 

    private void clearFields() {
        txtNo.setText(null);
        txtTitle.setText(null);
        txtAuthor.setText(null);
        txtGenre.setText(null);
        txtLexile.setText(null);
        txtPoints.setText(null);
    }

    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">                          
    private void initComponents() {

        jLabel1 = new javax.swing.JLabel();
        jLabel2 = new javax.swing.JLabel();
        jLabel3 = new javax.swing.JLabel();
        jLabel4 = new javax.swing.JLabel();
        jLabel5 = new javax.swing.JLabel();
        jLabel6 = new javax.swing.JLabel();
        txtTitle = new javax.swing.JTextField();
        txtNo = new javax.swing.JTextField();
        txtAuthor = new javax.swing.JTextField();
        txtGenre = new javax.swing.JTextField();
        txtLexile = new javax.swing.JTextField();
        txtPoints = new javax.swing.JTextField();
        btnInsert = new javax.swing.JButton();
        jScrollPane1 = new javax.swing.JScrollPane();
        tblTable = new javax.swing.JTable();
        btnDeselect = new javax.swing.JButton();
        btnUpdate = new javax.swing.JButton();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        jLabel1.setFont(new java.awt.Font("Tahoma", 0, 20)); // NOI18N
        jLabel1.setText("Title:");

        jLabel2.setFont(new java.awt.Font("Tahoma", 0, 20)); // NOI18N
        jLabel2.setText("Accession Number:");

        jLabel3.setFont(new java.awt.Font("Tahoma", 0, 20)); // NOI18N
        jLabel3.setText("Author:");

        jLabel4.setFont(new java.awt.Font("Tahoma", 0, 20)); // NOI18N
        jLabel4.setText("Lexile:");

        jLabel5.setFont(new java.awt.Font("Tahoma", 0, 20)); // NOI18N
        jLabel5.setText("Genre:");

        jLabel6.setFont(new java.awt.Font("Tahoma", 0, 20)); // NOI18N
        jLabel6.setText("Points:");

        btnInsert.setFont(new java.awt.Font("Tahoma", 0, 24)); // NOI18N
        btnInsert.setText("Insert");
        btnInsert.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnInsertActionPerformed(evt);
            }
        });

        tblTable.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null}
            },
            new String [] {
                "Title 1", "Title 2", "Title 3", "Title 4"
            }
        ));
        tblTable.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                tblTableMouseClicked(evt);
            }
        });
        tblTable.addKeyListener(new java.awt.event.KeyAdapter() {
            public void keyReleased(java.awt.event.KeyEvent evt) {
                tblTableKeyReleased(evt);
            }
        });
        jScrollPane1.setViewportView(tblTable);

        btnDeselect.setFont(new java.awt.Font("Tahoma", 0, 24)); // NOI18N
        btnDeselect.setText("Deselect");
        btnDeselect.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnDeselectActionPerformed(evt);
            }
        });

        btnUpdate.setFont(new java.awt.Font("Tahoma", 0, 24)); // NOI18N
        btnUpdate.setText("Update");
        btnUpdate.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnUpdateActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGap(50, 50, 50)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(jLabel6, javax.swing.GroupLayout.PREFERRED_SIZE, 170, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(txtPoints, javax.swing.GroupLayout.PREFERRED_SIZE, 187, javax.swing.GroupLayout.PREFERRED_SIZE))
                            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                                .addComponent(jLabel4, javax.swing.GroupLayout.PREFERRED_SIZE, 170, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(txtLexile, javax.swing.GroupLayout.PREFERRED_SIZE, 187, javax.swing.GroupLayout.PREFERRED_SIZE))
                            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                                .addComponent(jLabel5, javax.swing.GroupLayout.PREFERRED_SIZE, 170, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(txtGenre, javax.swing.GroupLayout.PREFERRED_SIZE, 187, javax.swing.GroupLayout.PREFERRED_SIZE))
                            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                                .addComponent(jLabel3, javax.swing.GroupLayout.PREFERRED_SIZE, 170, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(txtAuthor, javax.swing.GroupLayout.PREFERRED_SIZE, 187, javax.swing.GroupLayout.PREFERRED_SIZE))
                            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                                .addComponent(jLabel2, javax.swing.GroupLayout.PREFERRED_SIZE, 175, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(txtNo, javax.swing.GroupLayout.PREFERRED_SIZE, 187, javax.swing.GroupLayout.PREFERRED_SIZE))
                            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                                .addComponent(jLabel1, javax.swing.GroupLayout.PREFERRED_SIZE, 170, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addGap(26, 26, 26)
                                .addComponent(txtTitle, javax.swing.GroupLayout.PREFERRED_SIZE, 187, javax.swing.GroupLayout.PREFERRED_SIZE)))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 42, Short.MAX_VALUE)
                        .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 548, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addGroup(layout.createSequentialGroup()
                        .addGap(16, 16, 16)
                        .addComponent(btnInsert, javax.swing.GroupLayout.PREFERRED_SIZE, 123, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(btnDeselect)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(btnUpdate)
                        .addGap(0, 0, Short.MAX_VALUE)))
                .addGap(28, 28, 28))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(122, 122, 122)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jLabel2, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(txtNo, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jLabel1, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(txtTitle, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jLabel3, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(txtAuthor, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addGap(6, 6, 6)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jLabel5, javax.swing.GroupLayout.PREFERRED_SIZE, 37, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(txtGenre, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jLabel4, javax.swing.GroupLayout.PREFERRED_SIZE, 37, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(txtLexile, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jLabel6, javax.swing.GroupLayout.PREFERRED_SIZE, 37, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(txtPoints, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE)))
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 259, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(28, 28, 28)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(btnDeselect)
                    .addComponent(btnInsert)
                    .addComponent(btnUpdate))
                .addContainerGap(224, Short.MAX_VALUE))
        );

        pack();
    }// </editor-fold>                        

    private void btnInsertActionPerformed(java.awt.event.ActionEvent evt) {                                          
        String sql = "INSERT INTO LibrarySystemDatabase(txtNo, txtTitle, txtAuthor, txtGenre, txtLexile, txtPoints) VALUES(?,?,?,?,?,?)";
        try {
            pst = conn.prepareStatement (sql);
            pst.setString(1, txtNo.getText());
            pst.setString(2, txtTitle.getText());
            pst.setString(3, txtAuthor.getText());
            pst.setString(4, txtGenre.getText());
            pst.setString(5, txtLexile.getText());
            pst.setString(6, txtPoints.getText());
            pst.execute();
            JOptionPane.showMessageDialog(null, "Inserted Successfully");
            updateTable();
            clearFields();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);
        } finally {
            try {
                rs.close();
                pst.close();
            } catch (Exception e) {
            }
        }
    }                                         

    private void tblTableMouseClicked(java.awt.event.MouseEvent evt) {                                      
       int row = tblTable.getSelectedRow();
       String selection = tblTable.getModel().getValueAt(row, 0).toString();
       String sql = "select * from LibrarySystemDatabase where txtNo = " + selection;
       try {
           pst = conn.prepareStatement (sql);
           rs = pst.executeQuery();
           if(rs.next()) {
               txtNo.setText(rs.getString("txtNo"));
               txtTitle.setText(rs.getString("txtTitle"));
               txtAuthor.setText(rs.getString("txtAuthor"));
               txtGenre.setText(rs.getString("txtGenre"));
               txtLexile.setText(rs.getString("txtLexile"));
               txtPoints.setText(rs.getString("txtPoints"));
           }
       } catch (Exception e) {
           JOptionPane.showMessageDialog(null, e);
       } finally {
           try {
               rs.close();
               pst.close();
           } catch (Exception e) {
           }
       }
    }                                     

    private void tblTableKeyReleased(java.awt.event.KeyEvent evt) {                                     
         int row = tblTable.getSelectedRow();
       String selection = tblTable.getModel().getValueAt(row, 0).toString();
       String sql = "select * from LibrarySystemDatabase where txtNo = " + selection;
       try {
           pst = conn.prepareStatement (sql);
           rs = pst.executeQuery();
           if(rs.next()) {
               txtNo.setText(rs.getString("txtNo"));
               txtTitle.setText(rs.getString("txtTitle"));
               txtAuthor.setText(rs.getString("txtAuthor"));
               txtGenre.setText(rs.getString("txtGenre"));
               txtLexile.setText(rs.getString("txtLexile"));
               txtPoints.setText(rs.getString("txtPoints"));
           }
       } catch (Exception e) {
           JOptionPane.showMessageDialog(null, e);
       } finally {
           try {
               rs.close();
               pst.close();
           } catch (Exception e) {
           }
       }
    }                                    

    private void btnDeselectActionPerformed(java.awt.event.ActionEvent evt) {                                            
       clearFields();
       updateTable();
    }                                           

This is my code for the update button

    private void btnUpdateActionPerformed(java.awt.event.ActionEvent evt) {                                          
        String sql = "update LibrarySystemDatabase set"
                + " txtNo = '"+txtNo.getText()+"',"
                + " txtTitle = '"+txtTitle.getText()+"',"
                + " txtAuthor = '"+txtAuthor.getText()+"',"
                + " txtGenre = '"+txtGenre.getText()+"',"
                + " txtLexile = '"+txtLexile.getText()+"',"
                + " txtPoints = '"+txtPoints.getText()+"'";
        try {
            pst = conn.prepareStatement (sql);
            pst.execute();
            JOptionPane.showMessageDialog(null, "Record Updated");
            updateTable();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);
        } finally {
            try {
                rs.close();
                pst.close();
            } catch (Exception e) {
            }
        }
    }                                         

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {



        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new LibrarySystem().setVisible(true);
            }
        });
    }

    // Variables declaration - do not modify                     
    private javax.swing.JButton btnDeselect;
    private javax.swing.JButton btnInsert;
    private javax.swing.JButton btnUpdate;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JLabel jLabel6;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JTable tblTable;
    private javax.swing.JTextField txtAuthor;
    private javax.swing.JTextField txtGenre;
    private javax.swing.JTextField txtLexile;
    private javax.swing.JTextField txtNo;
    private javax.swing.JTextField txtPoints;
    private javax.swing.JTextField txtTitle;
    // End of variables declaration                   
}

Thanks

Solution 1:

Your update LibrarySystemDatabase SQL statement has no WHERE clause, so every row is updated to the same values.

Your code should be:

private void btnUpdateActionPerformed(java.awt.event.ActionEvent evt) {                                          
    String sql = "UPDATE LibrarySystemDatabase"
                 + " SET txtTitle = ?"
                    + ", txtAuthor = ?"
                    + ", txtGenre = ?"
                    + ", txtLexile = ?"
                    + ", txtPoints = ?"
               + " WHERE txtNo = ?";
    try (PreparedStatement pst = conn.prepareStatement(sql)) {
        pst.setString(1, txtTitle.getText());
        pst.setString(2, txtAuthor.getText());
        pst.setString(3, txtGenre.getText());
        pst.setString(4, txtLexile.getText());
        pst.setString(5, txtPoints.getText());
        pst.setString(6, txtNo.getText());
        int count = pst.executeUpdate();
        JOptionPane.showMessageDialog(null, count + " Records Updated");
        updateTable();
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }
}

Changes made:

• Use WHERE clause on the txtNo column, since that seems to be the primary key, according to your SELECT statements.

• Use try-with-resources for simpler handling of close().

• Call executeUpdate() to get update count, and show it.

• Use ? parameter markers, to prevent SQL injection attacks and SQL syntax errors, just like you already did for the INSERT statement.