Closing JDBC resources

As illustrated briefly in the previous ResultSet example, there are various JDBC classes that have a close() method. Like input and output streams, they should in principle be closed once they are no longer needed. And like streams, in emergencies, they will be closed on finalization.

What's a little complicated in the case of JDBC programming is that there are various classes– Connection, Statement or PreparedStatement and ResultSet– all of which should be cleaned up at the appropriate time. Which should be closed when can depend on whether you're going to run multiple SQL queries on the same connection, and also simply what's convenient to close when (e.g. if you're passing a ResultSet back from a method that you expect the closer to call, the caller may actually not have access to the underlying Statement). The following table summarises when I'd recommend closing the various resources in the two circumstances (one statement per connection, and a connection shared across various SQL statements):

Resource When to call close():
one statement/connection
When to call close():
multiple statements/connection
Connection In a finally clause, after closing the Statement. When the connection is "too old" (see below).
Statement In a finally clause, after executing the query. If convenient, just before closing the Connection.
ResultSet Never: just rely on closing the Statement (but see below). In a finally clause, after reading all required results.
Summary of when to call close() on various JDBC resources.

Single ResultSet per Statement

The Statement object and any ResultSet opened from it are tightly bound:

This means that in many cases, there should be no need to close both the ResultSet and Statement. Reese1 reports problems with buggy drivers that don't correctly clean up all resources unless you explicitly close both. I've not personally come across this issue and suspect it relates to quite old drivers. But if you notice an issue such as a memory leak, you may want to try closing both ResultSet and Statement after each SQL execution, even though it shouldn't strictly be necessary.

Closing "old" connections

In principle, you should be able to open a database connection and execute as many SQL queries on it as you wish. In practice, you may find that this is not possible or desirable and that it is better to give each connection a certain "lifetime" before closing and re-creating it. A common problem is that many systems are configured to automatically close sockets (and hence database connections) after some maximum time. So if you want to share a database connection over multiple queries, in practice you will generally need to do the following:

1. Reese, G. (2000), Database Programming with JDBC and Java, O'Reilly (2nd ed).

If you enjoy this Java programming article, please share with friends and colleagues. Follow the author on Twitter for the latest news and rants.

Editorial page content written by Neil Coffey. Copyright © Javamex UK 2021. All rights reserved.