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 = "kodejava";
private static final String PASSWORD = "s3cr*t";
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(100),
OUT product_code VARCHAR(20),
OUT product_price DECIMAL(10, 2))
BEGIN
SELECT code INTO product_code FROM product WHERE name = product_name;
SELECT price INTO product_price FROM product WHERE name = product_name;
END;;
DELIMITER ;
Maven Dependencies
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.1.0</version>
</dependency>
Latest posts by Wayan (see all)
- How do I create a string of repeated characters? - September 1, 2023
- How do I convert datetime string with optional part to a date object? - August 28, 2023
- How do I split large excel file into multiple smaller files? - April 15, 2023