Returning multiple results (JDBC 3.0)

18 April 2008

If you have worked with JDBC 2, you might be knowing that if your statement is returning multiple results, only one ResultSet can be opened at a time. This is a limitation. Good new is that JDBC 3.0 specification allows the Statement interface to support multiple open ResultSets. Lets see hot this can be done.

Please note that the execute() method closes any ResultSets that were opened from a previous call. To support multiple open results, the Statement interface adds an overloaded version of the method getMoreResults(). This method takes an integer flag which actually specifies the behavior of previously opened ResultSets when the getResultSet() method is called. The interface defines the flags as follows:

CLOSE_ALL_RESULTS
All previously opened ResultSet objects should be closed when calling getMoreResults().

CLOSE_CURRENT_RESULT
The current ResultSet object should be closed when calling getMoreResults().

KEEP_CURRENT_RESULT
The current ResultSet object should not be closed when calling getMoreResults().

Time for an example:

String procCall = "";
// Set the value of procCall to call a stored procedure.
// ...
 
CallableStatement cstmt = connection.prepareCall(procCall);
boolean retval = cstmt.execute();
if (retval == false) {
    // The statement returned an update count, so handle it.
    // ...
} else { // ResultSet
    ResultSet rs1 = cstmt.getResultSet();
    // ...
 
    retval = cstmt.getMoreResults(Statement.KEEP_CURRENT_RESULT);
    if (retval == true) {
        ResultSet rs2 = cstmt.getResultSet();
 
        // Both ResultSets are open and ready for use.
        rs2.next();
        rs1.next();
        // ...
    }
}