1

Let's say I get a PreparedStatement from a Connection object, and then later I overwrite the reference with another PreparedStatement. Then, later, I close() the reference. Will the first PreparedStatement (the one I lost the reference to) remain open? Or does some protocol or garbage collection take care of that?

For example:

PreparedStatement ps = connection.prepareStatement(MY_QUERY);
// do stuff
ps.execute();

ps = connection.prepareStatement(MY_OTHER_QUERY);
// do stuff
ps.execute();

ps.close();

Does the first PreparedStatement object, the one used to execute MY_QUERY, remain open?

asteri
  • 11,402
  • 13
  • 60
  • 84

3 Answers3

7

Yes, the PreparedStatement will be left open until it gets garbage collected* (if at all).

That's why you always see the rule to always release (close) external resources that you acquire.

JDBC resources (such as this prepared statement, connections, results sets) and IO resources (sockets, streams, ...) are the two most common resources that you manually need to manage in Java.

* strictly speaking it could be closed in the finalize method which could be slightly before it gets GCed, but after it becomes eligible for the GC, but for this discussion this is close enough.

Joachim Sauer
  • 302,674
  • 57
  • 556
  • 614
  • 1
    I think your final remark confuses the finally block with the finalize method. – bowmore Dec 20 '12 at 15:35
  • +1 if we use try with resource block introduced in java 7, PreparedStatement will be closed automtically. – Nandkumar Tekale Dec 20 '12 at 15:38
  • @bowmore: of course. I use `finalize` so rarely that I even mistype it. – Joachim Sauer Dec 20 '12 at 15:39
  • What do you mean by "(if at all)" in your answer? Is there a way that it would be left open after being garbage collected? Or are you saying that it may not ever be garbage collected? – asteri Dec 20 '12 at 16:15
  • @Jeff: it's possible that an external resource is not correctly released when its corresponding Java object is garbage collected. For example if I wrote a class that used native code to allocate a file handle and *did not* write a `finally` method to close the file handle, then my object could get garbage collected, but the file handle would remain open, effectively producing a resource leak. Also: it's very possible that something else (for example the connection) will hold a reference to the PreparedStatement, effectively preventing it from being GCed until the Connection itself can be GCed. – Joachim Sauer Dec 20 '12 at 16:18
1

It'll be closed when the object is eventually garbage collected. Since you have little control over the garbage collection process, it is a good idea to close statements as soon as you're done with them.

From the documentation for Statement.close():

Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources.

NPE
  • 486,780
  • 108
  • 951
  • 1,012
1

It will stay open, and there is no guarantee that it will be closed when garbaged collected. It depends on implementation. Implementation needs to override finalize like in eg FileInputStream.finalize which

"...ensures that the close method of this file input stream is called when there are no more references to it."

But Connection.close can close PreparedStatement, see API for Connection.close:

"..releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released.."

but only if it is not pooled Connection.

Evgeniy Dorofeev
  • 133,369
  • 30
  • 199
  • 275
  • So if the `Connection` that the `PreparedStatement` is prepared from is closed, it will close all of those (lost) `PreparedStatement` objects as well? – asteri Dec 20 '12 at 15:51
  • Yes it will, also DB will release all resources occupied by a session / connection on close. But it may not be the case for pooled connections, because they ignore close() – Evgeniy Dorofeev Dec 20 '12 at 16:05