008. CallableStatement

JDBC
- CallableStatement 설명

• CallableStatement

■■■ CallableStatement ■■■
// 호출이 가능한 작업객체~ 개발자로서 많이 사용되지는 않음
// PreparedStatement 를 가장 많이 활용하게 됨 -- 왜냐면 사용하는 것 만으로도 Secure Coding 가능
-----------------------------------------------------------------------------------------------------------
1. CallableStatement 인터페이스는 모든 DBMS에 대한
   표준 방법으로 저장 프로시저(stored procedure) 를 호출하는 방법을 제공하는 인터페이스이며,
   호출은 escape 문법으로 작성된다.
-----------------------------------------------------------------------------------------------------------
2. 결과 매개변수를 가지는 형태와 결과 매개변수가 없는 형태(두 가지)이며
   OUT 매개변수의 종류인 결과 매개변수는
   저장 프로시저에 대한 반환값이다.
   // ▼ 핵심 ▼
   두 형태 모두 IN 매개변수(입력), OUT 매개변수(출력), INOUT 매개변수(입출력)
   를 위해 사용되는 매개변수의 변수 번호를 가지며
   『?』는 매개변수의 저장 위치로 나타낸다.
-----------------------------------------------------------------------------------------------------------
3. 형식 및 구조
   (JDBC 에서 저장 프로시저 호출을 위한 문법)
   // 쿼리문을 직접 운용할 필요가 없음 -> 함수나 프로시저가 처리

   매개변수가 없는 저장 프로시저 호출
   {call procedure_name}
   IN 매개변수를 넘겨주는 프로시저 호출
   {call procedure_name[(?, ?, ...)]}
   OUT 매개변수를 반환하는 프로시저 호출 / 반환자료형이 존재하는 함수 호출
   {? = call procedure_name[(?, ?, ...)]}
-----------------------------------------------------------------------------------------------------------
4. CallableStatement 객체 생성
   CallableStatement 객체는
   Connection 의 prepareCall() 메소드에 의해 생성된다.
   예를 들어, 두 개의 인자값을 가지고 있으며 결과 매개변수가 없는
   저장 프로시저 preData 를 호출하는 방법은 다음과 같다.

   CallableStatement cstmt = conn.prepareCall("{call preData(?, ?)}");

   ※ 물음표(『?』)의 위치가 IN, OUT, INOUT  매개변수 중
      어떤 것인가 하는 것은 저장 프로시저 prcData 에 의존한다.
-----------------------------------------------------------------------------------------------------------
5. IN, OUT 매개변수
   // 매개변수의 정의는 DB 상에서 규정된 것을 사용 -> 이 때문에 사용빈도가 낮음
   CallableStatement 객체로 IN 매개변수를 넘겨주는 것은
   PreparedStatement 로 부터 상속받은 『setXxx()』 메소드이다.
   예를 들어, setFloat() 메소드는 float 값을 넘겨주게 되는 것이다.

   만약, 저장 프로시저가 OUT 매개변수를 반환한다면
   각 OUT 매개변수의 SQL 형(Type)은 CallblesStatement 객체를
   실행할 수 있는 상태가 되기 전까지 등록되어져야 한다.
   이는 몇 몇 DBMS 들이 SQL형(Type)을 필요로 하기 때문이며,
   이 때, SQL 형(Type) 을 등록하는 것은 registerOutPatrameter() 메소드를 사용하게 된다.
   그리고, SQL 구문이 실행되어진 다음에
   CallableStatement 의 『getXxx()』 메소드는 매개변수의 값을 검색하며,
   사용되는 정확한 『getXxx()』 메소드는 그 매개변수를 위해 등록된
   SQL 에 상응하는 자바 형(Type)이다.
   즉, registerOutParameter() 메소드는 SQL 타입을 사용하고, 
   getXxx() 메소드는 이것을 자바 타입으로 캐스트(cast) 하는 것이다.

---

 

close project / 열 때는 openProject 사용하면 됨

---

JDBC08

• DBConn 생성

/*=================
	DBConn.java
	- throws 
===================*/

