JAVA에서 MS-SQL 스토어드 프로시저 사용하기에 관한 글을 일전에 정리해서 올린적이 있다.

그 내용과 병행해서 보면 더욱 좋다.

이번에 쓸 내용은 프로시저내에서 output 변수로 선언된 값을 JAVA 에서 출력해주는 부분이다.

출처 : http://pcguy7.springnote.com/pages/1044536

CallableStatement
: SQL의 스토어드프로시저(Stored Procedure)를 실행시키기 위해 사용되는 인터페이스 이다.

스토어드프로시저란
: query문을 하나의 파일 형태로 만들거나 데이터베이스에 저장해 놓고 함수처럼 호출해서 사용하는 것임.
 이것을 이용하면 연속되는 query문에 대해서 매우 빠른 성능을 보인다.
 보안적인 장점 역시 가지고 있음.

 

스토어드프로시저로 값을 받아오려면,
호출하기에 앞서 반드시 CallableStatement인터페이스의 registerOutParameter()메서드를 호출해야 함.
 이 인터페이스는 PreparedStatement 인터페이스로부터 상속 받았기 때문에 setXXX()메서드를 사용할 수 있다.

 

(CallableStatement 예제)

CallableStatementTest.java
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
 


주의 1. Java의 코드에는 SQL의 query문이 들어가 있지 않았음.
 그리고 위에 정리해 놓은 것처럼, SQL서버 자체에 스토어드프로시저를 작성하여 query를 작성해 놓음.
 위에서 골뱅이(@)가 붙은 것들이 매개변수이고 그 중에서도 뒤에 OUTPUT이라고 붙은 것들이 리턴 될 값. 

 

CallableStatement객체 cs를 생성하여 프로시저를 호출하기 위한 prepareCall()메서드를 사용한다.
 여기서 물음표(?)가 프로시저로 전달되고 받아올 매개변수인 것입니다.

 

         CallableStatement cs = con.prepareCall("{call myStoredProcedure(?, ?, ?)}");

 

 setXXX()메서드를 이용하여 프로시저에 사용할 인자값을 넣어주게 됨.
 그리고 리턴되는 값들을 받아야 겠죠. 일반 메서드와 달리 여러 개의 인자값을 받을 수 있음.
 이때 스토어드프로시저에서 넘어오는 값을 얻기 위해서 registerOutParameter()메서드를 이용하여
 반환되는 값들을 셋팅하게 됩니다.
 

         cs.setInt(1,2);
         cs.registerOutParameter(2, java.sql.Types.VARCHAR);
         cs.registerOutParameter(3, java.sql.Types.INTEGER);

 

반환되는 값을 얻기 위해서는 CallableStatement를 실행한 후 다음과 같이 반환값을 얻어 낼 수 있습니다.

         cs.execute();
         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가 가지고 있는 메서드를 사할수 있음.

 

 


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();

registerOutParameter() 이 메소드는 프로시져에서 OUT으로 선언한 변수값


Posted by 현수림

댓글을 달아 주세요

구글 소스 코드 검색으로 찾아본 소스들.
개발에 참고용으로 사용할 때 좋다.

public void testRun() throws Exception {
        CallableStatement cstmt = connection.prepareCall(EXECUTE_PROCEDURE);
        try {
          cstmt.registerOutParameter(2, Types.INTEGER);
          cstmt.registerOutParameter(4, Types.INTEGER);
          cstmt.setInt(1, 5);
          cstmt.setInt(3, 0);
          cstmt.execute();
          int ans = cstmt.getInt(4);
          assertTrue("got wrong answer, expected 120: " + ans, ans == 120);
        } finally {
          cstmt.close();
        }

        PreparedStatement stmt = connection.prepareStatement(SELECT_PROCEDURE);
        try {
          stmt.setInt(1, 5);
          ResultSet rs = stmt.executeQuery();
          assertTrue("Should have at least one row", rs.next());
          int result = rs.getInt(2);
          assertTrue("Wrong result: expecting 120, received " + result, result == 120);

          assertTrue("Should have exactly one row.", !rs.next());
          rs.close();
        } finally {
          stmt.close();
        }

        CallableStatement cs = connection.prepareCall(CALL_SELECT_PROCEDURE);
        try {
          ((FirebirdCallableStatement)cs).setSelectableProcedure(true);
          cs.registerOutParameter(2, Types.INTEGER);
          cs.registerOutParameter(3, Types.INTEGER);
          cs.setInt(1, 5);
          cs.execute();
          ResultSet rs = cs.getResultSet();
          assertTrue("Should have at least one row", rs.next());
          int result = cs.getInt(3);
          assertTrue("Wrong result: expecting 120, received " + result, result == 1);

          int counter = 1;
          while(rs.next()) {
              assertTrue(rs.getInt(2) == cs.getInt(3));
              counter++;
          }

          assertTrue("Should have 6 rows", counter == 6);
          rs.close();
        } finally {
          cs.close();
        }
    }

