¼Ò¼³°°Àº ÀÚ¹Ù 4ºÎ  Á¦19Àå µ¥ÀÌÅͺ£À̽º  19.9 CallableStatement  

 

19.9 CallableStatement

 

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°¡ °¡Áö°í ÀÖ´Â ¸Þ¼­µå¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.



jabookÀúÀÚ¸íÇÔ
Á¦¸ñ:¼Ò¼³°°ÀºÀÚ¹Ù4ºÎ
ÀúÀÚ:ÃÖ¿µ°ü