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.
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();
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();
}
}
}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