package com.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConn
{
	private static Connection dbConn;
	
	public static Connection getConnection() throws ClassNotFoundException, SQLException
	{
		if (dbConn == null)
		{
			String url = "jdbc:oracle:thin:@localhost:1521:xe";
			String user = "scott";
			String pwd = "user";
		
			Class.forName("oracle.jdbc.driver.OracleManager");
			dbConn = DriverManager.getConnection(url,user,pwd);
		}
		
		return dbConn;
	}
	
	public static Connection getConnection(String url, String user, String pwd) throws ClassNotFoundException, SQLException
	{
		if (dbConn == null)
		{
			Class.forName("oracle.jdbc.driver.OracleManager");
			dbConn = DriverManager.getConnection(url, user, pwd);
		}

		return dbConn;
	}
	
	public static void Close() throws SQLException
	{
		if (dbConn != null)
		{
			if (!dbConn.isClosed())
			{
				dbConn.close();
			}
		}
		dbConn = null;
	}
}

---

SQL DEVELOPER

SELECT USER
FROM DUAL;
--==>> SCOTT

SELECT *
FROM TBL_MEMBER
ORDER BY SID;
--==>> 
/*
1	별희강	010-1111-1111
2	수인주	010-2222-2222
3	기배리	010-3333-3333
4	하경최	010-4444-4444
5	희정권	010-5555-5555
6	이서최	010-6666-6666
7	상훈문	010-7777-7777
8	준복이	010-8888-8888
*/

TRUNCATE TABLE TBL_MEMBER;
--==>> Table TBL_MEMBER이(가) 잘렸습니다.

SELECT *
FROM TBL_MEMBER
ORDER BY SID;
--==>> 조회결과 없음

--○ CallableStatement 실습을 위한 프로시저 작성
-- 프로시저 명   : PRC_MEMBERINSERT
-- 프로시저 기능 : TBL_MEMBER 테이블에 데이터를 입력하는 입력 프로시저
--// 매개변수 이름, 전화번호 받아오면 SID 를 MAX() + 1 로 구성하는 프로시저
CREATE OR REPLACE PROCEDURE PRC_MEMBERINSERT
(   V_NAME    IN  TBL_MEMBER.NAME%TYPE
,   V_TEL     IN  TBL_MEMBER.TEL%TYPE
)
IS
    -- 주요 변수 선언
    V_SID TBL_MEMBER.SID%TYPE;
BEGIN
    -- 기존 SID 의 최대값 얻어오기   
    SELECT NVL(MAX(SID),0) INTO V_SID
    FROM TBL_MEMBER;

    -- INSERT 구문 
    INSERT INTO TBL_MEMBER(SID, NAME, TEL) VALUES((V_SID+1), V_NAME, V_TEL);

    -- 커밋
    COMMIT;
END;
--==>> Procedure PRC_MEMBERINSERT이(가) 컴파일되었습니다.

--○ 생성된 프로시저 테스트(정상 작동여부 확인)
EXEC PRC_MEMBERINSERT('건우박','010-1111-1111');
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

--○ 테이블 조회
SELECT *
FROM TBL_MEMBER
ORDER BY SID;
--==>> 1	건우박	010-1111-1111

---

• Test001
  - DML 쿼리문

/*=======================================================
	Test001.java
	- CallableStatement 를 활용한 SQL 구문 전송 실습 
=========================================================*/

package com.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.Scanner;

import com.util.DBConn;

