CallableStatement는 SQL의 스토어드프로시저(Stored Procedure)를 실행시키기 위해 사용되는 인터페이스 입니다. 그럼 스토어드프로시저란 무엇일까요? 간단히 알아보면, query문을 하나의 파일 형태로 만들거나 데이터베이스에 저장해 놓고 함수처럼 호출해서 사용하는 것입니다. 이것을 이용하면 연속되는 query문에 대해서 매우 빠른 성능을 보이며, 여기서는 다루지 않지만 보안문제의 해결 등 상당한 이점이 있으니 개인적으로 DB책을 보고 공부할만할 가치가 충분히 있다고 생각합니다.
그럼 우리가 프로그래밍 하는 데는 또 무슨 이익이 있을까요? 위와 같은 실행능력 향상 외에 자바코드에 query문이 들어 가지 않으므로 자바 코드가 간결해지고 SQL에 독립적이 된다는 것입니다. 결코 그냥 지나칠 수 없는 부분이겠죠?.
스토어드프로시저로 값을 받아오려면, 호출하기에 앞서 반드시 CallableStatement인터페이스의 registerOutParameter()메서드를 호출해야 합니다. 이 인터페이스는 PreparedStatement 인터페이스로부터 상속 받았기 때문에 setXXX()메서드를 사용할 수 있습니다. 그럼 간단한 통해서 예제를 CallableStatement를 이용한 스토어드프로시저의 느낌을 알아 보겠습니다.
|
CallableStatementTest.java(CallableStatement 예제) |
|
import java.sql.*; public class CallableStatementTest{ public static void main(String[] args){ try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con = DriverManager.getConnection("jdbc:odbc:dbdsn", "id", "password"); CallableStatement cs = con.prepareCall("{call myStoredProcedure(?,?,?)}"); cs.setInt(1,2); cs.registerOutParameter(2, java.sql.Types.VARCHAR); cs.registerOutParameter(3, java.sql.Types.INTEGER); cs.execute(); System.out.println("*name : "+ cs.getString(2) +"*age : "+ cs.getInt(3)); cs.close(); con.close(); }catch(Exception e){System.out.println(e);} } } |
|
C:\JavaExample\19>javac CallableStatementTest.java C:\JavaExample\19>java CallableStatementTest *name : Jabook *age : 2 |
|
MS-SQL에서의 스토어드프로시저 myStoredProcedure 작성구문 |
|
CREATE PROCEDURE myStoredProcedure @age int , @na varchar(20) OUTPUT , @ageo int OUTPUT AS
SELECT @na = name, @ageo = age FROM mytest Where age = @age |
예제를 보시면 Java의 코드에는 SQL의 query문이 들어가 있지 않은 것을 아실 것입니다. 그리고 위에 정리해 놓은 것처럼, SQL서버 자체에 스토어드프로시저를 작성하여 query를 작성해 놓았습니다. 위에서 골뱅이(@)가 붙은 것들이 매개변수이고 그 중에서도 뒤에 OUTPUT이라고 붙은 것들이 리턴 될 값들입니다.
CallableStatement객체 cs를 생성하여 프로시저를 호출하기 위한 prepareCall()메서드를 사용하고 있습니다. 여기서 물음표(?)가 프로시저로 전달되고 받아올 매개변수인 것입니다.
n CallableStatement cs = con.prepareCall("{call myStoredProcedure(?, ?, ?)}");
setXXX()메서드를 이용하여 프로시저에 사용할 인자값을 넣어주게 됩니다. 그리고 리턴되는 값들을 받아야 겠죠. 일반 메서드와 달리 여러 개의 인자값을 받을 수 있습니다. 이때 스토어드프로시저에서 넘어오는 값을 얻기 위해서 registerOutParameter()메서드를 이용하여 반환되는 값들을 셋팅하게 됩니다.
n cs.setInt(1,2);
n cs.registerOutParameter(2, java.sql.Types.VARCHAR);
n cs.registerOutParameter(3, java.sql.Types.INTEGER);
반환되는 값을 얻기 위해서는 CallableStatement를 실행한 후 다음과 같이 반환값을 얻어 낼 수 있습니다.
n cs.execute();
n System.out.println("*name : "+ cs.getString(2) +"*age : "+ cs.getInt(3));
정리해 보자면, 이렇게 CallableStatement인터페이스는 데이터베이스의 스토어드프로시저를 호출하기 위해 prepareCall()메서드를 이용하여 CallableStatement객체를 생성합니다. 그 prepareCall()메서드는 Connection인터페이스의 메서드입니다. 스토어드프로시저를 실행하기 전에 받아올 값에 대비하기 위해서 registerOutParameter()메서드를 사용하는 것도 명심해야 할 점입니다.
|
☞ Callable Statement |
데이터베이스의 스토어드프로시저를 실행시키기 위해 사용되는 메서드. 스토어드프로시저를 사용하면 속도, 코드의 독립성, 보안성등의 다양한 이점을 얻을 수 있다. |
|
CallableStatement인터페이스 주요 메서드 |
|
public void registerOutParameter(int parameterIndex, int sqlType) throws SQLException : 프로시저로 받아온 값을 JDBC타입으로 등록합니다. 모든 받아온 값은 반드시 이 과정을 거쳐야 합니다. 대표적인 sqlType을 알아보면 NULL, FOLAT, INTEGER, DATE등이 있습니다. *PreparedStatement클래스를 상속하므로 getXXX()등, PreparedStatement가 가지고 있는 메서드를 사용할 수 있습니다. |
===================================================================================================================
7. CallableStatement
7.1 개요
CallableStatement 객체는 모든 DBMS들에 대한 표준방법으로 저장 프로시져(stored procedure)를 호출하는 방법을 제공한다.
저장 프로시져를 데이터베이스에 저장하고, 저장 프로시져의 호출은 CallableStatement 객체를 사용한다.
이러한 호출은 두가지 형태중의 하나인 escape 문법으로 작성되어진다.
즉, 결과 매개변수를 가지는 형태와 결과 매개변수가 없는 형태.(escape 문법에 대한 내용은 4장 "Statement"를 찹조해라). OUT 매개변수의 종류인 결과 매개변수는 저장 프로시져에 대한 리턴값이다.
두 형태 모두 입력(IN 매개변수), 출력(OUT 매개변수) , 또는 둘다(INOUT 매개변수)를 위해 사용되어지는 매개변수의 변수 번호를 가질 것이다.
?는 매개변수의 위한 저장위치로 취급된다.
JDBC에서 저장 프로시져를 호출하기 위한 문법은 아래와 같다. "[ ]"내의 내용은 선택적이다.
{call procedure_name[(?, ?, ...)]}
결과 매개변수를 리턴하는 프로시져를 위한 문법은 다음과 같다.:
{? = call procedure_name[(?, ?, ...)]}
매개변수가 없는 저장 프로시져를 위한 문법은 다음과 같을 것이다. :
{call procedure_name}
보통, CallableStatement 객체를 생성하는 사람은 이미 사용하는 DBMS가 저장 프로시져를 지원하고 그러한 프로시져들이 있는 곳을 안다는 것이다.
그러나 검사할 필요가 있다면, 다양한 DatabaseMetaData 메쏘드들을 사용하여 정보를 얻는다.
예를 들어, supportsStoredProcedures 메쏘드는 DBMS가 저장 프로시져 호출을 지원하면 true를 리턴하며, getProdedures 메쏘드는 유효한 저장 프로시져의 설명을 리턴한다.
CallableStatement는 일반적으로 SQL문들을 처리하는 Statement 클래스를 상속받고, 또한 IN 매개변수를 처리하는 PreparedStatement 메쏘드들을 상속받는다.
CallableStatement에 정의된 모든 메쏘드들은 OUT 매개변수들이나 INOUT 매개변수의 출력쪽을 처리한다.
즉, 이러한 메쏘드들은 OUT 매개변수의 SQL형들을 등록하거나, 그것들로부터 값들을 검색하거나, 리턴된 값이 SQL NULL 인지를 검사한다.
7.1.1. CallableStatement 객체 생성하기
CallableStatement 객체는 Connection의 prepareCall 메쏘드에 의해 생성된다.
아래의 예제는 저장 프로시져 getTestData의 호출을 포함하는 CallableStatement의 인스턴스를 만든다.
이것은 두 개의 인자들을 가지고 있고 결과 매개변수는 없다.:
CallableStatement cstmt = con.prepareCall("{call getTestData(?, ?)}");
? 위치가 IN, OUT, 또는 INOUT 매개변수인지는 저장 프로시져 getTestData에 의존한다.
7.1.2 IN/OUT 매개변수들
CallableStatement객체로 IN 매개변수를 넘겨주는 것은 PreparedStatement로부터 상속받은 setXXX 메쏘드이다.
넘겨질 값의 데이터형은 사용할 setXXX 메쏘드를 결정한다.(setFloat는 float 값을 넘겨준다.등등)
만약 저장 프로시져가 OUT 매개변수를 리턴한다면, 각 OUT 매개변수의 SQL형은 CallableStatement 객체를 실행할 수 있기 전에 등록되어져야 한다.(이것은 몇몇 DBMS들이 SQL형을 필요로 하기 때문에 필요하다.)
SQL형을 등록하는 것은 registerOutParameter 메쏘드를 사용한다.
그리고나서 SQL문이 실행되어진 다음에, CallableStatement의 getXXX 메쏘드는 매개변수 값을 검색한다.
사용하는 정확한 getXXX 메쏘드는 그 매개변수를 위해 등록되어진 SQL에 상응하는 자바형이다.(SQL형들을 자바형들로의 표준 매핑은 8.5.1장의 테이블에서 보여준다.)
바꾸어말하면, registerOutParameter는 SQL형을 사용하고(데이터베이스가 리턴할 SQL형과 매칭하기위해서), getXXX는 이것을 자바형으로 캐스트(cast)한다.
다음의 코드는 OUT 매개변수들을 등록하고, cstmt에 의해 호출되는 저장 프로시져를 실행하고, 그런다음 OUT 매개변수들내에서 리턴된 값을 검색한다.
getByte 메쏘드는 첫 번째 OUT 매개변수로부터 자바 byte를 검색하고, getBigDecimal은 두 번째 OUT 매개변수로부터 (소수점 뒤에 세 개의 숫자를 가진) BigDecimal 객체를 검색한다.:
CallableStatement cstmt = con.prepareCall("{call getTestData(?, ?)}");
cstmt.registerOutparameter(1, java.sql.Types.TINYINT);
cstmt.registerOutparameter(2, java.sql.Types.DECIMAL, 3);
cstmt.executeQuery();
byte x = cstmt.getByte(1);
java.math.BigDecimal n = cstmt.getBigDecimal(2, 3);
ResultSet와는 달리, CallableStatement는 점진적으로 큰 OUT값들을 검색하기 위한 특별한 메카니즘을 제공하지 않는다.
7.1.3 INOUT 매개변수들
출력을 받아들이고 입력도 공급하는 매개변수(INOUT 매개변수)는 PreparedStatement로부터 상속받은 적당한 setXXX메소드의 호출외에 registerOutParamter 메쏘드의 호출을 요구한다.
setXXX메쏘드는 매개변수의 값을 입력 매개변수로 설정하고 registerOutParameter는 그것의 SQL형을 출력 매개변수로 등록한다.
setXXX 메쏘드는 드라이버가 SQL 값으로 변환된후에 데이터베이스로 전송할 자바 값을 제공한다.
이 IN값의 SQL형과 registerOutParameter 메쏘드에 공급되어지는 값은 같아야만한다.
그런다음 출력값을 검색하기위해서, 상응하는 getXXX 메쏘드를 사용한다.
예를 들어, 자바형이 byte인 매개변수는 입력값을 할당하기위해서 setByte 메쏘드를 사용하고, TINYINT를 registerOutParameter의 SQL형으로 공급하고, 출력값을 검색하기위해서 getByte를 사용해야한다.
(자세한 내용은 8장 "SQL과 자바형들을 매핑하기"를 참조해라)
다음의 예제는 유일한 매개변수로 INOUT 매개변수를 가지고 있는 저장 프로시져 reviseTotal이 있다는 것을 가정한다.
setByte 메쏘드는 매개변수를 25로 설정하며, 이것은 드라이버가 SQL TINYINT로써 데이터베이스에 전송할 것이다.
그런다음 registerOutParameter는 SQL TINYINT로써 매개변수를 등록한다.
저장 프로시져가 실행된 후에, 새로운 SQL TINYINT 값을 리턴하고, getByte 메쏘드는 이 새로운 값을 자바 byte로 검색할 것이다.
CallableStatement cstmt = con.prepareCall("{call reviseTotal(?)}");
cstmt.setByte(1, 25);
cstmt.registerOutparameter(1, java.sql.Types.TINYINT);
cstmt.executeUpdate();
byte x = cstmt.getByte(1);
7.1.4 결과후에 OUT Parameter 검색
몇몇 DBMS들에 있는 제한 때문에, 최대이식성을 위해서 CallableStatement 객체의 실행에 의해 생성된 모든 결과들은 OUT 매개변수들이 CallableStatement.getXXX 메쏘드를 사용하여 검색되기전에 검색되어져야한다.
만약 CallableStatement 객체가 다중 ResultSet 객체들을 리턴한다면(execute 메쏘드 호출을 이용하여), 모든 결과들은 OUT 매개변수를 검색하기전에 검색되어질 것이다. 이러한 경우에, 모든 결과들이 접근되어지는 것을 확인하기위해서, Statement 메쏘드들 getResultSet, getUpdateCount, 그리고 getMoreResults는 더 이상의 결과가 없을때까지 호출되어져야한다.
이것을 실행한후에, OUT 매개변수들로부터의 값들은 CallableStatement.getXXX 메쏘드들을 사용하여 검색될 수 있다.
7.1.5 OUT 매개변수들로써 NULL 값들을 검색하기
OUT 매개변수로 리턴되는 값은 SQL NULL일 것이다. 이러한 것이 발생했을 때, SQL NULL 값은 getXXX 메쏘드에 의해 리턴된 값이 getXXX 메쏘드 형에 의존하여 null, 0 또는 false가 되도록 변환되어져야 한다.
ResultSet 객체에서 처럼, 0 또는 false값이 원래 SQL NULL인지 알 수 있는 유일한 방법은 wasNull 메쏘드를 가지고 검사하는 것이다.
wasNull 메쏘드는 getXXX 메쏘드에 의해 읽은 마지막 값이 SQL NULL 이면 true, 아니면 false를 리턴한다.
자세한 내용은 5장 "ResultSet"은 참조해라.
자바에서 Callablestatement를 통해 stored procedure를 호출하는 방법을 가지고 무진장 헤맸다... ㅠㅠ
그러나 알아내주는게 인지상정.....
<!--------------------------- 프로시져 ---------------------------->
CREATE OR REPLACE PROCEDURE PROC_GETLOGININFO
(
P_ID IN JS_MBR_MASTER.MBR_ID%TYPE,
P_LOGIN_SEQ OUT JS_MBR_LOGIN_INFO.LOGIN_SEQ%TYPE,
P_RESENT_DATE OUT JS_MBR_LOGIN_INFO.RESENT_DATE%TYPE,
P_LOGIN_VALUE OUT JS_MBR_LOGIN_INFO.LOGIN_VALUE%TYPE,
P_NICK_NAME OUT JS_MBR_LOGIN_INFO.NICK_NAME%TYPE,
P_GUBUN OUT JS_MBR_LOGIN_INFO.GUBUN%TYPE,
P_NAME OUT JS_MBR_LOGIN_INFO.NAME%TYPE,
P_NOTE_NY OUT JS_MBR_LOGIN_INFO.NOTE_NY%TYPE,
P_MBR_ID OUT JS_MBR_LOGIN_INFO.MBR_ID%TYPE,
P_AGE OUT JS_MBR_LOGIN_INFO.AGE%TYPE
)
IS
BEGIN
SELECT LOGIN_SEQ, RESENT_DATE, LOGIN_VALUE, NICK_NAME,
GUBUN, NAME, NOTE_NY, MBR_ID, AGE
INTO P_LOGIN_SEQ, P_RESENT_DATE, P_LOGIN_VALUE, P_NICK_NAME,
P_GUBUN, P_NAME, P_NOTE_NY, P_MBR_ID, P_AGE
FROM JS_MBR_LOGIN_INFO
WHERE MBR_ID = P_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END PROC_GETLOGININFO;
/
Grant Execute on hansol_js.PROC_GETLOGININFO to hsjs;
<!--------------------------프로시져 끝 --------------------------------->
프로시져에 보면 파라미터가 10개이다.
IN 타입 1개, OUT 타입 9개..... (그외의 제공 INOUT도 있음 참고)
<!----------------------------- 자바 소스 ----------------------------------->
cs = con.prepareCall("{call PROC_GETLOGININFO(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)} ");
cs.setString(1, mbr_id);
cs.registerOutParameter(2, Types.VARCHAR);
cs.registerOutParameter(3, Types.DATE);
cs.registerOutParameter(4, Types.VARCHAR);
cs.registerOutParameter(5, Types.VARCHAR);
cs.registerOutParameter(6, Types.VARCHAR);
cs.registerOutParameter(7, Types.VARCHAR);
cs.registerOutParameter(8, Types.VARCHAR);
cs.registerOutParameter(9, Types.VARCHAR);
cs.registerOutParameter(10, Types.VARCHAR);
cs.execute();
rs = cs.getResultSet();
<!----------------------------- 자바 소스 끝 ----------------------------------->
처음 call 프로시저명으로 in타입의 파라미터만 넘겨주면 되는줄 알았는데 그건 오산...
아주 큰 오산 ㅠㅠ( 2시간짜리 지식)
out타입의 파라미터도 모두 세팅을 해줘야 한다. 세팅의 방법은 registerOutParameter 메서드를 통해 데이터의 출력 타입에 대해 세팅을 해주는것.
java.sql.Types에 보면 여러가지 타입들이 있는데 맞는것들로 세팅을 해야한다.
그런 다음.. 소스내용 그대로 수행하면 OK~~~~~
즐프~~~~
댓글을 달아 주세요