Automatic destruction of Java Statement / Resultset object

Question

I need to clarify some things about how Java deals with unwanted objects. As part of my 'Teach myself Java from wherever I can learn it' course, I have created a library method that returns a ResultSet object:

package libfunc;
.
.
public ResultSet getRecordset(String pSQL) throws Exception {

    ResultSet retVal = null;
    Statement st;
    try  {
         st= con.createStatement();
        retVal = st.executeQuery(pSQL);

    } catch (Exception e) {
        logFile.writeLog(Level.SEVERE, pSQL + "\n" + e);
        throw e;
    }
    return retVal;
}

After gaining a little more knowledge I thought that this may be a better way of doing things:

public ResultSet getRecordset(String pSQL) throws Exception {

    ResultSet retVal = null;
    
    try (Statement st= con.createStatement()) {
         
        retVal = st.executeQuery(pSQL);

    } catch (Exception e) {
        logFile.writeLog(Level.SEVERE, pSQL + "\n" + e);
        throw e;
    }
    return retVal;
}

However, after making the change, the following code in the calling method fails, telling me that the Resultset object is already closed:

package someApp;
.
import libfunc;
.

sql = "select * from a_table";
try (ResultSet rsT = libfunc.getRecordset(sql)) {

    while (rsT.next()) {
        Object[] rowdata = { rsT.getString("field1"), rsT.getString("field2"), rsT.getInt("uid") };
        model.addRow(rowdata);
    }
} catch (Exception e) {
    some code

}

At this point I realised that the returned Resultset object needs the Statement object (st, that is automatically discarded after the try{} block in the second library snippet) to stay alive, hence the original code must stay.

This leaves me wondering - after I've finished with the Resultset, what happens to the Statement object that I created in the library? Does it automatically cease to exist when the Try{} of the calling method is finished?

It is feasible that there are several calls to the same library method, potentially leaving several Statement objects - how can I be certain that I'm not leaving behind any stray objects that will fill up usable memory?

I also call dispose() when I close the Frame that displays the data but am uncertain if that would deal with all the underlying objects.

You need to distinguish between objects existing / not existing (being garbage collected) - and an object's having its `close()` method called, as is the case in the try-with-resource block for auto closeables. Those are two very different things that have nothing to do with each other. The statement object still exists but its internal state has changed such that it is no longer usable for some purposes. — luk2302, Mar 05 '21 at 12:21
The problem with using the first approach however is that many statement objects might survive that are not closed, and those non-closed objects can be a burden on the database (or whatever underlying thing they still have a connection to). — luk2302, Mar 05 '21 at 12:22
you might want to look into try-with-resources and autoCloseable. The gist of it being that your second method uses the try-with-resources which automatically closes your ResultSet. — Paul, Mar 05 '21 at 12:23
@luk2302 - Thanks, you have described my issue - how can I properly dispose of the Statement objects that are no longer needed, given that the only reference I have is via a Resultset that is now closed? — Hippy Steve, Mar 05 '21 at 12:59
The solution is to never pass the resultset or the statement out, you need to retrieve all the data out of the resultset within your method / class and only pass the actual data out. — luk2302, Mar 05 '21 at 13:00
Either don't do this, or call `st.closeOnCompletion()` before returning the result set in your first solution. — Mark Rotteveel, Mar 05 '21 at 15:02

score 0 · Answer 1 · answered Mar 05 '21 at 14:31

OK, after looking at This post I gained a little more insight into how it all works and realised that my problem comes from being a little over-zealous with my concept of library code! By including a connection to the DB in the class that deals with filling the list, I overcome the problem.


    package someApp;
    .
    .
    Connection con = some_db_connection;
    .
    .
    .
    String sql = "select * from a_table";
    try (Statement st = con.createStatement();  ResultSet rsT = st.executeQuery(sql) ) {

            while (rsT.next()) {
                Object[] rowdata = { rsT.getString("Field1"), rsT.getString("field2"), rsT.getInt("uid") };
                model.addRow(rowdata);
            }
        } catch (Exception e) {
            some code

        }

Big thanks to @luk2302 for pointing me in the right direction! Every day is a school day...

Automatic destruction of Java Statement / Resultset object

1 Answers1