public class Test001
{
	public static void main(String[] args)
	{
		try
		{
			Scanner sc = new Scanner(System.in);
			Connection conn = DBConn.getConnection();
		
			do
			{
				System.out.print("이름 입력(-1 종료) : ");
				String name = sc.next();
				
				if (name.equals("-1"))
					break;
				
				System.out.print("전화번호 입력 : ");
				String tel = sc.next();
				
				if (conn != null)
				{
					System.out.println("데이터베이스 연결 성공~");
					
					try
					{
						// 쿼리문 준비 check
						//-- PreparedStatement를 상속받아 쿼리문을 먼저 준비해야함
						String sql = "{call PRC_MEMBERINSERT(?,?)}";
						
						// CallableStatement 작업 객체 생성 check
						CallableStatement cstmt = conn.prepareCall(sql);
						
						// 매개변수 전달
						cstmt.setString(1, name);
						cstmt.setString(2, tel);
						
						//-- Statement들은 executeUpdate, executeQuery 를 사용하는 기준은 동일하다.
						int result = cstmt.executeUpdate();
						
						if (result > 0)
							System.out.println("프로시저 호출 및 데이터 입력 완료~");
						
					} catch (Exception e)
					{
						System.out.println(e.toString());
					}
				}
			} while (true);
			
			DBConn.Close();
			
			System.out.println("\n데이터베이스 연결 종료...");
			System.out.println("프로그램 종료됨...");
			
		} catch (Exception e)
		{
			System.out.println(e.toString());
		}
	}
}

---

SQL DEVELOPER

--○ JDBC 의 Test001 실행 후 확인
SELECT *
FROM TBL_MEMBER
ORDER BY SID;
--==>>
/*
1	건우박	010-1111-1111
2	연수양	010-2222-2222
3	지혜김	010-3333-3333
4	아현한	010-4444-4444
*/

---

SQL DEVELOPER

--○ CallableStatement 실습을 위한 프로시저 작성
-- 프로시저 명   : PRC_MEMBERSELECT
-- 프로시저 기능 : TBL_MEMBER 테이블에 데이터를 읽어오는 입력 프로시저
-- ※ 『SYS_REFCURSOR』 자료형을 이용하여 커서 다루기
--// 오라클에서 기본저긍로 제공하는 커서
CREATE OR REPLACE PROCEDURE PRC_MEMBERSELECT
(   VRESULT OUT SYS_REFCURSOR
)
IS
    -- 주요 변수 선언 → 커서 정의(X)
BEGIN
    OPEN VRESULT FOR            --FOR 은 각각에 집어넣을 때
        SELECT SID, NAME, TEL   --┐
        FROM TBL_MEMBER         --┃-- 이 구문 안에 집어넣는다는 뜻
        ORDER BY SID;           --┘
    --// ※ 외부에서 쓰려고 만든 커서이기 때문에 닫으면 안됨
    --CLOSE VRESULT;
    
    --// ※ DML 구문이 아니기 때문에 사용하지 않는다.
    --COMMIT;
END;
--> Procedure PRC_MEMBERSELECT이(가) 컴파일되었습니다.

---

• Test002
  - Select 쿼리문

/*=======================================================
	Test002.java
	- CallableStatement 를 활용한 SQL 구문 전송 실습 
=========================================================*/

package com.test;

import java.sql.CallableStatement;
import java.sql.Connection;

import com.util.DBConn;

import oracle.jdbc.internal.OracleTypes;

public class Test002
{
	public static void main(String[] args)
	{
		try
		{
			Connection conn = DBConn.getConnection();
			
			if (conn != null)
			{
				System.out.println("데이터베이스 연결 성공~");
				
				try
				{
					// 쿼리문 준비 check
					String sql = "{call PRC_MEMBERSELECT(?)}";
					
					// CallableStatement 작업 객체 생성 check
					CallableStatement cstmt = conn.prepareCall(sql);
					
					// chekck
					// 프로시저 내부에서 SYS_REFCURSOR 를 사용하고 있기 때문에
					// OracleTypes.CURSOR 를 사용하기 위한 등록 과정이 필요한 상황
					// 1. Project Explorer 상에서 해당 프로젝트 마우스 우클릭
					//  > Build Path > Configure Build Path 클릭
					// 2. 이후 열리는 대화창에서 Libraries 탭 클릭
					//  > 우측 Add External Jars 버튼 클릭
					// 3. 이 때 열리는 파일 등록창에
					//  『ojdbc6.jar』 파일 추가 등록
					// 4. 『import oracle.jdbc.internal.OracleTypes;』 구문 추가 등록
					
					/*
					//--※ Test001 과 비교
					Test001
					get 이나 set 은 자바에서 사용 -- 내부적으로 오라클에 값을 전달할 때,
					오라클은 자료구조를 매핑하는 테이블이 있어 String -> VARCHAR2 로 변환해서 가져옴
					그러나
					Test002
					커서타입으로 넘기는건 오라클에서 제대로확인이 불가능하기 때문에 자바에서 오라클 내부의 타입을 지정해서 건네줌
					*/
					
					// 매개변수 전달 check
					// cstmt.registerOutParameter(1, 오라클에서의커서);
					cstmt.registerOutParameter(1, OracleTypes.CURSOR);


				} catch (Exception e)
				{
					System.out.println(e.toString());
				}
			}
		} catch (Exception e)
		{
			System.out.println(e.toString());
		}
	}
}

