Why should I call close() on a ResultSet and Connection instance?

Question

When I don't need to use instances of those ResultSet and Connection anymore in my program, why should I call the .close() method on both of them ?

What are the dangers (if any) of not doing so ?

Answer 1

There are two questions here:

Database connections

Holding a database connection open consumes resources on the database; it uses memory and databases are configured to have a maximum number of connections, so you increase to likelihood of running out of connections. Also the state of the session is maintained, so you can run into trouble with locks being accidentally held beyond their intended scope.

On the positive side, prepared statements stay compiled and ready for use, so if you code and use your SQL correctly, you can gain significant performance benefits from reusing prepared statements. However, doing so may complicate your code and care is advised.

Also, obtaining a connection is quite expensive, so that's why connection pools exist. These leave the connections open, but the client gets connections, uses them, then releases them back to the pool when done.

Result set

Holding result sets open will also hold certain locks open if you don't commit (which closes then result set), thus depending on your application, you can quickly hit deadlocks or severe liveliness issues. Regardless of whether you hold connections open, always close your result sets as soon as possible to release as much resource back to the database as you can.

Answer 2

*.close() allows the object instance to release references to resources it may hold.

In the case of a ResultSet this could be a fairly significant number of other objects. The GC will get around to cleaning them up eventually - if you "let go of" the ResultSet. It's a good practice to get into.

A Connection object will be holding resources associated with the connection. In this case the GC will never directly recover those resources (possibly indirectly if the Connection implements a finalize method). You want to *.close() anything that might be holding up resources of any limited nature, so those resource can be reused.

It's not uncommon to have some cap on the # of connections, for example. You'll have better response & less potential for failure if you release those resources as soon as you can.

In BOTH cases, closing may improve overall responsiveness of your code; it's a good bet to make.