Closing JDBC resources
Posted : Friday August 15th, 2008

I previously submitted this content to SDN Share, a Sun program to promote best practices, examples etc among developers. However it bears repeating so here it is again.

In this article we'll look at why disposing of (closing) database resources correctly is critical and perhaps more importantly the correct way to do this.

The first point that should be addressed is how important or not properly closing your database resources in JDBC code is because only when we are aware of how important it truly is and what the consequences are can we appreciate the value of writing proper code to close JDBC resources properly. So what exactly are the consequences of failing to properly close JDBC resources? They consequences may include all of the following (arranged from best to worst case scenarios)

• your code will have degraded performance
• the database server will have degraded performance
• your application will fail because no more database connections can be allocated
• your application container will fail because no more database connections can be allocated
• the database server becomes unresponsive
• the database server crashes

None of these scenarios are very good and the last few border on catastrophic. Unfortunately it seems that many people remain unaware of just how bad a problem they can create for themselves by failing to properly close JDBC Connections, Statements and ResultSets.

So why is it so bad? One has to remember that unlike a stand-alone program a JDBC program is not just allocating resources locally but also allocating resources on another system (the database server) which is not controlled by Java. In other words when you leak JDBC resources you are not just leaking them locally in your program but you are leaking them on the database server as well! Under some circumstances even the outright failure of your program may not cause these server allocated resources to be released and will require intervention by your DBA to resolve.

How to properly dispose of (close) JDBC resources

There is only one right way to close JDBC resources properly and it includes all of the following.

• you must call the close method
• you must close the resources in the opposite order to that in which you opened them. Close ResultSets first, Statements second and Connections last
• you must close all your resources in a finally block

Example - the correct way

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try{
  conn = // get connection
  ps = conn.prepareStatement(sqlString);
  rs = ps.executeQuery();
}catch(SQLException sqle){
  /* whatever exception handling is appropriate for your program*/
  sqle.printStackTrace();
}finally{
  if(rs!=null){
    try{
      rs.close();
    }catch(SQLException closeRsEx){
      // log exception
    }
  }
  if(ps!=null){
    try{
      ps.close();
    }catch(SQLException closePsEx){
      // log exception
    }
  }
  if(conn!=null){
    try{
      conn.close();
    }catch(SQLException closeConnEx){
      // log exception
    }
  }
}

Please note that the point of the above example is not to say that all your JDBC code must only go into one method, the point is to demonstrate all the steps that go into making sure that we close our JDBC resources properly. Namely; we call close on all our JDBC objects, we do it in the inverse order from creation, we close all our resources in a finally block to ensure that our closing code is called. You should be closing your resources as soon as you are done with them.

Please also note that Statements were mentioned but a PreparedStatement was used. The rules to follow when closing JDBC resources regarding Statement apply just as much if not moreso to the sub-interfaces of Statement (PreparedStatement and CallableStatement).

Common mistakes

Sometimes the best way to learn what is right is to study what is wrong when mistakes are made. So in this light are a few examples of commonly seen mistakes in code dealing with JDBC resources.

One mistake is to set the Connection, Statement and ResultSet variables to null instead of calling the close methods. This seems to spring from a notion that then the Java garbage collector will "deal" with it and all will be fine. This is though a mistake for two reasons. One you may be leaving resources tied up locally for longer than they have to be. Two you not be closing resources on the database properly if at all thus causing all of the database server related problems listed previously. To avoid this mistake you must call close on all the instances of Connection, Statement and ResultSet you use.

A second mistake often seen is to only close some of the resources like Connection but not ResultSets or Statements. This one is based on the theory that closing the Connection is "good enough" and that the JDBC driver will deal with the rest. This one is a mistake for three reasons. One you will probably leaving resources locally and on the database tied up for longer than is necessary (thus degrading performance). Two, it relies on the JDBC driver and database implicitly cleaning up for you which may or not happen correctly. It is better to explicitly call close than implicitly. Three it may not work at all in some scenarios, for example with a ConnectionPool where the connections are not in fact ever closed but recycled.

Conclusion

Hopefully in reading this article you've learned why properly closing JDBC resources correctly is so important. It's sadly all too common to find threads in JDBC related forums started by people in a panic because their application which worked in development is failing badly in production with errors like "No more connections". Take the time to write your JDBC code properly and save yourself the pain and expense of having to trace all your JDBC code later to find the source of leaks and crashes.

Tags

best  closing  JDBC  practices  programming  resources 

Categories

Technical  Java  Database 

Comments