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):

Summary of when to call close() on various JDBC resources.

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.

Single ResultSet per Statement

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

• there can only ever be a maximum of one open ResultSet per Statement;
• closing the Statement automatically closes any open ResultSet created from it;
• executing SQL on a Statement also closes any previously-open ResultSet on that statement.

This means that in many cases, there should be no need to close both the ResultSet and Statement. Reese¹ 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:

• close and re-create the connection when you judge that it has "expired" (e.g. after so many hours, or after so many thousand queries);
• be prepared in any case for the connection to be closed "under your feet".

---

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

---