How to call a stored procedure that returns output parameters, using JDBC program?

A. 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 a stored procedure using the following syntax:

CALL procedure_name (input_parameter1, input_parameter2, input_parameter3)

JDBC provides a standard stored procedure SQL escape syntax using which you can procedures in all RDBMSs

To call a stored procedure using a JDBC program you need to:

• Register the driver: class using the registerDriver() method of the DriverManager class. Pass the driver class name to it, as parameter.

• Establish a connection: Connect ot the database using the getConnection() method of the DriverManager class. Passing URL (String), username (String), password (String) as parameters to it.

• Create Statement: Create a CallableStatement object using the prepareCall() method of the Connection interface.

• Execute the Query: Execute the query using the executeupdate() method of the Statement interface.

Example

Assume we have a table named Sales in the database with the following contents:

+----+-------------+--------------+--------------+--------------+-------+----------------+
| ID | ProductName | CustomerName | DispatchDate | DeliveryTime | Price | Location       |
+----+-------------+--------------+--------------+--------------+-------+----------------+
| 1  | Key-Board   | Raja         | 2019-09-01   | 05:30:00     | 2000  | Hyderabad      |
| 2  | Earphones   | Roja         | 2019-05-01   | 05:30:00     | 2000  | Vishakhapatnam |
| 3  | Mouse       | Puja         | 2019-03-01   | 05:29:59     | 3000  | Vijayawada     |
| 4  | Mobile      | Vanaja       | 2019-03-01   | 04:40:52     | 9000  | Chennai        |
| 5  | Headset     | Jalaja       | 2019-04-06   | 18:38:59     | 6000  | Goa            |
+----+-------------+--------------+--------------+--------------+-------+----------------+

We have created a stored procedure named getProductPrice in the database as shown below:

mysql> DELIMITER // ;
mysql> CREATE PROCEDURE getProductPrice (
          IN in_id INTEGER,
          OUT out_ProdName VARCHAR(20),
          OUT out_CustName VARCHAR(20),
          OUT out_price INTEGER)
       BEGIN
          SELECT ProductName, CustomerName, Price
          INTO out_ProdName, out_CustName, out_price
          FROM Sales where id = in_id;
       END //
Query OK, 0 rows affected (0.04 sec)
mysql> DELIMITER ;

This procedure accepts id of the customer as IN parameter and returns product name (String), customer name (String) and, price (int) values as OUT parameters from the customers table.

Following JDBC program establishes connection with MySQL database, and calls the procedure named getProductPrice, by passing an id value and, retrieves the product name, customer name and price values from the OUT parameters of this procedure and displays the values.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
public class CallngStoredProcedureExample_OUT {
   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 a procedure
      CallableStatement cstmt = con.prepareCall("{call getProductPrice(?, ? ,?, ? )}");
      //Setting the value for the TN parameter
      cstmt.setInt(1, 3);
      //Registering the type of the OUT parameters
      cstmt.registerOutParameter(2, Types.VARCHAR);
      cstmt.registerOutParameter(3, Types.VARCHAR);
      cstmt.registerOutParameter(4, Types.INTEGER);
      //Executing the CallableStatement
      cstmt.executeUpdate();
      //Retrieving the values for product name, customer name and, price
      String product_name = cstmt.getString(2);
      String customer_Name = cstmt.getString(3);
      int price = cstmt.getInt(4);
      System.out.println("Details of the sale with given id are: ");
      //Displaying the values
      System.out.println("Product Name: "+product_name);
      System.out.println("Customer Name: "+customer_Name);
      System.out.println("Price: "+price);
   }
}

Output

Connection established......
Details of the sale with given id are:
Product Name: Mouse
Customer Name: Puja
Price: 3000