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

java jdbc connection resultset
sweet dreams picture sweet dreams · Jul 31, 2012 · Viewed 17.4k times · Source

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

Bohemian picture Bohemian · Jul 31, 2012

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.

Related questions

Where to close a JDBC Connection while I want to return the ResultSet

It seems that the ResultSet will be automatically closed when I close the Connection. But I want to return the ResultSet and use it in another method, then I don't know where to close Connection and PreparedStatement. public ResultSet executeQuery(…

Read More
When to close Connection, Statement, PreparedStatement and ResultSet in JDBC

Few questions on JDBC coding: For a single client application, do we need a Connection pool? Is it a good idea to create a Connection at the beginning and keep it alive without close it until application exit? Why? PreparedStatement …

Read More
Connection behavior - DriverManager.getConnection() and DataSource.getConnection()

If I get a connection object using DriverManager.getConnection() and DataSource.getConnection(), how they differ in behavior when .close() is called on those objects? Before .close() method call, I got relevant Statement and ResultSet objects from these two different connections. …

Read More