ResultSet not closed when connection closed?

I've been doing code review (mostly using tools like FindBugs) of one of our pet projects and FindBugs marked following code as erroneous (pseudocode):

Connection conn = dataSource.getConnection();

try{
    PreparedStatement stmt = conn.prepareStatement();
    //initialize the statement
    stmt.execute();
    ResultSet rs =  stmt.getResultSet();
    //get data
}finally{
    conn.close();
}

The error was that this code might not release resources. I figured out that the ResultSet and Statement were not closed, so I closed them in finally:

finally{
    try{
        rs.close()
    }catch(SqlException se){
        //log it
    }
    try{
        stmt.close();
    }catch(SqlException se){
        //log it
    }
    conn.close();
}

But I encountered the above pattern in many projects (from quite a few companies), and no one was closing ResultSets or Statements.

Did you have troubles with ResultSets and Statements not being closed when the Connection is closed?

I found only this and it refers to Oracle having problems with closing ResultSets when closing Connections (we use Oracle db, hence my corrections). java.sql.api says nothing in Connection.close() javadoc.

One problem with ONLY closing the connection and not the result set, is that if your connection management code is using connection pooling, the connection.close() would just put the connection back in the pool. Additionally, some database have a cursor resource on the server that will not be freed properly unless it is explicitly closed.

I've had problems with unclosed ResultSets in Oracle, even though the connection was closed. The error I got was

"ORA-01000: maximum open cursors exceeded"

So: Always close your ResultSet!

You should always close all JDBC resources explicitly. As Aaron and John already said, closing a connection will often only return it to a pool and not all JDBC drivers are implemented exact the same way.

Here is a utility method that can be used from a finally block:

public static void closeEverything(ResultSet rs, Statement stmt,
        Connection con) {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) {
        }
    }
    if (stmt != null) {
        try {
            stmt.close();
        } catch (SQLException e) {
        }
    }
    if (con != null) {
        try {
            con.close();
        } catch (SQLException e) {
        }
    }
}

Oracle will give you errors about open cursors in this case.

According to: http://java.sun.com/javase/6/docs/api/java/sql/Statement.html

it looks like reusing a statement will close any open resultsets, and closing a statement will close any resultsets, but i don't see anything about closing a connection will close any of the resources it created.

All of those details are left to the JDBC driver provider.

Its always safest to close everything explicitly. We wrote a util class that wraps everything with try{ xxx } catch (Throwable {} so that you can just call Utils.close(rs) and Utils.close(stmt), etc without having to worry about exceptions that close scan supposedly throw.

ResultSet not closed when connection closed?

Related

Recent Posts