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
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();
}