This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Closing PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Closing PreparedStatement" Watch "Closing PreparedStatement" New topic
Author

Closing PreparedStatement

Brian Ata
Greenhorn

Joined: May 22, 2010
Posts: 27
Hi Guys,

I have a private class with static public methods that return ResultSet objects such as :

public static ResultSet foo (....

since I am returning a ResultSet reference, I cannot close the PreparedStatement since
it immediately closes the the ResultSet, which renders the caller function empty handed
in terms of the populated ResultSet it is expecting.

So my question is, is it safe to leave PreparedStatement not explicitly closed and naturally
depend on GC ? The connection object is for sure closed by the caller function but since
I am using a connection pool, I am not sure but it is logical that the connection may not
be closed but returned to pool which means that there will be no push to close related
objects such as the one I am concerned, PreparedStatement.

Thanks all..



Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60774
    
  65

No!

Expensive resources such as connections, statements and result sets should be closed as soon as possible.

Returning a result set is a poor idea. Rather, copy the data into a relevant data structure and return that. Close all database resources in a timely manner.

[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Brian Ata
Greenhorn

Joined: May 22, 2010
Posts: 27
Thank you Bear, that is what I think, I was just hoping somebody will convince me with good underlying information presented
that nothings bad will happen but thanks again : )

I was actually doing as you suggested, but at some point I want to seperate the SQL from the DAO classes, and at that time
I didn't notice I am not closing the PreparedStatement object. More clearly, that is what I did:

This is from my DAO class, from which I call my ststic SQLStatements class method for the ResultSet object.


The ResultSet object is coming from below, SQLSatements class, and it is where I cannot close the
PreparedStatement object, but the Connection and ResultSet objects are closed by the caller function
above.



Now, I have to refactor again, cancel that static SQLStatements class, and carry the related content back again to my DAO
classses so that I can close the PeparedStatement object after taking what I need from the ResultSet object but I still wonder
if there is really that wrong if one cannot close the PreparedStatement object even if the Connection and ResultSet objects
are closed.

Thanks again!
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30076
    
149

You might have an even bigger problem. When you close the connection, it returns to the pool. If that connection gets used to run another query while you are still using the PreparedStatement, results are unpredictable. Also, many drivers close the PreparedStatement and ResultSet when you close the Connection.

Trying to close the PreparedStatement is functionally incorrect.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Brian Ata
Greenhorn

Joined: May 22, 2010
Posts: 27
Jeanne Boyarsky wrote:You might have an even bigger problem. When you close the connection, it returns to the pool. If that connection gets used to run another query while you are still using the PreparedStatement, results are unpredictable. Also, many drivers close the PreparedStatement and ResultSet when you close the Connection.

Trying to close the PreparedStatement is functionally incorrect.


Thank you Jeanne for clearing a few more question marks in my head.

I understand the problem, but when you said "Trying to close the PreparedStatement is functionally incorrect." , I guess you meant it in the given context, under the situation I presented, it is functionally incorrect, otherwise we should close it always, not rely on specific driver vendor's implementation of behavior.

The problem showed itself when I was trying to apply data base access as an another layer, and while checking available patterns, I realized that Data Access Object pattern combined with Transfer Object pattern, powered by Facade Pattern to enable client access looked like a wonderful idea and I do not mean it is not, but at some point, I didn't like the SQL statements in my DAO classes, that is when I separated that role and introduced an another layer under DAO layer to provide the ResultSets, but I now see it is flawed since I do not see a way to free 100% all resources, in this context PreparedStatement. I checked other patterns to enable that SQLStatement class execute a PreparedStatement, close the PreparedStatement and still return the rows and I found a few workarounds, but in my opinion, it will be a bad approach since all will introduce more overhead , speed penalty, and more importantly complexity increases, I like simple things.

But I still do not like having SQL in DAO, so maybe I will just separate the SQL Queries and let DAO classes get them from a single resource.

Thank you again your time and comments,
If you guys have other recommendations, please advice me, any pointer or reference is greatly appreciated.

Edit: I came across a clear explanation of behavior of PreparedStatements when used with a connection pool.
PreparedStatement w/ Connection Pool
 
Don't get me started about those stupid light bulbs.
 
subject: Closing PreparedStatement
 
Similar Threads
Connection Pooling
Please help clarify this!
PreparedStatement with Connection Pooling
jboss-connection pool problem
Queries regarding PreparedStatement