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ºÎ ÀúÀÚ:ÃÖ¿µ°ü |