Java ResultSet is not being closed properly

Question

I have a Java application with many code snippets like the example below. Very simple querying of an Oracle database. Valid data is returned and parsed, then the close() functions are called.

ResultSet rs = null;
Statement stmt = null;

try
{
    stmt = conn.createStatement();
    rs = stmt.executeQuery("SELECT * FROM example");
    while (rs.next())
    {
        // do stuff
    }

    rs.close();
    stmt.close();
    System.out.println("test1");
}
catch (Exception e)
{
    System.out.println("error1");
}

I started encountering a "maximum cursors exceeded" error. I checked my methods to determine whether ResultSets are not being closed. The catch clause was never triggered, and "test1" was printed every single time. Which means the rs.close() and stmt.close() lines are NOT being skipped. Yet the ResultSets are not actually being closed.

I added a finally clause and it cleared up the issue.

finally
{
    if (rs != null)
    {
        try
        {
            rs.close();
            System.out.println("test2");
        }
        catch (Exception e)
        {
            System.out.println("error2");
        }
    }
    if (stmt != null)
    {
        try
        {
            stmt.close();
            System.out.println("test3");
        }
        catch (Exception e)
        {
            System.out.println("error3");
        }
    }
}

OUTPUT:

test1
test2
test3

My question is, why do rs.close() and stmt.close() need to be called twice? The calls in the try clause appear to do nothing. Yet I call them again in the finally clause, and they are successful. How is this possible?

score 1 · Answer 1 · answered Mar 25 '19 at 21:51

Use try-with-resources (Java 7+):

try (Statement stmt = conn.createStatement()) {
    try (ResultSet rs = stmt.executeQuery("SELECT * FROM example")) {
        while (rs.next()) {
            // do stuff
        }
        System.out.println("test1");
    }
} catch (Exception e) {
    System.out.println("error1");
}

Basil Bourque · Answer 2 · 2019-03-25T22:06:25.007

No, no need to call twice

No, there is no need in JDBC to be calling close twice. I suspect something else is going on.

We cannot tell for sure what is going on in your code with certainty. We cannot know if your supposedly second call actually fixed the problem. The documentation for Statement::close, for example, says:

Calling the method close on a Statement object that is already closed has no effect.

try-with-resources

As the Answer by Andreas suggests, you should be using a try-with-resources.

See:

Java 7 tech note on try-with-resources.
Oracle Tutorial.
Java 9 enhancement to try-with-resources, using previously-created variable within Try-With-Resource Statement.

Use try-with-resources for JDBC and also for any resource implementing AutoCloseable.

You can put one or more resources in your try( … ). Separate with semi-colons, the last semi-colon being optional. Java will take of tracking the resources, each being closed in the reverse order of being opened. If an exception occurs in the middle, Java knows not to try closing the null resource objects. This significantly simplifies your coding.

Java ResultSet is not being closed properly

2 Answers2

No, no need to call twice

try-with-resources