mysql sqlExceptiion Error

Question

I will begin by saying that I googled the problem and found explanation but still can not fix the error...:s I'm getting java.sql.SQLException: Operation not allowed afterResultSetclosed (what I understand is that I should create a new statement and new ResultSet and like said still can't make it work - if that is now right way of thinking) would appreciate all the help.

public class IdPanel extends JPanel {

    private JLabel idLabel;
    private JTextField idField;
    private JButton okBtn;
    private JButton newEmployeBtn;
    private NewEmployeDialog newEmployeDialog;

    private Connection myConn;
    private Statement myStmt;
    private ResultSet myRs;

    public IdPanel() throws SQLException {

        idLabel = new JLabel("ID:");
        idField = new JTextField(12);
        okBtn = new JButton("OK");
        newEmployeBtn = new JButton("New Employe...");

        myConn = DriverManager.getConnection(
                "jdbc:mysql://*********", ********);
        myStmt = myConn.createStatement();
        myRs = myStmt.executeQuery("select * from employe");

        try {
            newEmployeDialog = new NewEmployeDialog();
        } catch (SQLException e1) {
            System.out.println("check IdPanel-->NewEmployeDialog");
        }

        setLayout(new GridBagLayout());
        setGc();

        okBtn.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                String id = idField.getText();
                try {
                    while (myRs.next()) {
                        String idImp = myRs.getString(1);
                        if (id.equals(idImp)) {
                            System.out.println(id);

                            try {
                                myStmt.executeUpdate("update employe set phone =99321 where id=346");
                            } catch (SQLException e1) {
                                e1.printStackTrace();
                            }
                        }
                        idField.setText("");
                    }
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        });

        myConn.close();
        myStmt.close();
        myRs.close();
    }

MadProgrammer · Answer 1 · 2015-05-25T21:54:30.090

0

You are trying to access myRs in your ActionListener, but myRs has already been closed.

Swing is an event driven enviroment, by the time the ActionListener is notified of the ok button's event, the close statements have already been executed

Instead of creating the connection and exciting the query in the main block, move it to the AdtionListener

I'd also encourage you to use PreparedStatements, see Using Prepared Statements for more details

okBtn.addActionListener(new ActionListener() {
    public void actionPerformed(ActionEvent e) {
        String id = idField.getText();
        try (Connection myConn = DriverManager.getConnection("jdbc:mysql://*********",  * * * * * * * *)) {
            try (PreparedStatement stmt = myConn.prepareStatement("update employe set phone=? where id=?")) {
                stmt.setInt(1, 99321);
                stmt.setInt(2, Integer.parseInt(id));
                stmt.executeUpdate()
                idField.setText("");
            }
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }
});

Also take a look at The try-with-resources Statement for better resource management ideas

edited May 25 '15 at 21:54

answered May 25 '15 at 21:47

MadProgrammer

343,457
22
230
366

Perffect -thank you for help, explenation (event driven enviroment) and tips (prepared Statements) (y) – Pawel B May 25 '15 at 22:06
idField.setText(""); should be always inside invokeLater, doesn't matter for Java4/5 probably :-) – mKorbel May 26 '15 at 05:50
@mKorbel Why? It's within the context of the `ActionListener` which is called from within the EDT... – MadProgrammer May 26 '15 at 05:51
you are still in risk that after 15/20(max -30) seconds your EDT go away in Java7/8, doesn't matter if is called from Listener with reason to freeze (intentionally) GUI, I think that series of bugs in Java6 reason for SecondaryLoop is there in Java7, – mKorbel May 26 '15 at 06:00
@mKorbel How can the EDT go away, when the code is been executed within it's context? Is this a deadlock feature? – MadProgrammer May 26 '15 at 06:02
I'm think that is about exceptionhttp://stackoverflow.com/questions/7053865/cant-get-arrayindexoutofboundsexception-from-future-and-swingworker-if-threa from RepaintManagerhttp://stackoverflow.com/questions/7787998/how-to-generate-exceptions-from-repaintmanager, very long NIO, and every JDBC, XxxSocket, ... are reasons for short_cuts as are SwingWorker (my view is that is sloppy,simple tacky), and later with SecondaryLoop implemented in official APIs – mKorbel May 26 '15 at 06:58

score 0 · Answer 2 · answered May 25 '15 at 21:53

You need to move the Statement and ResultSet code into the actionPerformed method. Right now you open a result set and immediately close it, then try to use it at a later time, in the ActionListener. You should also use PreparedStatements and proper error handling, like so:

try (Connection conn = DriverManager.getConnection("...") {
    PreparedStatement ps = conn.prepareStatement("...");
    try (ResultSet rs = ps.executeQuery()) {
        /* Parse result */
    }
} catch (SQLException ex) {
    for (Throwable t : ex) {
        t.printStackTrace();
    }
}

mysql sqlExceptiion Error

2 Answers2