Order in closing resources

Question

Should I close the statement before the connection? And the resultset before the statement? Or is it all the other way around?

Connection conn = null;
Statement st = null;
Resultset rs = null;

try {
    // Do stuff

} catch (SQLException e) {
    // Do stuff
}
finally {
    if (rs != null) rs.close();
    if (st != null) st.close();
    if (conn != null) conn.close();         
}

Or

Connection conn = null;
Statement st = null;
Resultset rs = null;

try {
    // Do stuff

} catch (SQLException e) {
    // Do stuff
}
finally {
    if (conn != null) conn.close();         
    if (st != null) st.close();
    if (rs != null) rs.close();
}

Answer 1

Conclusion: the order does not really matter as long as you close them individually.

---

From the official API specification on close() on class Connection, Statement and ResultSet:

• For already closed resources, close() is a no-op:

  Calling the method close on a Connection object that is already closed is a no-op.

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

  Calling the method close on a ResultSet object that is already closed is a no-op.

• Closing a resource of "higher order" closes their "derived" resources:

  [Connection.close()] Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically reeased.

  [Statement.close()] Note:When a Statement object is closed, its current ResultSet object, if one exists, is also closed.

  You can try the following code to see if closing Connection also closes the other two resources:

  // url, username, password and sql pre-defined
  
  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  
  try
  {
      conn = DriverManager.getConnection(url, username, password);
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
  }
  catch (SQLException e) { e.printStackTrace(); }
  finally
  {
      if (conn != null)
      {
          System.out.println("conn is not null and I'm about to close it");
  
          try
          {
              conn.close();
  
              System.out.println(stmt != null && stmt.isClosed() ?
                                 "stmt is also closed" : "stmt is alive");
  
              System.out.println(rs != null && rs.isClosed() ?
                                 "rs is also closed" : "rs is alive");
          }
          catch (SQLException e) { e.printStackTrace(); }
      }
  }
  

Hence, say if we close them in this FIFO order: Connection > Statement > ResultSet:

finally
{
    if (conn != null)
    {
        try { conn.close(); }
        catch (SQLException e) { e.printStackTrace(); }
    }
                                                        
    if (stmt != null)
    {
        try { stmt.close(); }
        catch (SQLException e) { e.printStackTrace(); }
    }
                                                        
    if (rs != null)
    {
        try { rs.close(); }
        catch (SQLException e) { e.printStackTrace(); }
    }
}

• If a "higher order" resource is successfully closed, its "derived" resources should've been closed as well
  • Calling close() on these already closed resources is a no-op but is fine as no exceptions are thrown
• If failed to be closed, its "derived" resources still have a chance to be closed

And you can also tell the similiar story for any other arrangements.

Answer 2

You should close the resources in the reverse of the order you opened (as if these resources are on a stack).

With Java 7 try-with-resources, the ideal way is:

try (
   Connection conn = somethingThatGetsAConnection();
   Statement st = conn.createStatement();
   Resultset rs = st.executeQuery("SELECT something");
) {
    // Do stuff

} catch (SQLException e) {
    // Do stuff
}

And Java will take care of it for you, and it will close the resource in the reverse order. See also the Oracle tutorial on try-with-resources:

Note that the 'close' methods of resources are called in the opposite order of their creation.

You can find a more in-depth look at try-with-resources in the article Better Resource Management with Java SE 7: Beyond Syntactic Sugar

The Java Language Specification for Java 7 mentions in section 14.20.3:

Resources are initialized in left-to-right order. If a resource fails to initialize (that is, its initializer expression throws an exception), then all resources initialized so far by the try-with-resources statement are closed. If all resources initialize successfully, the try block executes as normal and then all non-null resources of the try-with-resources statement are closed.

Resources are closed in the reverse order from that in which they were initialized. A resource is closed only if it initialized to a non-null value. An exception from the closing of one resource does not prevent the closing of other resources.

This can also be seen as a clear indication that the Java language designers consider closing resources in the reverse order they where allocated the norm.

Answer 3

To solve this with minimal effort, try using Java 7's new A.R.M. (Automatic Resource Management) Blocks, also known as Try-With-Resources.

try (Connection conn = null, Statement st = null, ResultSet rs = null){
    // Do stuff

} catch (SQLException e) {
    // Do stuff
}

No ugly Finally or worrying about proper order, Java takes care of it for you.

Some more info regarding ARM/Try-With-Resources Blocks: http://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html

Answer 4

The first example is the right way. The problem with any other order is that closing a Statement will automatically close any underlying ResultSet as well (and same may happen for a Connection) - so you need to close the one lowest in the hierarchy first.

The close() methods may throw a SQLException, as @aubin pointed out. One easy solution to this problem is to use DBUtils closeQuietly() method for closing them - then you don't even need to null-check!

Answer 5

ResultSet, Statement and then the Connection. The golden rule to JDBC connections and statements is to close in the reverse order of initiation or opening. In addition, the ResultSet is dependant on the execution of the Statement and the Statement is dependant on the Connection instance. Hence, the closing should occur in that order (ResultSet, Statement, Connection).

Answer 6

Close the result set, then the statement, then the connection.

In other words, close everything down on a last-in-first-out basis.