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.

About Ben Teese

I'm a Senior Consultant at Shine Technologies. You can find me on Twitter and GitHub.
This entry was posted in Java. Bookmark the permalink.

7 Responses to How to Close JDBC Resources Properly – Every Time

  1. Kon says:

    Very nice. Verbose, but safe.
    Same concept can be applied generally to network connections – not so important for sockets and http, but if you”re establishing bluetooth connections and resources are short (i.e. your system can only have one bluetooth session open at any one point in time), then this looks like a great way to make sure you always clean up after yourself.

  2. matthewj says:

    There is a discussion on java.net about the use (or not) of the new finalize() method for closing resources – http://weblogs.java.net/blog/jfalkner/archive/2007/07/blarg27_dont_us.html

    I’m yet to hear of a good use for finalize() given the indeterminate timing of garbage collection, however on a recent Java Posse podcast one suggestion was to put assertions in the finalize() method as an extra safety net to check for un-closed resources.

  3. There’s some talk of adding closures to Java, which would allow for some nice Rubyish approaches to safely clean up resources.

    With closures you could do something like (I expect the formatting of the following will get mangled…):

    dataSource.getConnection() do |c|
    c.createStatement() do |s|
    //do stuff with statement s
    end
    end

    And have the framework code that instantiates the resource automatically clean it up when client code is done with it.

    This is the way that File etc are handled in Ruby. When the closures that opened the file finishes, it automatically closes it (of course you can still open the file and handle closing it manually if you want).

  4. James says:

    Your first line where you get the connection could surely also throw an exception?

    I prefer the following format which catches all the issues you have stated, does not result in excessive indentation, and does not require as many try/finallys (which seem to add no value).

    public List listAll() throws FinderException {
    
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
    
            try {
                conn = DataSourceLocator.getDataSource().getConnection();
                ps = conn.prepareStatement(SQLStatementBuilder.getCurrentUsers());
                rs = ps.executeQuery();
    
                List list = new ArrayList();
                while (rs.next()) {
                    UserListDto dto = new UserListDto();
                    dto.setUserID(rs.getLong(1));
                    dto.setUsername(rs.getString(2));
                    dto.setUserType(rs.getInt(3));
                    list.add(dto);
                }
                return list;
    
            } catch (SQLException ex) {
                throw new FinderException(ex.getMessage());
    
            } finally {
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException e) { /*ignored*/ }
                }
                if (ps != null) {
                    try {
                        ps.close();
                    } catch (SQLException e) { /*ignored*/ }
                }
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException e) { /*ignored*/ }
                }
            }
        }
    
  5. James says:

    Or no indentation at all! haha

  6. Pingback: Java, JDBC and “memory leaks” | meta/LPAR

  7. Pingback: JBoss AS and JDBC DataSource tutorial « Tillias's Blog

Comments are closed.