Java 예제의 CallableStatement

Java의 CallableStatement는 Java 프로그램에서 저장 프로시저를 호출하는 데 사용됩니다. 저장 프로시저는 일부 작업을 위해 데이터베이스에서 컴파일하는 명령문 그룹입니다. 저장 프로시저는 복잡한 시나리오가 있는 여러 테이블을 처리할 때 유용하며 데이터베이스에 여러 쿼리를 보내는 대신 필요한 데이터를 저장 프로시저로 보내고 데이터베이스 서버 자체에서 논리를 실행할 수 있습니다.

CallableStatement

-- For 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 {
			// load the Driver Class
			Class.forName(DB_DRIVER_CLASS);

			// create the connection now
			con = DriverManager.getConnection(DB_URL,DB_USERNAME,DB_PASSWORD);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
}

CallableStatement 예

Employee 테이블에 데이터를 삽입하는 간단한 저장 프로시저를 작성해 보겠습니다. 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 프로시저는 Employee 테이블에 삽입될 호출자의 입력을 기대하고 있습니다. insert 문이 제대로 작동하면 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;
		
		//Read User Inputs
		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);
			
			//register the OUT parameter before calling the stored procedure
			stmt.registerOutParameter(6, java.sql.Types.VARCHAR);
			
			stmt.executeUpdate();
			
			//read the OUT parameter now
			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를 생성하고 OUT 매개변수를 설정한다는 점입니다. CallableStatement registerOutParameter() 메서드로 저장 프로시저를 실행하기 전에 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;

getEmployee 저장 프로시저를 사용하여 직원 데이터를 읽는 Java CallableStatement 예제 프로그램은 다음과 같습니다. 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;
		
		//Read User Inputs
		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);
			
			//register the OUT parameter before calling the stored procedure
			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();
			
			//read the OUT parameter now
			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;
		
		//Read User Inputs
		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);
			
			//register the OUT parameter before calling the stored procedure
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			
			stmt.execute();
			
			//read the OUT parameter now
			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 특정 클래스를 사용하고 있으며 다른 데이터베이스에서는 작동하지 않습니다. OUT 매개변수 유형을 OracleTypes.CURSOR로 설정한 다음 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;

Java 프로그램에서 insertEmployeeObject 저장 프로시저를 호출하는 방법을 살펴보겠습니다. 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;
		
		//Create Object Array for Stored Procedure call
		Object[] empObjArray = new Object[5];
		//Read User Inputs
		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);
			
			//register the OUT parameter before calling the stored procedure
			stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
			
			stmt.executeUpdate();
			
			//read the OUT parameter now
			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 매개변수로 사용할 수도 있고 데이터베이스에서 값을 가져오기 위해 읽을 수도 있습니다. 이것이 저장 프로시저를 실행하기 위한 Java 예제의 CallableStatement에 대한 전부입니다. 여기에서 무언가를 배웠기를 바랍니다.