java - 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.