How to retrieve multiple ResultSets from a stored procedure using a JDBC program?

Stored procedures are sub routines, segment of SQL statements which are stored in SQL catalog. All the applications that can access Relational databases (Java, Python, PHP etc.), can access stored procedures.

Stored procedures contain IN and OUT parameters or both. They may return result sets in case you use SELECT statements. Stored procedures can return multiple result sets.

Retrieving Results from a procedure:

You can call an existing stored procedure using the CallableStatement. The prepareCall() method of the Connection interface accepts the procedure call in string format and returns a callable statement object.

CallableStatement cstmt = con.prepareCall("{call sampleProcedure()}");

Execute the above created callable statement using the executeQuery() method this returns a result set object.

//Executing the CallableStatement
ResultSet rs1 = cstmt.executeQuery();

If this procedure returns more result-set objects move to the next result-set using the cstmt.getMoreResults() method.

And then, retrieve the next result-set using the getResultSet() method of the CallableStatement interface.

ResultSet rs2 = cstmt.getResultSet();

Example

Assume we have a table named cricketers_data in the database with the following description:

+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| First_Name     | varchar(255) | YES  |     | NULL    |       |
| Last_Name      | varchar(255) | YES  |     | NULL    |       |
| Year_Of_Birth  | date         | YES  |     | NULL    |       |
| Place_Of_Birth | varchar(255) | YES  |     | NULL    |       |
| Country        | varchar(255) | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

And a table named dispatch_data with the following description:

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| Product_Name     | varchar(255) | YES  |     | NULL    |       |
| Name_Of_Customer | varchar(255) | YES  |     | NULL    |       |
| Dispatch_Date    | date         | YES  |     | NULL    |       |
| Location         | varchar(255) | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+

And we have created a procedure named sampleProcedure which retrieves the contents of these two tables as shown below:

mysql> DELIMITER // ;
mysql> Create procedure sampleProcedure ()
       BEGIN
          Select * from cricketers_data;
          Select * from dispatch_data;
       END//
Query OK, 0 rows affected (0.04 sec)
mysql> DELIMITER ;

Following JDBC example establishes connection with the database, calls the procedure named sampleProcedure, retrieves the result-sets it returns, and prints the contents.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class MultipleResultSetsStoredProcedure {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/mydatabase";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Preparing a CallableStatement to call the retrieveData procedure
      CallableStatement cstmt = con.prepareCall("{call sampleProcedure()}");
      //Executing the CallableStatement
      ResultSet rs1 = cstmt.executeQuery();
      //Displaying the result
      System.out.println("Contents of the first result-set");
      while(rs1.next()) {
         System.out.print("First Name: "+rs1.getString("First_Name")+", ");
         System.out.print("Last Name: "+rs1.getString("Last_Name")+", ");
         System.out.print("Year of Birth: "+rs1.getDate("Year_Of_Birth")+", ");
         System.out.print("Place of Birth: "+rs1.getString("Place_Of_Birth")+", ");
         System.out.print("Country: "+rs1.getString("Country"));
         System.out.println();
      }
      System.out.println(" ");
      cstmt.getMoreResults();
      System.out.println("Contents of the second result-set");
      ResultSet rs2 = cstmt.getResultSet();
      while(rs2.next()) {
         System.out.print("Product Name: "+rs2.getString("Product_Name")+", ");
         System.out.print("Name of Customer: "+rs2.getString("Name_Of_Customer")+", ");
         System.out.print("Dispatch Date: "+rs2.getDate("Dispatch_Date")+", ");
         System.out.print("Location: "+rs2.getString("Location"));
         System.out.println();
      }
   }
}

Output

Connection established......
Contents of the first result-set
First Name: Shikhar, Last Name: Dhawan, Year of Birth: 1981-12-05, Place of Birth: Delhi, Country: India
First Name: Jonathan, Last Name: Trott, Year of Birth: 1981-04-22, Place of Birth: CapeTown, Country: SouthAfrica
First Name: Lumara, Last Name: Sangakkara, Year of Birth: 1977-10-27, Place of Birth: Matale, Country: Srilanka
First Name: Virat, Last Name: Kohli, Year of Birth: 1988-11-05, Place of Birth: Delhi, Country: India
First Name: Rohit, Last Name: Sharma, Year of Birth: 1987-04-30, Place of Birth: Nagpur, Country: India

Contents of the second result-set
Product Name: KeyBoard, Name of Customer: Amith, Dispatch Date: 1981-12-05, Location: Hyderabad
Product Name: Ear phones, Name of Customer: Sumith, Dispatch Date: 1981-04-22, Location: Vishakhapatnam
Product Name: Mouse, Name of Customer: Sudha, Dispatch Date: 1988-11-05, Location: Vijayawada