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();
}

Answer 3

Yes, You can. See this MSDN article https://msdn.microsoft.com/en-us/library/ms378758(v=sql.110).aspx

public static void executeStatement(Connection con) {
   try {
      String SQL = "SELECT TOP 10 * FROM Person.Contact; " +
                   "SELECT TOP 20 * FROM Person.Contact";
      Statement stmt = con.createStatement();
      boolean results = stmt.execute(SQL);
      int rsCount = 0;

      //Loop through the available result sets.
     do {
        if(results) {
           ResultSet rs = stmt.getResultSet();
           rsCount++;

           //Show data from the result set.
           System.out.println("RESULT SET #" + rsCount);
           while (rs.next()) {
              System.out.println(rs.getString("LastName") + ", " + rs.getString("FirstName"));
           }
           rs.close();
        }
        System.out.println();
        results = stmt.getMoreResults();
        } while(results);
      stmt.close();
      }
   catch (Exception e) {
      e.printStackTrace();
   }
}

I've tested that and it works fine.

Answer 4

public static void executeProcedure(Connection con) {
   try {
      CallableStatement stmt = con.prepareCall(...);
      .....  //Set call parameters, if you have IN,OUT, or IN/OUT parameters

      boolean results = stmt.execute();
      int rsCount = 0;

      //Loop through the available result sets.
     while (results) {
           ResultSet rs = stmt.getResultSet();
           //Retrieve data from the result set.
           while (rs.next()) {
        ....// using rs.getxxx() method to retrieve data
           }
           rs.close();

        //Check for next result set
        results = stmt.getMoreResults();
      } 
      stmt.close();
   }
   catch (Exception e) {
      e.printStackTrace();
   }
}

Answer 5

Before use java, you need look at the RESULT SETS clause.

MSSQL has this feature that can help you with your java code, in a more practical way.

This example will exec two queries:

EXEC('SELECT id_person, name, age FROM dbo.PERSON; SELECT id_url, url FROM dbo.URL;')
WITH RESULT SETS
(
  (
    id_person BIGINT,
    name VARCHAR(255),
    age TINYINT
  ),
  (
    id_url BIGINT,
    url VARCHAR(2000)
  )
);

You can use stored procedures with RESULT SETS as well.

More about: https://technet.microsoft.com/en-us/library/ms188332(v=sql.110).aspx

Answer 6

The UNION ALL query allows you to combine the result sets of 2 or more "select" queries. It returns all rows (even if the row exists in more than one of the "select" statements).

Each SQL statement within the UNION ALL query must have the same number of fields in the result sets with similar data types.........

select * from projects
UNION ALL
select * from user

Answer 7

The answer: it is NOT possible. The only way: Run them as separate queries.