Retrieving a known number of result sets from a stored procedure in a JDBC application

Retrieving a known number of result sets from a stored procedure is a simpler procedure than retrieving an unknown number of result sets.

Procedure

To retrieve result sets when you know the number of result sets and their contents, follow these steps:

  1. Invoke the Statement.execute method, the PreparedStatement.execute method, or the CallableStatement.execute method to call the stored procedure.

    Use PreparedStatement.execute if the stored procedure has input parameters.

  2. Invoke the getResultSet method to obtain the first result set, which is in a ResultSet object.
  3. 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.
  4. If there are n result sets, repeat the following steps n-1 times:
    1. Invoke the getMoreResults method to close the current result set and point to the next result set.
    2. Invoke the getResultSet method to obtain the next result set, which is in a ResultSet object.
    3. 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.

Example

The following code illustrates retrieving two result sets. The first result set contains an INTEGER column, and the second result set contains a CHAR column. The numbers to the right of selected statements correspond to the previously described steps.
CallableStatement cstmt;
ResultSet rs;
int i;
String s;
…
cstmt.execute();                        // Call the stored procedure       1 
rs = cstmt.getResultSet();              // Get the first result set        2 
while (rs.next()) {                     // Position the cursor             3 
 i = rs.getInt(1);                      // Retrieve current result set value
 System.out.println("Value from first result set = " + i);  
                                        // Print the value
}
cstmt.getMoreResults();                 // Point to the second result set  4a 
                                        // and close the first result set
rs = cstmt.getResultSet();              // Get the second result set       4b 
while (rs.next()) {                     // Position the cursor             4c 
 s = rs.getString(1);                   // Retrieve current result set value
 System.out.println("Value from second result set = " + s); 
                                        // Print the value
}
rs.close();                             // Close the result set
cstmt.close();                          // Close the statement