How to use ResultSet correctly? No operations allowed after statement closed-CodePudding

any idea how to improve this code to avoid the java.sql.SQLException: No operations allowed after statement closed.

I can't understand where the error actually occurs, I guess after if (resultSet.next()) the connection is closed.

This method is executed when a user exits and saves their data.

public void saveData(GenericData data) {
        database.open();

        try {
            ResultSet resultSet = database.getStatement().executeQuery(data.getDao().getSQLQuery(data));

            if (resultSet.next()) {
                if (data.getDao().getSQLUpdate(data) != null) database.getStatement().executeUpdate(data.getDao().getSQLUpdate(data));
            }
            else database.getStatement().execute(data.getDao().getSQLInsert(data));

            resultSet.close();
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }

        database.close();
    }

database.open method:

public boolean open() {
        if (isConnected()) {
            return true;
        }

        try {
            Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
            if (this.connection == null) {
                this.connection = DriverManager.getConnection(url, user, password);
            }

            if (this.statement == null && this.connection != null) {
                this.statement = this.connection.createStatement();
            }
        } catch (Exception exception) {
            exception.printStackTrace();
        }
        return isConnected();
    }

database.getStatement method return the statement created in open method.

The line 208 in the code is else database.getStatement().execute(data.getDao().getSQLInsert(data));.

[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]: java.sql.SQLException: No operations allowed after statement closed.
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:82)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at it.thedarksword.essentialsvc.database.controller.DataController.saveData(DataController.java:208)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at it.thedarksword.essentialsvc.database.process.DataProcess.lambda$save$0(DataProcess.java:21)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.exec(CompletableFuture.java:1760)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:373)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(ForkJoinPool.java:1182)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.util.concurrent.ForkJoinPool.scan(ForkJoinPool.java:1655)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1622)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:165)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]: Caused by: com.mysql.cj.exceptions.StatementIsClosedException: No operations allowed after statement closed.
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.StatementImpl.checkClosed(StatementImpl.java:336)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:655)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   ... 10 more

CodePudding user response：

executeQuery is probably closing the database connection and you try to execute a statement again.

I suggest you to handle the open and the close in the place you are calling the statements to avoid these issues.

If you are using java 8 features, keep in mind that Connection, PreparedStatement and ResultSet, are all auto-closeable. So, the best option is to you use try-with-resources and handle all the things inside. For example:

try (Connection con = ConnectionPool.getConnection();
    PreparedStatement st = con.prepareStatement("UPDATE users SET exitTime=? WHERE userId=?"))
{
    st.setLong(1, System.currentTimeMillis());
    st.setInt(2, user_id);
    try(ResultSet rs = st.executeQuery())
    {
        // place your checks...
        while (rs.next())
        {
            // ...
        }
    }
}
catch (SQLException e)
{
    e.printStackTrace();
}

CodePudding user response：

The error is obviously in line 208 of your program DataController.java

it.thedarksword.essentialsvc.database.controller.DataController.saveData(DataController.java:208)

However, you need to tell us what exactly is line 208.

Next, you assumes that database.getStatement() will return a new Statement object, and not in close status. Based on the error message, it returned a statement that is closed, so you can't execute another UPDATE SQL.

A closed Statement can't be used to execute executeQuery() or executeUpdate(). This is what the error message is complaining about.

Your programming flow most likely closed the Statement, yet line 208 re-use the same Statement object/variable to run another UPDATE SQL.

Recommendation is to temporary modify your program to create a new statement every time to confirm this is the cause, then slowly narrow down and optimize the code