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

It is said to be a good habit to close all JDBC resources after usage. But if I have the following code, is it necessary to close the Resultset and the Statement?

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 {
    try { if (rs != null) rs.close(); } catch (Exception e) {};
    try { if (stmt != null) stmt.close(); } catch (Exception e) {};
    try { if (conn != null) conn.close(); } catch (Exception e) {};
}

The question is if the closing of the connection does the job or if it leaves some resources in use.

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.