Can we fulfill some queries while passing results in java

Question

I am trying to implement a task in Java using JDBC as a stored procedure in SQL. In SQL, when we write the cursor, we first execute the select query, and then select the records and perform some actions.

I probably ran a fetch request 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..... . . . .. } 

Since my fetch request contains 30 records, but when I execute it, while (res.next ()) time is executed only once.

But instead of asking, I'm just trying to display a field to check if it selects or not, then it works fine .. (although the loop contains only System.out.println statements)

 sql="SELECT * FROM tbl_name"; res=stmt.executeQuery(sql); while(res.next()) { SOP("fields : "+res.getString(0)); } 

(I think that when there is a revision of the result set, and if there is between the requests, then the requests receive execution, but at the same time, the cycle also receives execution, and after a while, when the execution of the requests ends with it, while the result set cycle also ends and therefore it is executed once. I am not sure about that.)

Why this is happening, I do not understand. Is something I am doing wrong?

Answer 1

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

By default, only one ResultSet can be opened for a Statement. in the same time. Therefore, if reading one ResultSet object alternates with reading another, each must be generated by different Statement objects. All execution methods in the Application interface implicitly closes the current ResultSet if one exists.

Calling executeQuery inside your loop implicitly closes the external ResultSet , so you only see one line.

Answer 2

I would restructure your flow. First of all, do not try to reuse the same Statement object to execute a new request. For example, when I try to use the PostgreSQL driver, I easily get the exception: "This ResultSet is closed."

Instead, rewrite it 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.

Answer 3

This is not so, you can try until your request is completed, the loop will wait.

Answer 4

Ideally, you can have only one statement executed at a time in relation to one database connection, so that you can either create and execute the second statement, or iterate over the result set from the first statement and save the data in the collection (for example, in arraylist hashmap), then close this statement and run the second one, this time extracting the identifier from the collection to which you saved them.