[Java JDBC] Gọi Stored Procedure có OUT parameter với CallableStatement

Chào các bạn, trong bài viết này, chúng ta sẽ cùng tìm hiểu về cách gọi Stored Procedure có OUT parameter với CallableStatement trong Java

Ví dụ sử dụng JDBC CallableStatement để gọi Stored Procedure có cả tham số IN và OUT.

Đã test với Java 8 và Oracle database 19c

pom.xml
XML:
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc</artifactId>
    <version>8</version>
    <scope>system</scope>
    <systemPath>path.to/ojdbc8.jar</systemPath>
</dependency>

1. JDBC CallableStatement
1.1 PL/SQL stored procedure có cả tham số IN và OUT.

SQL:
CREATE OR REPLACE PROCEDURE get_employee_by_id(
   p_id IN EMPLOYEE.ID%TYPE,
       o_name OUT EMPLOYEE.NAME%TYPE,
   o_salary OUT EMPLOYEE.SALARY%TYPE,
   o_date OUT EMPLOYEE.CREATED_DATE%TYPE)
    AS
    BEGIN

    SELECT NAME , SALARY, CREATED_DATE INTO o_name, o_salary, o_date from EMPLOYEE WHERE ID = p_id;

    END;

1.2 Sử dụng JDBC để gọi stored procedure trên.

StoreProcedureOutParameter.java
Java:
package com.mkyong.jdbc.callablestatement;

import java.math.BigDecimal;
import java.sql.*;

public class StoreProcedureOutParameter {

    public static void main(String[] args) {

        String createSP = "CREATE OR REPLACE PROCEDURE get_employee_by_id( "
                + " p_id IN EMPLOYEE.ID%TYPE, "
                + " o_name OUT EMPLOYEE.NAME%TYPE, "
                + " o_salary OUT EMPLOYEE.SALARY%TYPE, "
                + " o_date OUT EMPLOYEE.CREATED_DATE%TYPE) "
                + " AS "
                + " BEGIN "
                + "     SELECT NAME, SALARY, CREATED_DATE INTO o_name, o_salary, o_date from EMPLOYEE WHERE ID = p_id; "
                + " END;";

        String runSP = "{ call get_employee_by_id(?,?,?,?) }";

        try (Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:orcl", "system", "Password123");
             Statement statement = conn.createStatement();
             CallableStatement callableStatement = conn.prepareCall(runSP)) {

            // create or replace stored procedure
            statement.execute(createSP);

            callableStatement.setInt(1, 3);

            callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
            callableStatement.registerOutParameter(3, Types.DECIMAL);
            callableStatement.registerOutParameter(4, java.sql.Types.DATE);

            // run it
            callableStatement.executeUpdate();

            // java.sql.SQLException: operation not allowed: Ordinal binding and Named binding cannot be combined!
            /*String name = callableStatement.getString("NAME");
            BigDecimal salary = callableStatement.getBigDecimal("SALARY");
            Timestamp createdDate = callableStatement.getTimestamp("CREATED_DATE");*/

            String name = callableStatement.getString(2);
            BigDecimal salary = callableStatement.getBigDecimal(3);
            Timestamp createdDate = callableStatement.getTimestamp(4);

            System.out.println("name: " + name);
            System.out.println("salary: " + salary);
            System.out.println("createdDate: " + createdDate);

        } catch (SQLException e) {
            System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

Download Source Code
$ git clone https://github.com/mkyong/java-jdbc.git


Cám ơn các bạn đã theo dõi. Hẹn gặp lại các bạn trong các bài viết sau :D

Bài viết tham khảo tại: https://mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/
 

Bình luận