This example show you how to register out
parameter for executing a stored procedure using the CallableStatement.registerOutParameter()
method call. We must register the out
parameters before the query execution. The registerOutParameter()
method takes two parameters, the index of the parameter and the sql data type of the out parameter.
package org.kodejava.jdbc;
import java.sql.*;
public class RegisterOutParameter {
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";
public static void main(String[] args) {
try (Connection connection =
DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
// Creates a CallableStatement for executing the stored
// procedure
String query = "call Get_Product_Detail_By_Name(?, ?, ?)";
CallableStatement cb = connection.prepareCall(query);
// Sets the input parameter
cb.setString(1, "Data Structures, Algorithms");
// Registers the out parameters
cb.registerOutParameter(2, Types.VARCHAR);
cb.registerOutParameter(3, Types.DECIMAL);
// Executes the query
cb.executeQuery();
// Gets the query result output
System.out.println("Name : " + cb.getString(1));
System.out.println("Code : " + cb.getString(2));
System.out.println("Price : " + cb.getBigDecimal(3));
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Here is the MySQL stored procedure that we call in the code above.
DELIMITER ;;
DROP PROCEDURE IF EXISTS `Get_Product_Detail_By_Name`;;
CREATE PROCEDURE `Get_Product_Detail_By_Name`(
INOUT product_name VARCHAR(50),
OUT product_code VARCHAR(10),
OUT product_price DECIMAL(10, 2))
BEGIN
SELECT code INTO product_code FROM products WHERE name = product_name;
SELECT price INTO product_price FROM products WHERE name = product_name;
END;;
DELIMITER ;
Maven dependencies
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.0.32</version>
</dependency>
Latest posts by Wayan (see all)
- How do I build simple search page using ZK and Spring Boot? - March 8, 2023
- How do I calculate days between two dates excluding weekends and holidays? - January 12, 2023
- How do I discover the quarter of a given date? - January 11, 2023