Program 1: Calling a Stored Procedure with an IN Parameter
This program calls a stored procedure that accepts an employee ID as an input parameter. The procedure retrieves the employee’s details and returns them as a ResultSet.
MySQL Procedure
DELIMITER //
CREATE PROCEDURE GetEmployee(
IN p_emp_id INT
)
BEGIN
SELECT employee_id, employee_name, department, salary
FROM employee
WHERE employee_id = p_emp_id;
END //
DELIMITER ;
import java.sql.*;
public class CallableINExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
try {
Connection con =
DriverManager.getConnection(
url, username, password);
CallableStatement cstmt =
con.prepareCall(
"{call GetEmployee(?)}");
cstmt.setInt(1, 101);
ResultSet rs = cstmt.executeQuery();
if (rs.next()) {
System.out.println("Employee ID: " +
rs.getInt("employee_id"));
System.out.println("Employee Name: " +
rs.getString("employee_name"));
System.out.println("Department: " +
rs.getString("department"));
System.out.println("Salary: " +
rs.getDouble("salary"));
}
rs.close();
cstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" CallableINExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" CallableINExample
Employee ID: 101
Employee Name: Ravi
Department: Development
Salary: 78500.0Code language: CSS (css)Program 2: Calling a Procedure with an OUT Parameter
This program demonstrates how to retrieve a value returned through an OUT parameter. The employee ID is supplied as an IN parameter, while the employee name is returned through an OUT parameter.
MySQL Procedure
DELIMITER //
CREATE PROCEDURE GetEmployeeName(
IN p_emp_id INT,
OUT p_emp_name VARCHAR(100)
)
BEGIN
SELECT employee_name
INTO p_emp_name
FROM employee
WHERE employee_id = p_emp_id;
END //
DELIMITER ;
import java.sql.*;
public class CallableOUTExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
try {
Connection con =
DriverManager.getConnection(
url, username, password);
CallableStatement cstmt =
con.prepareCall(
"{call GetEmployeeName(?, ?)}");
cstmt.setInt(1, 101);
cstmt.registerOutParameter(
2, Types.VARCHAR);
cstmt.execute();
String name =
cstmt.getString(2);
System.out.println(
"Employee Name: " + name);
cstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" CallableOUTExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" CallableOUTExample
Employee Name: Ravi
PS D:\test>Code language: CSS (css)Program 3: Retrieving Multiple OUT Parameters
This program retrieves both the employee name and salary using OUT parameters. It demonstrates how multiple output parameters can be registered and retrieved from a stored procedure.
MySQL Procedure
DELIMITER //
CREATE PROCEDURE GetEmployeeDetails(
IN p_emp_id INT,
OUT p_emp_name VARCHAR(100),
OUT p_salary DOUBLE
)
BEGIN
SELECT employee_name, salary
INTO p_emp_name, p_salary
FROM employee
WHERE employee_id = p_emp_id;
END //
DELIMITER ;
import java.sql.*;
public class MultipleOUTExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
try {
Connection con =
DriverManager.getConnection(
url, username, password);
CallableStatement cstmt =
con.prepareCall(
"{call GetEmployeeDetails(?, ?, ?)}");
cstmt.setInt(1, 101);
cstmt.registerOutParameter(
2, Types.VARCHAR);
cstmt.registerOutParameter(
3, Types.DOUBLE);
cstmt.execute();
String name =
cstmt.getString(2);
double salary =
cstmt.getDouble(3);
System.out.println("Employee Name: " + name);
System.out.println("Salary: " + salary);
cstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" MultipleOUTExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" MultipleOUTExample
Employee Name: Ravi
Salary: 78500.0Code language: CSS (css)Program 4: Using an INOUT Parameter
An INOUT parameter receives a value from Java and returns a modified value from the procedure. This program sends a salary amount to the procedure and receives the salary after adding a bonus.
MySQL Procedure
DELIMITER //
CREATE PROCEDURE AddBonus(
INOUT p_salary DOUBLE
)
BEGIN
SET p_salary = p_salary + 5000;
END //
DELIMITER ;
import java.sql.*;
public class InOutExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
try {
Connection con =
DriverManager.getConnection(
url, username, password);
CallableStatement cstmt =
con.prepareCall(
"{call AddBonus(?)}");
cstmt.setDouble(1, 50000);
cstmt.registerOutParameter(
1, Types.DOUBLE);
cstmt.execute();
double updatedSalary =
cstmt.getDouble(1);
System.out.println(
"Original Salary: 50000.0");
System.out.println(
"Salary After Bonus: " +
updatedSalary);
cstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" InOutExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" InOutExample
Original Salary: 50000.0
Salary After Bonus: 55000.0Code language: CSS (css)Program 5: Calling a Stored Function Using CallableStatement
A stored function returns a value directly to the calling program. This program calls a MySQL function that calculates the annual salary from the monthly salary.
MySQL Function
DELIMITER //
CREATE FUNCTION CalculateAnnualSalary(
monthly_salary DOUBLE
)
RETURNS DOUBLE
DETERMINISTIC
BEGIN
RETURN monthly_salary * 12;
END //
DELIMITER ;
import java.sql.*;
public class CallableFunctionExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
try {
Connection con =
DriverManager.getConnection(
url, username, password);
CallableStatement cstmt =
con.prepareCall(
"{? = call CalculateAnnualSalary(?)}");
// Register function return value
cstmt.registerOutParameter(
1, Types.DOUBLE);
// Set input value
cstmt.setDouble(2, 50000);
cstmt.execute();
double annualSalary =
cstmt.getDouble(1);
System.out.println(
"Monthly Salary: 50000.0");
System.out.println(
"Annual Salary: " +
annualSalary);
cstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" CallableFunctionExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" CallableFunctionExample
Monthly Salary: 50000.0
Annual Salary: 600000.0Code language: CSS (css)