CODEWITHSUNDEEP
javasql
Sebastien Chagnon
Sebastien Chagnon

Reputation: 13

Operation not allowed after ResultSet closed error even if the PreparedStatement is used only once

I get this error whenever I try to get a ResultSet. I have looked a long time online and there are a lot of poeple getting the same problem as me but their problem is that they use the PreparedStatement for multiple queries.

As you can see in the following code, I use the PreparedStatement only once.

The getSQLConnection() method returns a brand new Connection. The error is thrown on the line : ResultSet rs = ps.executeQuery()

    try (Connection connection = getSQLConnection();
            PreparedStatement ps = connection.prepareStatement(qry);
            ResultSet rs = ps.executeQuery()) {

        return rs;

    } catch (SQLException ex) {
        plugin.get().getLogger().log(Level.SEVERE, "Couldn't execute SQL statement: ", ex);
    }

I have used SQL databases before and it has never done this. I am confused. Hope you guys may help me!

Upvotes: 0

Views: 65

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

When you used the try with resources you told Java to close all resources inside the try clause once the block itself has finished executing. This includes, as you have written it, the ResultSet, which means whoever called your method will never be able to use that result set. Instead, you should process the result set inside the try block:

try (Connection connection = getSQLConnection();
        PreparedStatement ps = connection.prepareStatement(qry);
        ResultSet rs = ps.executeQuery()) {

    // process result set here
}

Besides that the result set will be closed be the time the caller receives it, returning has another problem that the cleanup for your JDBC call must now be done in two places. This is highly undesirable, and a maintenance problem.

Upvotes: 2

Related Questions