Skip to content
Advertisement

How can I delete or update a record sql from JTable in Java Swing

I’m coding on a java Swing sql project. I’m stuck at deleting and updating an information record. I can add a new record normally but can’t update or delete one. When I delete, it will notify ‘can’t find column number’. Here is my code and a picture of the error message. Thanks in advance.

enter image description here

private void JtbDeleteActionPerformed(java.awt.event.ActionEvent evt) {                                          
  int row = jtbStudent.getSelectedRow();
    String cell = jtbStudent.getModel().getValueAt(row, 0).toString();
    try {
        stm = cnn.createStatement();
        if (JOptionPane.showConfirmDialog(null, "Are you sure?", "WARNING",
    JOptionPane.YES_NO_OPTION) == JOptionPane.YES_OPTION) {
        stm.execute("DELETE FROM Student where RollNo = " + cell);
        loadTable();
        JOptionPane.showMessageDialog(null, "Delete Successfully");
    } else {
        JOptionPane.showMessageDialog(null, "Delete Unsuccesfully!");
    }

    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }
    finally{
        try {
            stm.close();
            rs.close();
        } catch (Exception e) {
        }
    }
}      

Update button

 private void jbtUpdateActionPerformed(java.awt.event.ActionEvent evt) {       
  try {
        int row = jtbStudent.getSelectedRow();
        String cell = jtbStudent.getModel().getValueAt(row, 0).toString();
        stm = cnn.createStatement();
        String value1 = jtxName.getText();
        String value2 = jtxMark.getText();
        String sql="UPDATE Student set Name='"+value1+"' ,mark='"+value2+"' 
,where RollNo = '" + cell + "'";
        stm.execute(sql);
        System.out.println("Update success!");
        loadTable();
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }

Advertisement

Answer

You are using an invalid SQL-statement. The line you are writing (and sending to the database) is DELETE FROM Student where RollNo = A02.

What you expect (in valid SQL) would be: DELETE FROM Student where RollNo = 'A02'.

Now for simplicity, you could use

stm.execute("DELETE FROM Student where RollNo = '" + cell + "'");

But that would be open to SQL injection, meaning that if the String in the variable cell could read something like "'; drop table student;", your database would drop the students table.

Better use the prepared statement (https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html):

PreparedStatement stm = cnn.prepareStatement("DELETE FROM Student where RollNo = ?");
stm.setString(1, cell);
boolean result = stm.execute();

This way, the String is properly escaped. Please refere to jdbc’s documentation for details.

PreparedStatement – https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html Connection – https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String)

6 People found this is helpful
Advertisement