How to Retrieve Multiple Result Sets from a Stored Procedure in JDBC?

A stored procedure can process related data and return multiple result sets, this way may save fewer calls to database server. You need to include code to retrieve the result sets, Java JDBC Statement provide the getResultSet method to retrieve each result set. You can access the first result set by calling the getResultSet method on your Statement object. In a loop, position the cursor using the next method, and retrieve data from each column of the current row of the ResultSet object using getXXX methods. To determine if more result sets are available, you can call the getMoreResults method in Statement, which returns a boolean value of true if more result sets are available. If more result sets are available, you can call the getResultSet method again to access them, continuing the process until all result sets have been processed. If the getMoreResults method returns false, there are no more result sets to process. Calling getMoreResults() implicitly closes any previously returned ResultSet object(s) from method getResultSet.

In the following example, an open connection to the Database SQL Server is passed in to the function, and the stored procedure is returns n result sets:

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 retieve data
           }
           rs.close();

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

When you make the call to the getMoreResults() method of the Statement class, the previously returned result set is implicitly closed. How to keep result sets open when you check the next availiable result set. You can call the one with a parameter getMoreResults(int current) method. The parameter current indicates what should happen to current ResultSet objects obtained using the method getResultSet. You can specify one of these constants:

• Statement.KEEP_CURRENT_RESULT : Checks for the next ResultSet, but does not close the current ResultSet.
• Statement.CLOSE_CURRENT_RESULT : Checks for the next ResultSet, and closes the current ResultSet.
• Statement.CLOSE_ALL_RESULTS : Closes all ResultSets that were previously kept open.

Printer-friendly version | Mail this to a friend