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 1 - 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 for now...
sqle.printStackTrace();
}finally{
if(rs!=null){
try{
rs.close();
}catch(SQLException closeRsEx){
// you should probably log this exception
}
}
if(ps!=null){
try{
ps.close();
}catch(SQLException closePsEx){
// you should probably log this exception
}
}
if(conn!=null){
try{
conn.close();
}catch(SQLException closeConnEx){
// you should probably log this 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.