Why should I call close() on a ResultSet and Conne

问题:

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 ?

回答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.

回答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.

回答3:

By not closing JDBC connections you increase the likelihood of long waits for idle JDBC connections to be reaped where connection pooling is used. Closing JDBC connections will allow quicker reuse and improve performance.

Failure to close resultsets, can lead to some databases not freeing up cursor resources.

回答4:

If you didn't close result set object, you may face out of memory error on long run of your code. I faced out of memory error when I missed closing it and directly made result set reference as null. I have used MySQL database.