Retrieving Multiple ResultSet Objects

JDBC for MySQL - Herong's Tutorial Examples

∟Retrieving Multiple ResultSet Objects

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) HerongYang.com. All Rights Reserved.
 */
import java.sql.*;
public class MySqlCallMultipleResultSet {
  public static void main(String [] args) {
    Connection con = null;
    try {
      com.mysql.cj.jdbc.MysqlDataSource ds
        = new com.mysql.cj.jdbc.MysqlDataSource();
      ds.setServerName("localhost");
      ds.setPortNumber(3306);
      ds.setDatabaseName("HerongDB");
      ds.setUser("Herong");
      ds.setPassword("TopSecret");
      ds.setServerTimezone(java.util.TimeZone.getDefault().getID());
      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:

herong> java -cp .:mysql-connector-java.jar \
   MySqlCallMultipleResultSet.java

Head of the table:
 30001, 6a0, e2845, 2027-01-01 14:00:00.0
 30002, 1b0d, 641ed, null
 30003, 2058, b3d46, null
 30004, 1f91, bb9b, null
 30005, f8b, c5e3b, null
 30006, 24ac, 89f1b, null
 30007, 5ec, 7d256, null
 30008, f45, b95c8, null
 30009, 10bb, 636cc, null
 30010, 1e3a, b6ea, null

Tail of the table:
 40000, 15, c4568, null
 39999, 23a1, 657a7, null
 39998, 225e, 452d1, null
 39997, 1547, 17f73, null
 39996, bda, 718e1, null
 39995, f3d, 825cc, null
 39994, 2690, 16122, null
 39993, 1ed0, e3ec5, null
 39992, 2d7, c614, null
 39991, 1b0, caa32, null

Total number of records: 10000