Reuse ResultSet

Question

I need to run multiple 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 it a problem that the first two ResultSets are closed incorrectly or are they implicitly executed during garbage collection?

Answer 1

As soon as you execute the second request, the previous ResultSet automatically closed. And as for the Garbage Collection , you don't need to worry about it. You can just have stmt.close() at the end, that's all. It automatically closes all related ResultSet objects.

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

The ResultSet object is automatically closed by the Statement object that generated it when this Statement object is closed, re-executed, or used to extract the next result from a sequence of several Results.

If you want to check if your result set is closed or not, you can use the while loop to iterate over the result set and inside the while loop, create another query and assign it to one result set. You will see that an exception is 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 second iteration, you will get Exception: - Cannot perform operation after ResultSet is closed

Answer 2

I do not know what your problem is, but if you have some problems to run this code, you can try to close the connection and open another to fulfill the second request. Some database products, such as SQLite, allow only one open connection. If you have problems accessing the database, you should try this.