Queries returning multiple result sets

Question

I have a MSSQL database and am running the following query:

select * from projects; select * from user 

The above query returns two result sets at once, and I cannot fire both queries separately. How can I handle both the result set at once in a Java class?

Answer 1

Correct code to process multiple ResultSets returned by a JDBC statement:

PreparedStatement stmt = ...; boolean isResultSet = stmt.execute();  int count = 0; while(true) {     if(isResultSet) {         rs = stmt.getResultSet();         while(rs.next()) {             processEachRow(rs);         }          rs.close();     } else {         if(stmt.getUpdateCount() == -1) {             break;         }          log.info("Result {} is just a count: {}", count, stmt.getUpdateCount());     }      count ++;     isResultSet = stmt.getMoreResults(); } 

Important bits:

• getMoreResults() and execute() return false to indicate that the result of the statement is just a number and not a ResultSet.
• You need to check stmt.getUpdateCount() == -1 to know if there are more results.
• Make sure you either close the result sets or use stmt.getMoreResults(Statement.CLOSE_CURRENT_RESULT)

Answer 2

You can use Statement.execute(), getResultSet();

PreparedStatement stmt = ... prepare your statement result boolean hasResults = stmt.execute(); while (hasResults) {     ResultSet rs = stmt.getResultSet();     ... your code parsing the results ...     hasResults = stmt.getMoreResults(); }