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):
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).
Written by Neil Coffey. Copyright © Javamex UK 2012. All rights reserved.