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)

adding code snippet to the question will give some perspective rather than what you are doing. — Grinish Nepal, Mar 15 '19 at 00:34
Hi All, thanks for the help. I have added my code part. I am using same Statement object to execute 2 different SQL Select queries. My while loop is executing only once and when the loop is in 2nd iteration, its 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) — Manohar Amkem, Mar 15 '19 at 08:15
Yes you can use one single `Statement`, but once you get a second `ResultSet` from it the first one becomes invalid. — user207421, Mar 15 '19 at 10:14
Edit your example to use a simple analogy for your actual data, so we can follow along intuitively. We don’t know your data structure. — Basil Bourque, Mar 15 '19 at 16:47

score 0 · Answer 1 · answered Mar 16 '19 at 10:30

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).}

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

1 Answers1