// ODCIIndexClose
  public java.math.BigDecimal ODCIClose()
                throws java.sql.SQLException
  {
    extdemo3a sbtctx;   // contxt obj that holds the ResultSet and Statement
    OracleResultSet rset;
    PreparedStatement ps;
    System.out.println("in odciclose");

    int key = getScanctx().intValue();
    System.out.println("in odciclose2");

    Connection conn =
        sqlj.runtime.RuntimeContext.getRuntime().getDefaultConnection();
    CallableStatement  cstmt = conn.prepareCall
      ("{CALL dbms_output.put_line(\'Close\')}");
    cstmt.executeUpdate();

    System.out.println("key=" + key);

    // Get the resultSet and statement back from the ContextManager
    // so that we can close them.
    try{
        sbtctx = (extdemo3a)ContextManager.clearContext(key);
    }catch(InvalidKeyException ike){
        System.out.println("ContextManager InvalidKeyException");
        return ERROR;
    }

    rset = (OracleResultSet)sbtctx.getRs();
    ps = (PreparedStatement)sbtctx.getStmt();
    rset.close();
    ps.close();

    return SUCCESS;
  }

    private void checkFilmInStock(int filmId, int storeId, int expectedResult) throws Exception {
        Connection conn = dbconn.getJDBCConnection();
        CallableStatement stmt = conn.prepareCall("{call film_in_stock(?, ?, ?)}");
        stmt.setInt(1, filmId);
        stmt.setInt(2, storeId);
        stmt.registerOutParameter(3, Types.INTEGER);

        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            // Skip results
        }

        assertEquals(expectedResult, stmt.getInt(3));
    }

    private void checkFilmNotInStock(int filmId, int storeId, int expectedResult) throws Exception {
        Connection conn = dbconn.getJDBCConnection();
        CallableStatement stmt = conn.prepareCall("{call film_not_in_stock(?, ?, ?)}");
        stmt.setInt(1, filmId);
        stmt.setInt(2, storeId);
        stmt.registerOutParameter(3, Types.INTEGER);

        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            // Skip results
        }

        assertEquals(expectedResult, stmt.getInt(3));
    }


    /**
     * This calls the stored procedure if not already done. All parameter values are
     * put into m_resultsMap for use later.  This includes "in", "out" and "inout" parameter
     * values.
     *
     * @throws XAwareException
     *             If there is a problem with the select.
     */
    private void executeStoredProcedure() throws XAwareException {
        final String methodName = "executeStoredProcedure";
        XACallableStatementCreator csc = new XACallableStatementCreator(m_operationConfig);
        try {
            m_resultsMap = new HashMap<String, Object>();
            CallableStatement cstmt = csc.createCallableStatement(m_jdbcTemplate.getDataSource().getConnection());
            List<SpParameter> argList = m_operationConfig.getParameters();
            boolean isResultSet = cstmt.execute();
            for (SpParameter param : argList) {
                if (param.isOutBound()) {
                    if (isResultSet && param.isResultSet()) {
                        // We have resultsets
                        // First get the result set according to spec
                        Object rs = cstmt.getResultSet();
                        if (rs == null) {
                            // if not found according to spec try Oracle's way
                            rs = cstmt.getObject(param.getParamIndex());
                        }
                        m_resultsMap.put(param.getName(), rs);
                    }
                    else {
                        if (param.isReturn() && !param.isResultSet() && !isResultSet) {
                            // first lookup the return value according to spec
                            Integer count = cstmt.getUpdateCount();
                            if (count == -1) {
                                // if not found then try Oracle's way
                                count = cstmt.getInt(param.getParamIndex());
                            }
                            m_resultsMap.put(param.getName(), count);
                        }
                        else {
                            Object result = cstmt.getObject(param.getParamIndex());
                            m_resultsMap.put(param.getName(), result);
                        }
                    }
                    // according to spec if getMoreResults() returns false
                    // and getUpdateCount() return -1 then there are no more
                    // results to get.
                    Integer count = cstmt.getUpdateCount();
                    if (isResultSet || count >= 0) {
                        isResultSet = cstmt.getMoreResults();
                    }
                }
                else {
                    // put the in params in for later convenience
                    m_resultsMap.put(param.getName(), param.getValue());
                }
            }
            // Spring way returns closed result sets.
            // m_resultsMap = m_jdbcTemplate.getJdbcTemplate().call(csc, argList);
        }
        catch (Exception e) {
            String msg = ExceptionMessageHelper.getExceptionMessage(e);
            lf.severe(msg, getClass().getSimpleName(), methodName);
            throw new XAwareException(msg);
        }
    }


Posted by 현수림

댓글을 달아 주세요



티스토리 툴바