Must JDBC Resultsets and Statements be closed separately although the Connection is closed afterwards?

Question

What you have done is perfect and very good practice.

The reason I say its good practice... For example, if for some reason you are using a "primitive" type of database pooling and you call connection.close(), the connection will be returned to the pool and the ResultSet/Statement will never be closed and then you will run into many different new problems!

So you can't always count on connection.close() to clean up.

I hope this helps :)

Java 1.7 makes our lives much easier thanks to the try-with-resources statement.

try (Connection connection = dataSource.getConnection();
    Statement statement = connection.createStatement()) {
    try (ResultSet resultSet = statement.executeQuery("some query")) {
        // Do stuff with the result set.
    }
    try (ResultSet resultSet = statement.executeQuery("some query")) {
        // Do more stuff with the second result set.
    }
}

This syntax is quite brief and elegant. And connection will indeed be closed even when the statement couldn't be created.

From the javadocs:

When a Statement object is closed, its current ResultSet object, if one exists, is also closed.

However, the javadocs are not very clear on whether the Statement and ResultSet are closed when you close the underlying Connection. They simply state that closing a Connection:

Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released.

In my opinion, always explicitly close ResultSets, Statements and Connections when you are finished with them as the implementation of close could vary between database drivers.

You can save yourself a lot of boiler-plate code by using methods such as closeQuietly in DBUtils from Apache.

I'm now using Oracle with Java. Here my point of view :

You should close ResultSet and Statement explicitly because Oracle has problems previously with keeping the cursors open even after closing the connection. If you don't close the ResultSet (cursor) it will throw an error like Maximum open cursors exceeded.

I think you may encounter with the same problem with other databases you use.

Here is tutorial Close ResultSet when finished:

Close ResultSet when finished

Close ResultSet object as soon as you finish working with ResultSet object even though Statement object closes the ResultSet object implicitly when it closes, closing ResultSet explicitly gives chance to garbage collector to recollect memory as early as possible because ResultSet object may occupy lot of memory depending on query.

ResultSet.close();

If you want more compact code, I suggest using Apache Commons DbUtils. In this case:

Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
    conn = // Retrieve connection
    stmt = conn.prepareStatement(// Some SQL);
    rs = stmt.executeQuery();
} catch(Exception e) {
    // Error Handling
} finally {
    DbUtils.closeQuietly(rs);
    DbUtils.closeQuietly(stmt);
    DbUtils.closeQuietly(conn);
}

No you are not required to close anything BUT the connection. Per JDBC specs closing any higher object will automatically close lower objects. Closing Connection will close any Statements that connection has created. Closing any Statement will close all ResultSets that were created by that Statement. Doesn't matter if Connection is poolable or not. Even poolable connection has to clean before returning to the pool.

Of course you might have long nested loops on the Connection creating lots of statements, then closing them is appropriate. I almost never close ResultSet though, seems excessive when closing Statement or Connection WILL close them.