JDBC Execute select statement based on the result from another select statement

Question

I am trying to write a java program that will need to loop through a list of "old values" to get the list of "new values" from an oracle database table. list of OLD values, I would like to pass this list from a shell script, which will be a wrapper for the java program.

a b c

--select statement something like

select new_name from items where old_name = ? //loop through the list of old values

the second step will be to use new_name from above query to use in the below select statement

select msgid from new_name

i am trying to do something like this -

Connection conn = DriverManager.getConnection
     ("jdbc:oracle:thin:@//host:port/SID", "userid", "password");

Statement stmt = conn.createStatement();

for (int i=0; i < arg.length; i++)
{
ResultSet getNewVal = stmt.executeQuery("select new_name from items where old_name = " + old_name[i]);

while (getNewVal.next()){

          String newVal = getNewVal.getString(1);
          ResultSet getMsgID = stmt.executeQuery("select msgid from " + newVal );
          System.out.println (getMsgID.getString(1));

}
}

Issue - ResultSet from first query gets closed and generates an exception as I execute the second query (Closed Resultset: next)

any suggestion?

As requested including example table definition.

create table items ( new_name varchar2(20),old_name  varchar2(20) );
create table new_name ( msgid varchar2(20));

Are you really saying that there are tables named after the values of columns? — Scary Wombat, Aug 26 '19 at 07:52
Do you mean to say that old_name and new_name are both included as table fields? — Kavitha Karunakaran, Aug 26 '19 at 07:56
Why don't you do that with a single query? `select msgid from new_name where new_name in (select new_name from items where old_name = ?)` — , Aug 26 '19 at 08:05
@a_horse_with_no_name new_name is the actual table name in the second query. I thought of what you are suggesting, but did not think i could actually put this 2 sql statements into a single query. — Haris Rahim, Aug 26 '19 at 08:15

score 0 · Accepted Answer · answered Aug 26 '19 at 08:03

You are trying to use the same Statememt object to execute a DB query. If you re-use the same stmt obj to execute another query, it will close the old ResultSet context and you will get the same error/exception.

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

Read this for more information...

While this could be solved in multiple ways, I'm pointing 2 simple solutions that won't require too much changes..

Use another Statement object to execute another query inside the existing ResultSet.

Statement stmt = conn.createStatement();
Statement nestedStmt = conn.createStatement(); // add this line       

.
.
.
ResultSet getMsgID = nestedStmt.executeQuery("select msgid from " + newVal ); // Change this line in while loop
.
.
.

Use a temporary List or any suitable collection to hold the data temporarily, but this could decrease the performance if the result in resultSet is too too large (more RAM consumption my be needed)

To do this,

Create a simple List<String> & add each element of ResultSet into this list, and then iterate over this newly created list (not on ResultSet) then no need to use different Statement object.

I hope this would be helpful.

Thanks, i am using option one, much appreciate your suggestion @miiiii — Haris Rahim, Aug 27 '19 at 07:29
Great, but I would suggest, if you aren't using the result of first query anywhere else, you could go with the `single query execution` approach as suggested by @a_horse_with_no_name, maybe it could help somebody. — miiiii, Aug 27 '19 at 07:47

JDBC Execute select statement based on the result from another select statement

1 Answers1