Closing PreparedStatement

[Brian Ata]

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]

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.

[Brian Ata]

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.

    

public OperationHours readRestaurantOperationHours(long restaurantID){

        getConnection();
        OperationHours retVal = null;

        try {
            int dayID = DateTimeUtil.getRestaurantDayID(restaurantID);
            retVal = new OperationHours(restaurantID,dayID);
            
            ResultSet results =
                    SQLStatements.getOperationHours(connection, restaurantID, dayID);

            if(results.next()) {

                retVal.setStoreOpeningHour(results.getTime("OpeningAt"));
                retVal.setStoreClosingHour(results.getTime("ClosingAt"));
                retVal.setStoreDeliversFrom(results.getTime("DeliversFrom"));
                retVal.setStoreDeliversTo(results.getTime("DeliversUntill"));
            }

            results.close();
            results=null;
            connection.close();
            connection=null;

         }
         catch(SQLException e){
            e.printStackTrace();
         }
         finally{
            cleanUp();
         }

        return retVal;

    }

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.

   
public static ResultSet getOperationHours(Connection con, long restaurantID){

        ResultSet rs = null;

        try{
            PreparedStatement stmnt=con.prepareStatement(
            "SELECT DayID, OpeningAt, ClosingAt, DeliversFrom, DeliversUntill FROM " +
                    "OperationHours WHERE (RestaurantID = ?) ORDER BY DayID");

            stmnt.setLong(1, restaurantID);

            rs = stmnt.executeQuery();
            
        }
        catch(SQLException e){
            e.printStackTrace();
        }

        return rs;
     }

 

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]

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.

[Brian Ata]

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