Skip to main content

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.

Comments

Popular posts from this blog

Write Your Own Load Balancer: A worked Example

I was out walking with a techie friend of mine I’d not seen for a while and he asked me if I’d written anything recently. I hadn’t, other than an article on data sharing a few months before and I realised I was missing it. Well, not the writing itself, but the end result. In the last few weeks, another friend of mine, John Cricket , has been setting weekly code challenges via linkedin and his new website, https://codingchallenges.fyi/ . They were all quite interesting, but one in particular on writing load balancers appealed, so I thought I’d kill two birds with one stone and write up a worked example. You’ll find my worked example below. The challenge itself is italics and voice is that of John Crickets. The Coding Challenge https://codingchallenges.fyi/challenges/challenge-load-balancer/ Write Your Own Load Balancer This challenge is to build your own application layer load balancer. A load balancer sits in front of a group of servers and routes client requests across all of the serv

Bloodstock 2009

This year was one of the best Bloodstock s ever, which surprised me as the line up didn't look too strong. I haven't come away with a list of bands I want to buy all the albums of, but I did enjoy a lot of the performances. Insomnium[6] sound a lot like Swallow the Sun and Paradise Lost. They put on a very good show. I find a lot of old thrash bands quite boring, but Sodom[5] were quite good. They could have done with a second guitarist and the bass broke in the first song and it seemed to take ages to get it fixed. Saxon[8] gave us some some classic traditional heavy metal. Solid, as expected. The best bit was, following the guitarist standing on a monitor, Biff Bifford ripped off the sign saying "DO NOT STAND" and showed it to the audience. Once their sound was sorted, Arch Enemy[10] stole the show. They turned out not only to be the best band of the day, but of the festival, but then that's what you'd expect from Arch Enemy. Carcass[4] were very disappoin

Catalina-Ant for Tomcat 7

I recently upgraded from Tomcat 6 to Tomcat 7 and all of my Ant deployment scripts stopped working. I eventually worked out why and made the necessary changes, but there doesn’t seem to be a complete description of how to use Catalina-Ant for Tomcat 7 on the web so I thought I'd write one. To start with, make sure Tomcat manager is configured for use by Catalina-Ant. Make sure that manager-script is included in the roles for one of the users in TOMCAT_HOME/conf/tomcat-users.xml . For example: <tomcat-users> <user name="admin" password="s3cr£t" roles="manager-gui, manager-script "/> </tomcat-users> Catalina-Ant for Tomcat 6 was encapsulated within a single JAR file. Catalina-Ant for Tomcat 7 requires four JAR files. One from TOMCAT_HOME/bin : tomcat-juli.jar and three from TOMCAT_HOME/lib: catalina-ant.jar tomcat-coyote.jar tomcat-util.jar There are at least three ways of making the JARs available to Ant: Copy the JARs into th