Java JDBC Single Statement object before while loop and inside while loop

Question

I am writing one core java program with JDBC to connect to Oracle.

• I am selecting my First table with a select condition to get few row ids. I got some records selected in the resultset
• Now I am looping the resultset in a while loop (while resultSet.next())
• Then selecting my second table for the rows whose ids are equal to the ids in the 1st resultset

In both selects I am using the same Statement object. While running the program I am getting only 1st row details and then java is throwing the error that resultset is closed

In short, I wanted to know whether I can use one single Statement object before the loop and inside the loop ?

Below is my code sample

allCOBbatchRSet=stmt.executeQuery("SELECT RECID FROM V_F_BATCH WHERE BATCH_STAGE IS NOT NULL");
while (allCOBbatchRSet.next())
{
        BatchRSet=stmt.executeQuery("SELECT XMLRECORD FROM F_BATCH WHERE RECID="+cobBatchRecId);
        BatchRSet.next();
        ............
}

It failed with the exception

java.sql.SQLException: Closed Resultset: next at
oracle.jdbc.driver.InsensitiveScrollableResultSet.ensureOpen(InsensitiveScrollableResultSet.java:109) at
oracle.jdbc.driver.InsensitiveScrollableResultSet.next(InsensitiveScrollableResultSet.java:398) at
com.manohar.t24.COBDetails.getCOBDetails(COBDetails.java:46) 

Answer 1

You cannot reuse a statement while iterating over a result set created by that statement as executing another query (or any other statement type) on that Statement object will automatically close the result set of the previous query execution. This is required by the JDBC API and specification.

If you want to do this, then you need to disable auto-commit* and use two statement objects.

However the code you show is an anti-pattern known as the N+1 query problem, in general you should not loop over a result set to execute other individual selects per row: you can create a single select statement that does that for you. This usually performs a lot better.

For example, you can use:

select V_F_BATCH.RECID, F_BATCH.XMLRECORD 
from V_F_BATCH
inner join F_BATCH
  on F_BATCH.RECID = V_F_BATCH.RECID
where V_F_BATCH.BATCH_STAGE is not NULL 

This will also avoid the potential SQL injection issue your current code has.

---

^{*: Disabling auto-commit is necessary as JDBC requires any statement execution to commit in auto-commit mode. And a commit will also close open result sets unless they are holdable over commit (although some JDBC drivers are lenient in this regard and the result set will not be closed on an auto-commit boundary).}