[Java] MS-SQL 2000 연동 스토어드 프로시저 호출 관련 소스

구글 소스 코드 검색으로 찾아본 소스들.

개발에 참고용으로 사용할 때 좋다.

1. 본문 보기

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

2. 본문 보기

// 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;
  }

3. 본문 보기

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

4. 본문 보기

    /**
     * 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);
        }
    }