A stored procedure is a precompiled group of SQL statements stored in the database and executed when required. In JDBC, the CallableStatement interface is used to call and execute stored procedures from a Java application. The Connection.prepareCall() method creates a CallableStatement object, while setXXX() methods are used to provide input parameters. The execute() method executes the stored procedure, and output values can be retrieved using getXXX() methods after registering them with registerOutParameter().
Program: Executing a Stored Procedure Using CallableStatement
Step 1: Create the Stored Procedure in MySQL
Run the following SQL in MySQL:
DELIMITER //
CREATE PROCEDURE GetEmployeeDetails(
IN emp_id INT,
OUT emp_name VARCHAR(100),
OUT emp_salary DOUBLE
)
BEGIN
SELECT employee_name, salary
INTO emp_name, emp_salary
FROM employee
WHERE employee_id = emp_id;
END //
DELIMITER ;
This procedure accepts an employee ID and returns the employee name and salary through OUT parameters.
Step-2 Now Create the below .java file and paste the code and Execute.
import java.sql.*;
public class CallableStatementExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
try {
// Establish connection
Connection con =
DriverManager.getConnection(
url, username, password);
// Prepare stored procedure call
CallableStatement cstmt =
con.prepareCall(
"{call GetEmployeeDetails(?, ?, ?)}"
);
// Set IN parameter
cstmt.setInt(1, 101);
// Register OUT parameters
cstmt.registerOutParameter(
2, Types.VARCHAR);
cstmt.registerOutParameter(
3, Types.DOUBLE);
// Execute stored procedure
cstmt.execute();
// Retrieve OUT parameters
String employeeName =
cstmt.getString(2);
double salary =
cstmt.getDouble(3);
// Display result
System.out.println("Employee Details");
System.out.println("----------------------");
System.out.println(
"Employee ID: 101");
System.out.println(
"Employee Name: " +
employeeName);
System.out.println(
"Salary: " +
salary);
// Close resources
cstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" CallableStatementExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" CallableStatementExample
Employee Details
----------------------
Employee ID: 101
Employee Name: Ravi
Salary: 78500.0Code language: CSS (css)