CODEWITHSUNDEEP
javaresultset
Bhavesh Shah
Bhavesh Shah

Reputation: 3389

Can we execute some queries while traversing resultset in java

I am trying to implement a task in java using JDBC like Stored Procedure in SQL. In SQL, when we write cursor, first we execute select query and then fetching the records we perform some actions.

Likely I have fired a select query in Hive.

sql="SELECT a,c,b FROM tbl_name";
res=stmt.executeQuery();    -----------> CONTAINS 30 RECORDS
while(res.next())
{
     sql="INSERT INTO table .....";
     rs1=stmt.executeQuery();

     sql="SELECT d,e,f FROM table .....";
     rs1=stmt.executeQuery();
     like wise many queries are there.....
.
.
.
..
}

As my select query contains 30 records but when I execute it my while(res.next()) execute only once.

But instead of queries I just try to display field to check whether it is fetching or not then it is working fine.. (while loop contains only System.out.println statements)

sql="SELECT * FROM tbl_name";
res=stmt.executeQuery(sql);

while(res.next())
{
   S.O.P.("fields : "+res.getString(0));
}

(I think that when the resultset is getting traversed and if in between queries are there then queries get the executed but at the same time loop also get executes and after some time when queries execution get finishes by that while loop of resultset also get finishes and hence it get executes for once. I am not sure about it.)

Why it is happening I am not getting. Is anything I am doing wrong?

Upvotes: 6

Views: 9380

Answers (4)

Cameron Skinner
Cameron Skinner

Reputation: 54306

Each Statement can only have one open ResultSet at a time. From the documentation:

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.

The calls to executeQuery inside your loop will implicitly close the outer ResultSet, so that's why you only see one row.

Upvotes: 11

Rahul Borkar
Rahul Borkar

Reputation: 2762

ideally you can only have one statement executing at one moment in time against one database connection so you can either create and execute the second statement, or iterate through the resultset from first statement and store the data in collection (e.g. in an arraylist of hashmap) then close that statement and run the second one, this time retrieving the id's from the collection you saved them in.

Upvotes: 0

Alistair A. Israel
Alistair A. Israel

Reputation: 6567

I would restructure your flow. Primarily, don't try and reuse the same Statement object to execute a new query. When I try that using the PostgreSQL driver, for example, I readily get an exception, "This ResultSet is closed."

Instead, rewrite it to something like this:

Connection conn = DriverManager.getConnection(...);
Statement outerStatement = conn.createStatement();
ResultSet outerResultSet = outerStatement.executeQuery("...");

while (outerResultSet.next()) {
    Statement innerStatement = conn.createStatement();
    ResultSet innerResultSet = innerStatement.executeQuery("...");
    while (innerResultSet.next()) {
        // ...
    }
    innerResultSet.close();
    innerStatement.close();
}
outerResultSet.close();
outerStatement.close();
conn.close();

Of course, surround with try-catch-finally as needed.

Upvotes: 2

Rahul Borkar
Rahul Borkar

Reputation: 2762

It doesn't happen like that, You can try until your query gets executed loop will wait.

Upvotes: 0

Related Questions