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):
||When to call close():
|When to call close():
||In a finally clause, after closing the Statement.
||When the connection is "too old" (see below).
||In a finally clause, after executing the query.
||If convenient, just before closing the Connection.
Summary of when to call close() on various JDBC resources.
||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.
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:
- 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).