소설같은JSP2부  8장 JDBC와 JSP  10 CallableStatement  

 

8.10 확장된 JDBC – CallableStatement

 

CallableStatement는 SQL의 스토어드프로시저(Stored Procedure)를 실행시키기 위해 사용되는 인터페이스입니다. 그럼 스토어드프로시저란 무엇일까요? 간단히 알아보면, 쿼리문을 하나의 파일 형태로 만들거나 데이터베이스에 저장해 놓고 함수처럼 호출해서 사용하는 것입니다. 이것을 이용하면 연속되는 쿼리문에 대해서 매우 빠른 성능을 보이며, 여기서는 다루지 않지만 보안 문제의 해결 등 상당한 이점이 있으니 개인적으로 데이터베이스 책을 보고 공부할만한 가치가 충분히 있다고 생각합니다.

 

그럼 우리가 프로그래밍할 때 또 무슨 이익이 있을까요? 위와 같은 실행능력 향상 외에 자바코드에 쿼리문이 들어 가지 않으므로 자바 코드가 간결해지고 SQL에 독립적이 된다는 것입니다. 결코 그냥 지나칠 수 없는 부분이겠죠?.

 

CallableStatement의 특징

n         데이터베이스의 스토어드프로시저를 실행시킬 때 사용

n         속도, 코드의 독립성, 보안성에 상당한 잇점이 있음

 

스토어드프로시저로 값을 받아오려면, 호출하기에 앞서 반드시 CallableStatement 인터페이스의 registerOutParameter 메서드를 호출해야 합니다. 이 인터페이스는 PreparedStatement 인터페이스로부터 상속 받았기 때문에 setXxx 메서드를 사용할 수 있습니다. 그럼 간단한 예제를 통해서 CallableStatement를 이용한 스토어드프로시저의 느낌을 알아 보겠습니다.

 

CallableStatementTest.jsp

CallableStatement를 테스트한 예

<%@page contentType="text/html;charset=euc-kr" import="java.sql.*"%> 
<html>
<body> 
<h3>CallableStatement 테스트 </h3>
<hr>
<%  
    Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); 
    Connection conn = DriverManager.getConnection
("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=noveljsp;user=novel;password=jsp"); 
    CallableStatement cs = conn.prepareCall("{call myStoredProcedure(?,?,?)}"); 
    cs.setInt(1,3);  
    cs.registerOutParameter(2, java.sql.Types.VARCHAR);  
    cs.registerOutParameter(3, java.sql.Types.INTEGER);  
    cs.execute();  
%>
<table border = "1" cellspacing="0" cellpadding="1"> 
  <tr bgcolor = "pink"> 
    <td><b>name</b></td> 
    <td><b>age</b></td> 
  </tr> 
  <tr> 
    <td><%=cs.getString(2)%></td> 
    <td><%=cs.getInt(3)%></td> 
   </tr> 
<% 
    cs.close();  
    conn.close();  
%> 
</table> 
</body>
</html>

 

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

 

예제의 JSP코드에는 SQL의 쿼리문이 들어가 있지 않은 것을 확인할 수 있습니다. 그리고 위에 정리해 놓은 스토어드프로시저 구문을 사용하여 SQL 서버 내에 쿼리문을 저장했습니다. 작성된 스토어드프로시저에 대해 잠시 설명드리겠습니다.

 

첫번째 줄은 스토어드프로시저의 생성과 생성될 스토어드프로시저의 이름을 선언하고 있습니다.

 

n         CREATE PROCEDURE myStoredProcedure

 

두번째 줄부터 네번째 줄까지는 스토어드프로시저에서 사용할 매개변수를 선언하고 있습니다. 매개변수는 그 앞에 @를 붙여서 표시합니다. 그 중에서 getXxx 메서드로 리턴될 매개변수는 OUTPUT을 사용하여 따로 표시합니다. 매개변수는 최대 2100개까지 사용할 수 있으며 여러 개의 매개변수를 선언할 때에는 반드시 콤마(,)를 사용하여 구분해야합니다. 각각의 의미는 다음과 같습니다.

 

n         @‘변수명’ ‘데이터형’ ‘리턴여부’

 

변수의 선언이 끝나면 as를 사용한 후 SQL 구문을 작성합니다.

 

n         SELECT @na=name,@ageo=age FROM MyTest WHERE age=@age

 

이정도만 알고 있어도 스토어드프로시저를 사용하는 데에는 별 문제가 없을 것입니다. 좀더 자세한 내용을 원하신다면 데이터베이스 관련 서적을 보시기 바랍니다.

 

그러면 지금부터 위에서 설명드린 스토어드프로시저를 사용한 CallableStatementTest.jsp예제에 대해 설명드리겠습니다. 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         <td><%=cs.getString(2)%></td>

n         <td><%=cs.getInt(3)%></td>

 

정리해 보자면, 이렇게 CallableStatement 인터페이스는 데이터베이스의 스토어드프로시저를 호출하기 위해 prepareCall 메서드를 이용하여 CallableStatement 객체를 생성합니다. 그 prepareCall 메서드는 Connection 인터페이스의 메서드입니다. 스토어드프로시저를 실행하기 전에 받아올 값에 대비하기 위해서 registerOutParameter 메서드를 사용하는 것도 명심해야 할 점입니다.

 

CallableStatement 인터페이스 주요 메서드

public void registerOutParameter(int parameterIndex, int sqlType) throws SQLException : 프로시저로 받아온 값을 JDBC타입으로 등록합니다. 모든 받아온 값은 반드시 이 과정을 거쳐야 합니다. 대표적인 sqlType을 알아보면 NULL, FOLAT, INTEGER, DATE등이 있습니다.

 

*PreparedStatement클래스를 상속하므로 getXXX()등, PreparedStatement가 가지고 있는 메서드를 사용할 수 있습니다.



jabook저자명함
제목:소설같은JSP2부
저자:최영관