Azoraqua Azoraqua -4 years ago 123
MySQL Question

Exception thrown when trying to execute query in Java

When trying to execute the query, it throws a SQLException (Operation not allowed after ResultSet closed).
Connection is established though.

Code:

public ResultSet executeQuery(String sql, Object... parameters) {
try (Connection c = dataSource.getConnection(); PreparedStatement stmt = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
for (int i = 0; i < parameters.length; i++) {
stmt.setObject(i + 1, parameters[i]);
}

ResultSet resultSet = stmt.executeQuery();

resultSet.close();
stmt.close();
// c.close();

return resultSet;
} catch (SQLException e) {
throw new IllegalStateException(e);
}
}


Stacktrace:

java.sql.SQLException: Operation not allowed after ResultSet closed
at com.hylexismc.profiles.Main.getProfile(Main.java:127) ~[?:?]
at com.hylexismc.profiles.Main.hasProfile(Main.java:107) ~[?:?]
at com.hylexismc.profiles.Main.lambda$onEnable$0(Main.java:50) ~[?:?]
at org.bukkit.craftbukkit.v1_11_R1.scheduler.CraftTask.run(CraftTask.java:71) ~[spigot-1.11.2.jar:git-Spigot-3fb9445-2b6c9f4]
at org.bukkit.craftbukkit.v1_11_R1.scheduler.CraftScheduler.mainThreadHeartbeat(CraftScheduler.java:353) [spigot-1.11.2.jar:git-Spigot-3fb9445-2b6c9f4]
at net.minecraft.server.v1_11_R1.MinecraftServer.D(MinecraftServer.java:738) [spigot-1.11.2.jar:git-Spigot-3fb9445-2b6c9f4]
at net.minecraft.server.v1_11_R1.DedicatedServer.D(DedicatedServer.java:399) [spigot-1.11.2.jar:git-Spigot-3fb9445-2b6c9f4]
at net.minecraft.server.v1_11_R1.MinecraftServer.C(MinecraftServer.java:678) [spigot-1.11.2.jar:git-Spigot-3fb9445-2b6c9f4]
at net.minecraft.server.v1_11_R1.MinecraftServer.run(MinecraftServer.java:576) [spigot-1.11.2.jar:git-Spigot-3fb9445-2b6c9f4]
at java.lang.Thread.run(Unknown Source) [?:1.8.0_131]
Caused by: java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) ~[spigot-1.11.2.jar:git-Spigot-3fb9445-2b6c9f4]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897) ~[spigot-1.11.2.jar:git-Spigot-3fb9445-2b6c9f4]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886) ~[spigot-1.11.2.jar:git-Spigot-3fb9445-2b6c9f4]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860) ~[spigot-1.11.2.jar:git-Spigot-3fb9445-2b6c9f4]
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:743) ~[spigot-1.11.2.jar:git-Spigot-3fb9445-2b6c9f4]
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6301) ~[spigot-1.11.2.jar:git-Spigot-3fb9445-2b6c9f4]
at com.hylexismc.profiles.Main.getProfile(Main.java:115) ~[?:?]
... 9 more


Edit 1:
Now, I have changed it, it gives exactly the same, how does this work?

public ResultSet executeQuery(String sql, Object... parameters) {
Connection c = null;
PreparedStatement stmt = null;
ResultSet resultSet = null;

try {
c = dataSource.getConnection();
stmt = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

for (int i = 0; i < parameters.length; i++) {
stmt.setObject(i + 1, parameters[i]);
}

resultSet = stmt.executeQuery();
return resultSet;
} catch (SQLException e) {
throw new IllegalStateException(e);
} finally {
try {
assert c != null;
assert stmt != null;
assert resultSet != null;

c.close();
stmt.close();
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

Answer Source

You are closing the ResultSet by calling

resultSet.close();

After that you are returning the resultSet and you are calling the resultSet.next() method. You are not suppose to call the next method after closing the ResultSet.

For solving this issue close the ResultSet after getting the data.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download