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 table with multiple header in iText 8? - March 16, 2024
- How do I add Image to a Table in iText 8? - March 11, 2024
- How do I downgrade Android SDK emulator version? - February 12, 2024