Reusing ResultSet

Question

I need to run several queries in a row

Statement st = cnx.createStatement();
ResultSet rs = st.executeQuery( "SELECT [good stuff]");
// do something smart with rs
rs = st.execute( "SELECT [better stuff]");
// do something smarter with rs
rs = st.execute( "SELECT [best stuff]");
// you got it
try{ rs.close();} catch( SQLException ignore){};
try{ st.close();} catch( SQLException ignore){};

Is this a problem that the first two ResultSet are not properly closed or is it implicitely done during garbage collection?

Answer 1

I don't know what's your problem, but if you have some problems to run this code, you can try to close connection and open other to make the second query. Some database products, like SQLite, only admit one open connection. If you have any problem with database access, you should try that.

Answer 2

As soon as you execute the 2nd query, the previous ResultSet is automatically closed. And as far as Garbage Collection is concerned, you don't have to worry about that. You can just have a stmt.close() at the end that's all. It will automatically close all the related ResultSet objects.

Take a look at : - ResultSet#close documentation, which says that: -

A ResultSet object is automatically closed by the Statement object that generated it when that Statement object is closed, re-executed, or is used to retrieve the next result from a sequence of multiple results.

If you want to test, whether your resultset gets closed or not, you can use a while loop to iterate over the result set and inside the while loop, create another query and assign it to same result set. You will see that an Exception will be thrown..

ResultSet res = stmt.executeQuery("SELECT * FROM sometable");

while (res.next()) {
    res.getString(1);

    // Closes the previous `ResultSet`
    res = stmt.executeQuery("SELECT * FROM othertable");
} 

So, in the above code, on the 2nd iteration, you will get an Exception: - Cannot perform operation after ResultSet is closed