How to Close JDBC Resources Properly – Every Time

04 Aug 2007 How to Close JDBC Resources Properly – Every Time

In a Java program, we sometimes need to make sure we close a resource after we’ve finished using it. Common examples are files, Hibernate Sessions, JDBC Connections, Statements and ResultSets. The database-related ones are particular important – if we don’t close them, we can be left with unclosed connections to the database. This means that we could eventually run out of connections. Sure, these objects are supposed to close their underlying resources when they’re garbage collected, but sometimes they don’t get a chance to (for example, if the JVM exits suddenly) and sometimes they don’t do it even though they’re supposed to (some dodgy JDBC drivers can do this – I once used one that would leave the underlying database connection open if you didn’t close the ResultSet – even if you closed the Connection object).

Of course, I’m probably not telling you anything new here. And you’ll probably tell me that all you need to do is provide a ‘finally’ block that closes the resource.

Well let me first start by saying that frameworks like Spring provide helper classes that can do a lot of this stuff for you. You mightn’t think that it’s much effort, but as you’ll see soon, doing it properly can actually result in quite a lot of code. So if you’ve got the chance, check out Spring’s JdbcTemplate or HibernateTemplate classes. I don’t think you even need to use all of the other Spring infrastructure with them – as long as you can provide a JdbcTemplate with a java.sql.DataSource or a HibernateTemplate with a org.hibernate.SessionFactory, it’ll handle everything for you.

But what if you can’t use Spring, or it’s just overkill to use it? That’s fine, but it seems that people get confused about how to structure their code so that their ‘finally’ blocks work correctly under every circumstance. And this can result in unexpected behavior in your programs. Here’s one example that I see quite often:

Connection connection;

try {
connection = dataSource.getConnection();
// Do stuff with connection.
} finally {
connection.close();
}
So what’s wrong with this code? Well, if the call to dataSource.getConnection() fails, an exception will be thrown. The ‘finally’ block will still execute however, but the ‘connection’ object will be null. This will result in a NullPointerException being thrown instead of the original exception. So the original exception will be masked by the NullPointerException. ‘Who cares?’ you might say. Well, I care if I’m investigating a production problem at 3am and have just wasted five bleary-eyed minutes wondering why a NullPointerException occurred when the underlying cause was really a problem getting a database connection.

“Easy”, you say, “just do something like this:”

Connection connection;

try {
connection = dataSource.getConnection();

// Do stuff with connection.
} finally {
if (connection != null) connection.close();
}


That’ s great, but what if you’ve got a statement that you need to close too? “No worries”, you say, “the code needs to morph into something like this:”

Connection connection;
Statement statement;

try {
connection = dataSource.getConnection();
statement=connection.createStatement();

// Do stuff with statement.
} finally {
if (statement != null) statement.close();
if (connection != null) connection.close();
}

Well, what if the call to statement.close() fails? It’ll skip closing the connection. Whilst I admit that it’s unlikely that a call to statement.close() would fail, my point is that these are just a few of the wide number of possible combinations of things that can happen in your code. Wouldn’t it be easier if you didn’t have to worry about all these possibilities?

Well, fortunately, there’s a simple strategy that you can use to always make sure that these sorts of objects get closed down in an orderly, watertight fashion. It goes like this:

1. Do not initialize the connection variable to null – always assign the real connection object to it immediately.
2. On the very next line of code, start a try/finally block that will use that connection and then close it.
3. When you get a statement from the connection, don’t use the same try/finally block to manage it. Instead, repeat steps 1 and 2, but this time apply them to the statement instead of the connection. In other words, initialize the statement immediately and start a new, nested try/finally block on the next line of code.

Here’s what the code looks like:

Connection connection = dataSource.getConnection();
try {
Statement statement = connection.createStatement();
try {
// Do stuff with the statement
} finally {
statement.close();
}
} finally {
connection.close();
}

Because variables are initialized immediately, there’s no danger of NullPointerExceptions. And because each object is managed with it’s own carefully nested try/finally block, there’s no danger of things not being closed if some failure occurs in a contained block.

This pattern can pretty much be applied recursively. For example, you would manage a nested result set in the same manner:

Connection connection = dataSource.getConnection();
try {
Statement statement = connection.createStatement();

try {
ResultSet resultSet = statement.executeQuery("some query");

try {
// Do stuff with the result set.
} finally {
resultSet.close();
}
} finally {
statement.close();
}
} finally {
connection.close();
}

So hopefully you’re seeing a bit of a pattern here. In fact, we can generalise it for any object that needs to be closed:

1. Do not initialize the object variable to null – always assign the actual object to it immediately.
2. On the very next line of code, start a new, nested try/finally block. All code that uses the object should be within the ‘try’ block. The code that closes the object should be in the ‘finally’ block.
3. When you get a child object that also needs to be closed, go to step 1 and repeat.

It might look like overkill but in 10 years of coding Java I’ve found that this is the only way to really be sure that everything is going to be closed correctly. Every time I’ve tried to cut corners I’ve invariably introduced a defect. If you can see something wrong with it, or have got a better idea, I’d love to hear about it.

So as you’ve seen, doing this sort of thing properly requires a fair bit of code. And the more code we have, the more unit testing we have to do. That’s why it’s a good idea to use things like the Spring JdbcTemplate if you can – they’ve already done all of the work (and the testing) for you. What initially seems like a trivial piece of work is actually reasonably involved if you want to do it properly.

The same sort of pattern can be applied to files, or any other type of activity that requires resources to be closed.