CallableStatement는 Java 프로그램에서 저장 프로시저를 호출하는 데 사용됩니다. 저장 프로시저는 데이터베이스에서 일부 작업을 수행하기 위해 컴파일하는 일련의 문장들입니다. 저장 프로시저는 복잡한 시나리오와 여러 테이블을 다룰 때 유용하며, 여러 개의 쿼리를 데이터베이스로 보내는 대신 필요한 데이터를 저장 프로시저로 보내고 로직을 데이터베이스 서버에서 실행할 수 있습니다.
CallableStatement
JDBC API는 CallableStatement 인터페이스를 통해 저장 프로시저 실행을 지원합니다. 저장 프로시저는 데이터베이스별 구문으로 작성되어야 하며, 이 튜토리얼에서는 Oracle 데이터베이스를 사용할 것입니다. IN 및 OUT 매개변수와 함께 CallableStatement의 표준 기능을 살펴보겠습니다. 그 후에는 Oracle 특정 STRUCT 및 Cursor 예제를 살펴볼 것입니다. 먼저 아래 SQL 쿼리로 CallableStatement 예제 프로그램을 위한 테이블을 만들어 봅시다. create_employee.sql
-- Oracle DB를 위한 것
CREATE TABLE EMPLOYEE
(
"EMPID" NUMBER NOT NULL ENABLE,
"NAME" VARCHAR2(10 BYTE) DEFAULT NULL,
"ROLE" VARCHAR2(10 BYTE) DEFAULT NULL,
"CITY" VARCHAR2(10 BYTE) DEFAULT NULL,
"COUNTRY" VARCHAR2(10 BYTE) DEFAULT NULL,
PRIMARY KEY ("EMPID")
);
먼저 Oracle 데이터베이스 연결 객체를 얻는 유틸리티 클래스를 만들어 보겠습니다. 프로젝트의 빌드 경로에 Oracle OJDBC jar 파일이 있는지 확인하세요. DBConnection.java
package com.journaldev.jdbc.storedproc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBConnection {
private static final String DB_DRIVER_CLASS = "oracle.jdbc.driver.OracleDriver";
private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:orcl";
private static final String DB_USERNAME = "HR";
private static final String DB_PASSWORD = "oracle";
public static Connection getConnection() {
Connection con = null;
try {
// 드라이버 클래스 로드
Class.forName(DB_DRIVER_CLASS);
// 이제 연결 생성
con = DriverManager.getConnection(DB_URL,DB_USERNAME,DB_PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
}
CallableStatement 예제
사원 테이블에 데이터를 삽입하는 간단한 저장 프로시저를 작성해 보겠습니다. insertEmployee.sql
CREATE OR REPLACE PROCEDURE insertEmployee
(in_id IN EMPLOYEE.EMPID%TYPE,
in_name IN EMPLOYEE.NAME%TYPE,
in_role IN EMPLOYEE.ROLE%TYPE,
in_city IN EMPLOYEE.CITY%TYPE,
in_country IN EMPLOYEE.COUNTRY%TYPE,
out_result OUT VARCHAR2)
AS
BEGIN
INSERT INTO EMPLOYEE (EMPID, NAME, ROLE, CITY, COUNTRY)
values (in_id,in_name,in_role,in_city,in_country);
commit;
out_result := 'TRUE';
EXCEPTION
WHEN OTHERS THEN
out_result := 'FALSE';
ROLLBACK;
END;
insertEmployee 프로시저는 호출자로부터 입력을 받아 사원 테이블에 삽입할 것으로 예상합니다. 삽입 문이 정상적으로 작동하면 TRUE를 반환하고 예외가 발생하는 경우 FALSE를 반환합니다. CallableStatement를 사용하여 직원 데이터를 삽입하기 위해 insertEmployee 저장 프로시저를 실행하는 방법을 살펴보겠습니다. JDBCStoredProcedureWrite.java
package com.journaldev.jdbc.storedproc;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;
public class JDBCStoredProcedureWrite {
public static void main(String[] args) {
Connection con = null;
CallableStatement stmt = null;
// 사용자 입력 읽기
Scanner input = new Scanner(System.in);
System.out.println("Enter Employee ID (int):");
int id = Integer.parseInt(input.nextLine());
System.out.println("Enter Employee Name:");
String name = input.nextLine();
System.out.println("Enter Employee Role:");
String role = input.nextLine();
System.out.println("Enter Employee City:");
String city = input.nextLine();
System.out.println("Enter Employee Country:");
String country = input.nextLine();
try{
con = DBConnection.getConnection();
stmt = con.prepareCall("{call insertEmployee(?,?,?,?,?,?)}");
stmt.setInt(1, id);
stmt.setString(2, name);
stmt.setString(3, role);
stmt.setString(4, city);
stmt.setString(5, country);
// 저장 프로시저 호출 전에 OUT 매개변수 등록
stmt.registerOutParameter(6, java.sql.Types.VARCHAR);
stmt.executeUpdate();
// 이제 OUT 매개변수 읽기
String result = stmt.getString(6);
System.out.println("Employee Record Save Success::"+result);
}catch(Exception e){
e.printStackTrace();
}finally{
try {
stmt.close();
con.close();
input.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
우리는 사용자 입력을 Employee 테이블에 저장하기 위해 읽고 있습니다. PreparedStatement와 다른 유일한 것은 ” {call insertEmployee(?,?,?,?,?,?)} “를 통해 CallableStatement를 생성하고 CallableStatement registerOutParameter() 메서드로 OUT 매개변수를 설정하는 것입니다. 저장 프로시저를 실행하기 전에 OUT 매개변수를 등록해야 합니다. 저장 프로시저를 실행한 후에는 CallableStatement getXXX() 메서드를 사용하여 OUT 객체 데이터를 가져올 수 있습니다. OUT 매개변수를 등록할 때 java.sql.Types를 통해 OUT 매개변수의 유형을 지정해야 합니다. 이 코드는 일반적인 특성을 가지고 있으므로 MySQL과 같은 다른 관계형 데이터베이스에 동일한 저장 프로시저가 있다면 이 프로그램으로 실행할 수 있습니다. 위의 CallableStatement 예제 프로그램을 여러 번 실행할 때의 출력은 다음과 같습니다.
Enter Employee ID (int):
1
Enter Employee Name:
Pankaj
Enter Employee Role:
Developer
Enter Employee City:
Bangalore
Enter Employee Country:
India
Employee Record Save Success::TRUE
-----
Enter Employee ID (int):
2
Enter Employee Name:
Pankaj Kumar
Enter Employee Role:
CEO
Enter Employee City:
San Jose
Enter Employee Country:
USA
Employee Record Save Success::FALSE
두 번째 실행이 실패한 것을 주의하세요. 전달된 이름이 열 크기보다 큽니다. 저장 프로시저에서 예외를 처리하고 이 경우에는 false를 반환합니다.
CallableStatement 예제 – 저장 프로시저 OUT 매개변수
이제 직원 ID로 직원 데이터를 가져오는 저장 프로시저를 작성해 봅시다. 사용자가 직원 ID를 입력하면 프로그램이 직원 정보를 표시합니다. getEmployee.sql
create or replace
PROCEDURE getEmployee
(in_id IN EMPLOYEE.EMPID%TYPE,
out_name OUT EMPLOYEE.NAME%TYPE,
out_role OUT EMPLOYEE.ROLE%TYPE,
out_city OUT EMPLOYEE.CITY%TYPE,
out_country OUT EMPLOYEE.COUNTRY%TYPE
)
AS
BEGIN
SELECT NAME, ROLE, CITY, COUNTRY
INTO out_name, out_role, out_city, out_country
FROM EMPLOYEE
WHERE EMPID = in_id;
END;
자바 CallableStatement 예제 프로그램을 사용하여 getEmployee 저장 프로시저를 사용하여 직원 데이터를 읽는 방법은 다음과 같습니다; JDBCStoredProcedureRead.java
package com.journaldev.jdbc.storedproc;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;
public class JDBCStoredProcedureRead {
public static void main(String[] args) {
Connection con = null;
CallableStatement stmt = null;
// 사용자 입력 읽기
Scanner input = new Scanner(System.in);
System.out.println("Enter Employee ID (int):");
int id = Integer.parseInt(input.nextLine());
try{
con = DBConnection.getConnection();
stmt = con.prepareCall("{call getEmployee(?,?,?,?,?)}");
stmt.setInt(1, id);
// 저장 프로시저 호출 전 OUT 매개변수 등록
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
stmt.registerOutParameter(3, java.sql.Types.VARCHAR);
stmt.registerOutParameter(4, java.sql.Types.VARCHAR);
stmt.registerOutParameter(5, java.sql.Types.VARCHAR);
stmt.execute();
// 현재 OUT 매개변수 읽기
String name = stmt.getString(2);
String role = stmt.getString(3);
String city = stmt.getString(4);
String country = stmt.getString(5);
if(name !=null){
System.out.println("Employee Name="+name+",Role="+role+",City="+city+",Country="+country);
}else{
System.out.println("Employee Not Found with ID"+id);
}
}catch(Exception e){
e.printStackTrace();
}finally{
try {
stmt.close();
con.close();
input.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
다시 프로그램은 일반적이며 동일한 저장 프로시저를 갖는 모든 데이터베이스에 대해 작동합니다. 위의 CallableStatement 예제 프로그램을 실행할 때 출력이 무엇인지 살펴보겠습니다.
Enter Employee ID (int):
1
Employee Name=Pankaj,Role=Developer,City=Bangalore,Country=India
CallableStatement 예제 – 저장 프로시저 Oracle CURSOR
ID를 통해 직원 정보를 읽고 있기 때문에 단일 결과를 얻으며 OUT 매개변수가 데이터를 읽는 데 잘 작동합니다. 그러나 역할이나 국가별로 검색하는 경우 여러 행을 얻을 수 있으며 이 경우 Oracle CURSOR를 사용하여 결과 집합을 읽을 수 있습니다. getEmployeeByRole.sql
create or replace
PROCEDURE getEmployeeByRole
(in_role IN EMPLOYEE.ROLE%TYPE,
out_cursor_emps OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN out_cursor_emps FOR
SELECT EMPID, NAME, CITY, COUNTRY
FROM EMPLOYEE
WHERE ROLE = in_role;
END;
JDBCStoredProcedureCursor.java
package com.journaldev.jdbc.storedproc;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import oracle.jdbc.OracleTypes;
public class JDBCStoredProcedureCursor {
public static void main(String[] args) {
Connection con = null;
CallableStatement stmt = null;
ResultSet rs = null;
// 사용자 입력 읽기
Scanner input = new Scanner(System.in);
System.out.println("Enter Employee Role:");
String role = input.nextLine();
try{
con = DBConnection.getConnection();
stmt = con.prepareCall("{call getEmployeeByRole(?,?)}");
stmt.setString(1, role);
// 저장 프로시저 호출 전 OUT 매개변수 등록
stmt.registerOutParameter(2, OracleTypes.CURSOR);
stmt.execute();
// 현재 OUT 매개변수 읽기
rs = (ResultSet) stmt.getObject(2);
while(rs.next()){
System.out.println("Employee ID="+rs.getInt("empId")+",Name="+rs.getString("name")+
",Role="+role+",City="+rs.getString("city")+
",Country="+rs.getString("country"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
try {
rs.close();
stmt.close();
con.close();
input.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
이 프로그램은 Oracle OJDBC 특정 클래스를 사용하며 다른 데이터베이스와 작동하지 않습니다. 우리는 OracleTypes.CURSOR로 OUT 파라미터 유형을 설정하고 그런 다음 ResultSet 객체로 캐스팅합니다. 코드의 나머지 부분은 단순한 JDBC 프로그래밍입니다. 위의 CallableStatement 예제 프로그램을 실행할 때, 다음과 같은 출력이 나옵니다.
Enter Employee Role:
Developer
Employee ID=5,Name=Kumar,Role=Developer,City=San Jose,Country=USA
Employee ID=1,Name=Pankaj,Role=Developer,City=Bangalore,Country=India
귀하의 Employee 테이블의 데이터에 따라 출력이 다를 수 있습니다.
CallableStatement 예제 – Oracle DB 객체 및 STRUCT
insertEmployee 및 getEmployee 저장 프로시저를 살펴보면, 프로시저 내에서 Employee 테이블의 모든 파라미터를 가지고 있습니다. 열의 수가 증가할수록 혼란스러워지고 오류가 발생할 가능성이 높아질 수 있습니다. Oracle 데이터베이스는 데이터베이스 객체를 생성하는 옵션을 제공하며 Oracle STRUCT를 사용하여 이와 함께 작업할 수 있습니다. 먼저 Employee 테이블 열에 대한 Oracle DB 객체를 정의해 보겠습니다. EMPLOYEE_OBJ.sql
create or replace TYPE EMPLOYEE_OBJ AS OBJECT
(
EMPID NUMBER,
NAME VARCHAR2(10),
ROLE VARCHAR2(10),
CITY VARCHAR2(10),
COUNTRY VARCHAR2(10)
);
이제 EMPLOYEE_OBJ를 사용하여 insertEmployee 저장 프로시저를 다시 작성해 보겠습니다. insertEmployeeObject.sql
CREATE OR REPLACE PROCEDURE insertEmployeeObject
(IN_EMPLOYEE_OBJ IN EMPLOYEE_OBJ,
out_result OUT VARCHAR2)
AS
BEGIN
INSERT INTO EMPLOYEE (EMPID, NAME, ROLE, CITY, COUNTRY) values
(IN_EMPLOYEE_OBJ.EMPID, IN_EMPLOYEE_OBJ.NAME, IN_EMPLOYEE_OBJ.ROLE, IN_EMPLOYEE_OBJ.CITY, IN_EMPLOYEE_OBJ.COUNTRY);
commit;
out_result := 'TRUE';
EXCEPTION
WHEN OTHERS THEN
out_result := 'FALSE';
ROLLBACK;
END;
insertEmployeeObject 저장 프로시저를 java 프로그램에서 어떻게 호출하는지 살펴보겠습니다. JDBCStoredProcedureOracleStruct.java
package com.journaldev.jdbc.storedproc;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;
import oracle.jdbc.OracleCallableStatement;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
public class JDBCStoredProcedureOracleStruct {
public static void main(String[] args) {
Connection con = null;
OracleCallableStatement stmt = null;
//저장 프로 시저 호출을 위한 객체 배열 만들기
Object[] empObjArray = new Object[5];
//사용자 입력 읽기
Scanner input = new Scanner(System.in);
System.out.println("Enter Employee ID (int):");
empObjArray[0] = Integer.parseInt(input.nextLine());
System.out.println("Enter Employee Name:");
empObjArray[1] = input.nextLine();
System.out.println("Enter Employee Role:");
empObjArray[2] = input.nextLine();
System.out.println("Enter Employee City:");
empObjArray[3] = input.nextLine();
System.out.println("Enter Employee Country:");
empObjArray[4] = input.nextLine();
try{
con = DBConnection.getConnection();
StructDescriptor empStructDesc = StructDescriptor.createDescriptor("EMPLOYEE_OBJ", con);
STRUCT empStruct = new STRUCT(empStructDesc, con, empObjArray);
stmt = (OracleCallableStatement) con.prepareCall("{call insertEmployeeObject(?,?)}");
stmt.setSTRUCT(1, empStruct);
//저장 프로 시저 호출 전에 OUT 매개 변수 등록
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
stmt.executeUpdate();
//지금 OUT 매개 변수 읽기
String result = stmt.getString(2);
System.out.println("Employee Record Save Success::"+result);
}catch(Exception e){
e.printStackTrace();
}finally{
try {
stmt.close();
con.close();
input.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
우선 EMPLOYEE_OBJ 데이터베이스 객체와 동일한 길이의 객체 배열을 만듭니다. 그런 다음 EMPLOYEE_OBJ 객체 변수에 따라 값을 설정합니다. 이렇게 하지 않으면 데이터가 잘못된 열에 삽입됩니다. 그런 다음 oracle.sql.StructDescriptor 및 개체 배열을 사용하여 oracle.sql.STRUCT 객체를 만듭니다. STRUCT 객체가 만들어지면 저장 프로 시저에 대한 IN 매개 변수로 설정하고 OUT 매개 변수를 등록하고 실행합니다. 이 코드는 OJDBC API와 강하게 결합되어 있으며 다른 데이터베이스에서는 작동하지 않습니다. 이 프로그램을 실행할 때의 출력은 다음과 같습니다.
Enter Employee ID (int):
5
Enter Employee Name:
Kumar
Enter Employee Role:
Developer
Enter Employee City:
San Jose
Enter Employee Country:
USA
Employee Record Save Success::TRUE
데이터베이스 객체를 OUT 매개 변수로 사용하여 값을 읽을 수도 있습니다. 이것으로 자바에서 Stored Procedures를 실행하는 CallableStatement에 대한 예제를 마칩니다. 여러분은 이것에서 무언가를 배웠으면 좋겠습니다.
Source:
https://www.digitalocean.com/community/tutorials/callablestatement-in-java-example