Boiler Plating Database Resource Cleanup - Part I (v2)

This is a modification of my original Boiler Plating Database Resource Cleanup article giving a bit more background, exploring a couple of different ideas and setting the sceane for the second part:

The problem is simple. Cleaning up after querying a database in Java is unnecessarily verbose and complex. Plain and simple. I'll start with an example that demonstrates the problem. The system I'm working on currently uses a number of web services. We have a set of web services on the production box, another on the development box and another on our local machines. The system asks the relevant database for the location of the web service based on the services' name:

try
{
  Class.forName(driver);
  Connection con = DriverManager.getConnection(connectionString,username,password );
  try 
  {
   PreparedStatement ps = con.prepareStatement("select url from services where name = 'Instruments'");
   
   try 
   {
    ResultSet rs = ps.executeQuery();
    if(rs.next())
    {
      System.out.println(rs.getString("url"));
    }
    
    try 
    {
      rs.close();
    }
    catch(SQLException e) 
    {
     // Report Error
    }
   }
   finally 
   {
    try 
    {
     ps.close();
    }
    catch(SQLException e) 
    {
     // Report Error
    }
   }
  }
  finally 
  {
   try 
   {
    con.close();
   }
   catch(SQLException e) 
   {
    // Report Error
   }
  }
}
catch(Exception e)
{
 // Report Error
}

This is a lot of code to get one string out of a database and most of it must be repeated every time a database is accessed. Most of it is error handling and resource management. In fact I've over simplified it. For a discussion of how error handling and resource management should really be handled see "Handling Exceptions in Finally" mentioned above.

The Sun Java Documentaton states the following for the Connection interface's close method:

Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released.

Note: A Connection object is automatically closed when it is garbage collected. Certain fatal errors also close a Connection object.

It also states the following for the Statement interface's close method:

Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources.

Note: A Statement object is automatically closed when it is garbage collected. When a Statement object is closed, its current ResultSet object, if one exists, is also closed.

And the following for the ResultSet interface's close method:

Releases this ResultSet object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed.

Note: A ResultSet object is automatically closed by the Statement object that generated it when that Statement object is closed, re-executed, or is used to retrieve the next result from a sequence of multiple results. A ResultSet object is also automatically closed when it is garbage collected.

This can all be interpreted in a number of two ways. The first is that everything gets closed when it is garbage collected, so there is no need to explicitly close anything. This relies on the appropriate finalizers getting called but, as Bloch tells us, Java provides no guarantee that a finalizer will ever be called, even when an object is garbage collected.

Another method is to explicitly close Statement and Connection objects as statement objects clean up their associated ResultSet objects. Drawbacks include any error caused by closing the ResultSet is potentially ignored and the resource is not release as soon as it could be.

Yet another method is to explicitly close everything. This is the most code, but releases resources and handles any error as soon as a resource is finished with, making it most efficient way of using resources.

I favour the third and final method. It is more code, but boiler plating will mean most of it only need to be written once. Everything will be cleaned up as soon as possible, all errors can be trapped and reported and nothing is left to chance.

The close methods for Connection, Statement and ResultSet objects can all throw if there is an exceptional circumstance. In Item 65 of Effective Java Bloch explains that exceptions should not be ignored. He also describes certain circumstances where it might be ok to ignore or log these types of exceptions. The Connection, Statement and ResultSet close methods could be considered one of these situations.

Read more.