PreparedStatement Delete in Java

Question

I have written a code to delete a record in a MySQL database from my Java Program. It compiles and runs it successfully, but the record is still in the datbase. Any suggestions?

JButton btnDelete = new JButton("Delete");
btnDelete.addActionListener(new ActionListener() {

    public void actionPerformed(ActionEvent arg0) {

        try {

            Object[] options = {"Yes", "No"};
            Component form = null;
            int n = JOptionPane.showOptionDialog(form, "Do you like to delete the record for Student ID: " +
                    textField_16.getText() + " ?", "Exit Confirmation", JOptionPane.YES_NO_CANCEL_OPTION, JOptionPane.QUESTION_MESSAGE, null, options, options);

            if(n == JOptionPane.YES_OPTION) {

                String mock = textField_16.getText();   

                String sql = "DELETE FROM mockexam WHERE MockID =?";
                PreparedStatement prest = con.prepareStatement(sql);
                prest.setString(1, mock);
                int val = prest.executeUpdate();
                JOptionPane.showMessageDialog(frmDeleteMock, "The record has been deleted successfully.");
            }
        } 
        catch (SQLException e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(frmDeleteMock, "Record couldn't be deleted. Please try again.");
        }       
    }
});

btnDelete.setBounds(45, 235, 89, 23);
panel_1.add(btnDelete);

Answer 1

You don't indicate what kind of DB you are running against (innoDB or MyISAM, etc) but if you are running against InnoDB, you need to have a commit() statement to commit the transaction after your executeUpdate(). Chances are you have something in your log that indicates that your transaction has been rolled back.

try {
    Object[] options = {"Yes", "No"};
    Component form = null;
    int n = JOptionPane.showOptionDialog(form, 
            "Do you like to delete the record for Student ID: " + textField_16.getText() + " ?", 
            "Exit Confirmation", JOptionPane.YES_NO_CANCEL_OPTION, JOptionPane.QUESTION_MESSAGE, null, options, options);

    if(n == JOptionPane.YES_OPTION) {
        String sql = "DELETE FROM mockexam WHERE MockID =?";
        PreparedStatement prest = con.prepareStatement(sql);
        prest.setString(1, "MockID");
        prest.executeUpdate();
        con.commit();
        JOptionPane.showMessageDialog(frmDeleteMock, "The record has been deleted successfully.");
    }
} 

catch (SQLException e) {
    e.printStackTrace();
    JOptionPane.showMessageDialog(frmDeleteMock, "Record couldn't be deleted. Please try again.");
}

You're also missing connection an PreparedStatement close() calls in a finally{} block to ensure that you don't have any leaks in your code.