

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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
- Related Questions & Answers
- How to call a stored procedure that accepts input parameters, using JDBC program?
- How to call a stored procedure that returns output parameters, using JDBC program?
- How to create a Stored procedure in a database using JDBC API?
- How to call a stored procedure using callable statement in JDBC explain?
- How to create a Stored procedure in Oracle database using JDBC API?
- How to retrieve a DATALINK object from a table using JDBC?
- How to retrieve binary data from a table using JDBC?
- How to call an existing stored procedure in a database using JDBC API?
- How to display message from a stored procedure?
- How to retrieve particular columns of a table using JDBC program?
- How to retrieve Date from a table in JDBC?
- How can I create a MySQL stored procedure that returns multiple values from a MySQL table?
- How to retrieve a record from an existing table in a database using JDBC API?
- How to write a JDBC program to extract data from multiple databases?
- How can we retrieve file from database using JDBC?