How to delete a row in JTable and in mysql

Question

Java code

int dialogDelete=JOptionPane.showConfirmDialog(null,"Are you sure you want to delete this Personnel?", "DELETE PERSONNEL",JOptionPane.YES_NO_OPTION);
            if(dialogDelete==JOptionPane.YES_OPTION){      
                DefaultTableModel model = (DefaultTableModel)tbl_personnel.getModel();
                int row = tbl_personnel.getSelectedRow();



                String url = "jdbc:mysql://localhost/mypos";
                String username = "root";
                String password = "";

            try {
                Connection connection = DriverManager.getConnection(url,username,password);
                System.out.println("database connected");
                stmt = connection.createStatement();

                stmt.execute("DELETE FROM Personnel WHERE Id ="+row+"");         
            } catch(Exception e) {
                throw new IllegalStateException("cannot",e);
            }      

                int modelRow = tbl_personnel.convertRowIndexToModel( row );
                model.removeRow( modelRow );


            }

I've edited the code. I can now delete the row in JTable and delete a row in mysql but the row that is deleted in JTable is not the row that is deleted in mysql. I'm not sure what's wrong.

That would depend on the TableModel – MadProgrammer Feb 21 '16 at 03:51 — MadProgrammer, Feb 21 '16 at 03:51

camickr · Answer 1 · 2016-02-21T20:00:49.560

I'm wondering how to delete a row from JTable when I click a row on JTable then press the delete button.

You don't need a MouseListener for this. The row will be selected by default.

You also would NOT use a MouseListener on your button. Instead you need to add an ActionListner to your "Delete Row" button.

Chances are you are using the DefaultTableModel so you can use the removeRow(...) method of the model to remove the row from the table.

The basic code in the ActionListener would be something like:

DefaultTableModel model = (DefaultTableModel)table.getModel();
int row = table.getSelectedRow();
int modelRow = table.convertRowIndexToModel( row );
model.removeRow( modelRow );

So basically you take your current code that displays the JOptionPane and add the above code if the response is YES.

Edit:

stmt.execute("DELETE FROM Personnel WHERE Id ="+row+"");

You can't use the "selected row to control which record you want to delete. The selected row represent the row position in the JTable, not the ID of the row as defined in your database table.

You must have a column in the TableModel that represents the ID of the row. So the code would be something like:

String id = table.getValueAt(row, column);
stmt.execute("DELETE FROM Personnel WHERE Id =" + id + "");

Also it is better to use a PreparedStatement. You are less like to make SQL syntax errors:

String sql = "DELETE FROM Personnel WHERE Id = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString( 1, id );
stmt.executeUpdate();
stmt.close();

Thank you. I've edited my code. I'm having trouble connecting it to mysql when I delete it. It just deletes in the table, my db is not affected. — CamilleTea, Feb 21 '16 at 14:17

How to delete a row in JTable and in mysql

1 Answers1