JDBC garbage collection

Question

What happens if i don't close resultset or preparedstatements.

Will they be closed and released by the garbage collector.

I'm asking this for local variables inside a function.

Do you know any documentation about this ?

Answer 1

if i don't close result set or prepared statements.Will they be closed and released by the garbage collector.

resultset and preparedstatment are closed ,by explicitly calling close method. Garbage collector will not close these. I you do not call close , then the oracle cursor is not released at the oracle end.

Will they be released by the garbage collector.

Generally an object becomes eligible for garbage collection in Java on following cases:

• All references of that object explicitly set to null e.g. object = null
• Object is created inside a block and reference goes out scope once control exit that block.
• Parent object set to null, if an object holds reference of another object and when you set container object's reference null, child or contained object automatically becomes eligible for garbage collection.
• If an object has only live references via WeakHashMap it will be eligible for garbage collection.

for your question : I'm asking this for local variables inside a function.

ResultSet Object created inside a method, not closed and reference goes out scope ,once control exit that method . , then the reference is set to null and object is eligible for garbage collection. I did say eligible not guaranteed.The underlying oracle cursor is still there in the database.because u did not call close.

Answer 2

-> What happens if i don't close resultset or preparedstatements? Will they be closed and released by the garbage collector?.

That way you are degrading the database and application performance. You must have to close or dispose JDBC resource properly.

To close or dispose (JDBC resource) objects means these objects are now available for garbage collection and GC will releases any JDBC resources which had been acquired.

In case of ResultSet object, it will be closed automatically when Statement.close() method is called. You may call ResultSet.close() method if you want to close ResultSet object explicitly (Read Article - 5.1.20 Closing a ResultSet Object).

Have a look at articles - Best Practice: Closing and releasing JDBC resources and Enhancements in Java SE 7 and JDBC 4.1 (Text from this article - Feature: The ability to use a try-with-resources statement to automatically close resources of type Connection, ResultSet, and Statement)

Answer 3

If your code does not close ResultSets or PreparedStatements when done using then, your application will hog scarce resources — like cursors — in the database. See, for example:

• ResultSet not closed when connection closed?
• What happens to the original resultSet when it is returned from a method into a new object?

The garbage collector does not know anything about closing ResultSets or PreparedStatements, so GC won't automagically take care of that for you. What will? Java 7's try-with-resources statement!

Answer 4

You must explicitly close the ResultSet and Statement objects after you finish using them. This applies to all ResultSet and Statement objects you create when using the JDBC drivers. The drivers do not have finalizer methods; cleanup routines are performed by the close() method of the ResultSet and Statement classes. If you do not explicitly close your ResultSet and Statement objects, serious memory leaks could occur. You could also run out of cursors in the database. Closing both the result set and the statement releases the corresponding cursor in the database; if you close only the result set, the cursor is not released

Answer 5

I am not an expert here, but I don't think GC can collect that as PSs are associated with database connections, so that won't get garbage collected. Yo can have a look here: http://www.theserverside.com/news/1365244/Why-Prepared-Statements-are-important-and-how-to-use-them-properly