This section describes how to retrieve multiple ResultSet objects from a stored procedure call.
If a stored procedure is returning multiple result sets, you should execute its CallableStatement object with the execute() method.
In the case, an internal pointer will be maintained inside the CallableStatement object. This pointer is pointing the current result, which
could be a result set or a update count.
If the current result is a result set, you can use the getResultSet() method to retrieve it into a ResultSet object.
If the current result is an update count, you can use the getUpdateCount() method to retrieve it as an integer.
To move the internal pointer to the next result, you can use the getMoreResult() method.
In a previous tutorial, I defined a store procedure called, HeadTail(), which returns 2 result sets.
The program below shows you to create a CallableStatement object to execute this stored procedure:
/**
* MySqlCallMultipleResultSet.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.sql.*;
public class MySqlCallMultipleResultSet {
public static void main(String [] args) {
Connection con = null;
try {
com.mysql.jdbc.jdbc2.optional.MysqlDataSource ds
= new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();
ds.setServerName("localhost");
ds.setPortNumber(3306);
ds.setDatabaseName("HerongDB");
ds.setUser("Herong");
ds.setPassword("TopSecret");
con = ds.getConnection();
// Create CallableStatement
CallableStatement cs = con.prepareCall("CALL HeadTail(?)");
// Register OUT parameters
cs.registerOutParameter(1, java.sql.Types.INTEGER);
// Execute the CALL statement and expecting multiple result sets
boolean isResultSet = cs.execute();
// First ReulstSet object
if (!isResultSet) {
System.out.println("The first result is not a ResultSet.");
return;
}
// First ReulstSet object
System.out.println("Head of the table:");
ResultSet res = cs.getResultSet();
while (res.next()) {
System.out.println(" "+res.getInt("ID")
+", "+res.getString("FirstName")
+", "+res.getString("LastName")
+", "+res.getTimestamp("ModTime"));
}
res.close();
// Move to the next result
isResultSet = cs.getMoreResults();
if (!isResultSet) {
System.out.println("The next result is not a ResultSet.");
return;
}
// Second ReulstSet object
System.out.println("Tail of the table:");
res = cs.getResultSet();
while (res.next()) {
System.out.println(" "+res.getInt("ID")
+", "+res.getString("FirstName")
+", "+res.getString("LastName")
+", "+res.getTimestamp("ModTime"));
}
res.close();
// Retrieve OUT parameters
System.out.println("Total number of records: "+cs.getInt(1));
// Close resource
cs.close();
con.close();
} catch (Exception e) {
System.err.println("Exception: "+e.getMessage());
e.printStackTrace();
}
}
}
The output of the program perfectly matches my expectation:
