5 Answers
5

When you are done with using your Connection, you need to explicitly close it by calling its close() method in order to release any other database resources (cursors, handles, etc) the connection may be holding on to.

Actually, the safe pattern in Java is to close your ResultSet, Statement, and Connection (in that order) in a finally block when you are done with them, something like that:

@onaclov2000 Yes, rs, ps, conn may be null depending on where the code breaks. That's why this is known as the "safe" pattern.
–
Pascal ThiventFeb 8 '10 at 22:53

8

@Pascal Thivent: Actually we don't need to close all of them. "Core Java Volume two - Advanced Features" book has wrote: The close method of a Statement object automatically closes the associated ResultSet if the statement has an open result set. Similarly, the close method of the Connection class closes all Statements of the Connection.
–
Majid AzimiJul 12 '11 at 12:13

Yes. You need to close the resultset, the statement and the connection. If the connection has come from a pool, closing it actually sends it back to the pool for reuse.

You typically have to do this in a finally{} block, such that if an exception is thrown, you still get the chance to close this.

Many frameworks will look after this resource allocation/deallocation issue for you. e.g. Spring's JdbcTemplate. Apache DbUtils has methods to look after closing the resultset/statement/connection whether null or not (and catching exceptions upon closing), which may also help.

... until it exits. This ties down various finite resources on the client and server side. If a client does this kind of thing too much, it can cause problems for the client itself, the database service, and possibly even for other applications running on client or server machine.
–
Stephen CFeb 9 '10 at 0:53

It is enough to close just Statement and Connection. There is no need to explicitly close the ResultSet object.

Java documentation says about java.sql.ResultSet:

A ResultSet object is automatically closed by the Statement object that generated it when that Statement object is closed, re-executed, or is used to retrieve the next result from a sequence of multiple results.