※ Build Path 설정
- Oracle CURSOR 사용을 위해서

---

왜 registerOutParameter() 을 사용하는지 설명

 

/*=======================================================
	Test002.java
	- CallableStatement 를 활용한 SQL 구문 전송 실습 
=========================================================*/

package com.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.util.DBConn;

import oracle.jdbc.internal.OracleTypes;

public class Test002
{
	public static void main(String[] args) throws SQLException
	{
		try
		{
			Connection conn = DBConn.getConnection();
			
			if (conn != null)
			{
				System.out.println("데이터베이스 연결 성공~");
				
				try
				{
					// 쿼리문 준비 check
					String sql = "{call PRC_MEMBERSELECT(?)}";
					
					// CallableStatement 작업 객체 생성 check
					CallableStatement cstmt = conn.prepareCall(sql);
					
					// check
					// 프로시저 내부에서 SYS_REFCURSOR 를 사용하고 있기 때문에
					// OracleTypes.CURSOR 를 사용하기 위한 등록 과정이 필요한 상황
					// 1. Project Explorer 상에서 해당 프로젝트 마우스 우클릭
					//  > Build Path > Configure Build Path 클릭
					// 2. 이후 열리는 대화창에서 Libraries 탭 클릭
					//  > 우측 Add External Jars 버튼 클릭
					// 3. 이 때 열리는 파일 등록창에
					//  『ojdbc6.jar』 파일 추가 등록
					// 4. 『import oracle.jdbc.internal.OracleTypes;』 구문 추가 등록
					
					/*
					//--※ Test001 과 비교
					Test001
					get 이나 set 은 자바에서 사용 -- 내부적으로 오라클에 값을 전달할 때,
					오라클은 자료구조를 매핑하는 테이블이 있어 String -> VARCHAR2 로 변환해서 가져옴
					그러나
					Test002
					커서타입으로 넘기는건 오라클에서 제대로확인이 불가능하기 때문에 자바에서 오라클 내부의 타입을 지정해서 건네줌
					*/
					
					// 매개변수 전달 check
					// cstmt.registerOutParameter(1, 오라클에서의커서);
					cstmt.registerOutParameter(1, OracleTypes.CURSOR);
					// 주고 받을 때 
					cstmt.execute();

					// + 수신하는것이 문자 형태로 넘겨 받는것이 아닌 커서라는 객체 자체를 받아오는 것
					// 첫번째 파라미터 값으로 넘겨받겠다.
					// ResultSet 타입으로 받아야 처리 할 수 있는데 가져오는 것은 Object 타입
					// 다운 캐스팅!
					//ResultSet rs = cstmt.getObject(1);
					ResultSet rs = (ResultSet)cstmt.getObject(1);
					
					while (rs.next())
					{
						String sid = rs.getString("SID");
						String name = rs.getString("NAME");
						String tel = rs.getString("TEL");
						
						/*
						String str = String.format("%3s %7s %12s", sid, name, tel);
						System.out.println(str);
						*/
						// 위와 동일한 구문
						System.out.printf("%3s %7s %12s", sid, name, tel);
					}
					rs.close();
					cstmt.close();
					
				} catch (Exception e)
				{
					System.out.println(e.toString());
				}
			}
		} catch (Exception e)
		{
			System.out.println(e.toString());
		}
		
		DBConn.Close();
		
		System.out.println("\n데이터베이스 연결 닫힘...");
		System.out.println("프로그램 종료됨");
	}
}