JDBC - Calling MySQL stored procedure example




Technologies used:   JDK 1.8.0_121 | MySQL 5.7.12 | Eclipse Mars.2 (4.5.2)

In RDBMS, a stored procedure is set of sql statements which is complied and stored in a database to perform a special task.

A stored procedure can have any combination of input, output and input/output parameters.

The CallableStatement#prepareCall() method is used to call a stored procedure from a java program. The following example demonstrates how to call a MySQL stored procedure from a java program using JDBC CallableStatement.

 

Sample Database

Consider the PRODUCT table having the following records.

+----+-----------+-------+
| ID | NAME      | PRICE |
+----+-----------+-------+
|  1 | Pencil    | 15.15 |
|  2 | Pen       | 20.00 |
|  3 | Color Box | 12.45 |
+----+-----------+-------+

 

Stored Procedure Creation

The following is a MySQL stored procedure with IN, OUT and INOUT parameters.

DELIMITER $$
CREATE PROCEDURE  PRODUCT_PROC (
	IN pid int,
    OUT pname varchar(50),
    INOUT pprice decimal(10,2)
)
BEGIN

	declare tempPrice decimal(10,2);
    
    -- Select data
	select name,price into pname,tempPrice from product where id = pid;
    
    -- Update new price
    update product set price=pprice where  id = pid;
    
    -- Return old price
    set pprice= tempPrice;
    
END$$
DELIMITER ;

 

Calling stored procedure from Java program

Here is an example of calling the PRODUCT_PROC stored procedure from java program.

Note - The CallableStatement#registerOutParameter() method must be registered for OUT and INOUT parameters.

StoredProcedureExample.java

package com.boraji.tutorial.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

/**
 * @author imssbora
 */
public class StoredProcedureExample {
   public static void main(String[] args) {
      String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
      String username = "root";
      String password = "admin";
      String sql = "{call PRODUCT_PROC(?,?,?)}";

      try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password); 
            CallableStatement stmt=conn.prepareCall(sql);) {
         
         //Set IN parameter
         stmt.setInt(1, 1);  
         
         //Set OUT parameter
         stmt.registerOutParameter(2, Types.VARCHAR);
         
         //Set INOUT parameter
         stmt.setDouble(3, 15.15);
         stmt.registerOutParameter(3, Types.DOUBLE);
         
         //Execute stored procedure
         stmt.execute();
         
         // Get Out and InOut parameters
         System.out.println("Product Name = "+stmt.getString(2));
         System.out.println("Product Old Price = "+stmt.getDouble(3));
         
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Output

Product Name = Pencil
Product Old Price = 14.15