myBatis3执行存储过程

女爷i 2022-06-16 10:51 188阅读 0赞

使用myBatis3执行存储过程不像iBatis2那样，有procedure这样的标签可以供使用，如下为iBatis2的使用方法：

<procedure id="provCitySplit" parameterMap="provCitySplitParameterMap" resultClass="java.lang.Long">
    
      {call PKG_SUB_POLICY.PROV_CITY_SPLIT(?,?,?,?)}
    
    </procedure>

parameterMap="provCitySplitParameterMap"的定义如下：

java调用方法如下：

@Override
    
    public Long callProvCitySplitProcedure(Long subPolicyId, Long cityId,
    
        Long fetchNum) throws SQLException {
    
      Map<String, Long> map = new HashMap<String, Long>();
    
      map.put("subPolicyId", subPolicyId);
    
      map.put("cityId", cityId);
    
      map.put("fetchNum", fetchNum);
    
      SqlMapSession sqlMapSession = sqlMapClient.openSession();
    
      try {
    
        sqlMapSession.update("PROCEDURE.provCitySplit", map);
    
        Long resultCode = map.get("resultCode");
    
        return resultCode;          
    
      } catch (SQLException e) {
    
        throw new SQLException(e);
    
      } finally {
    
        if (sqlMapSession.getCurrentConnection() != null) {
    
          System.out.println("Get CurrentConnection and close...");
    
          sqlMapSession.getCurrentConnection().close();
    
        }
    
        sqlMapSession.close();
    
      }
    
    }

而mybatis3已经没有procedure这样的标签，只剩下select来操作存储过程，使用方法如下：

<select id="cancelSubPolicy" statementType="CALLABLE">
    
        {call p_cancel_sub_policy(#{subPolicyId, jdbcType=NUMERIC, mode=IN},
    
                                  #{canceledCount, jdbcType=NUMERIC, mode=OUT})}
    
    </select>

java调用方法如下：

public long cancelSubPolicy(Integer coreDBId, Long subPolicyId) throws SQLException {
    
        SqlSession sqlSession = null;
    
        try {
    
            sqlSession = sessionFactory.openSession();
    
            final Map<String, Object> param = new HashMap<String, Object>();
    
            param.put("subPolicyId", subPolicyId);
    
            long time = System.currentTimeMillis();
    
            sqlSession.select("com.ailk.dm.odomain.dms.dao.MultiTaskDao.cancelSubPolicy", param, null);
    
            log.info("cancelSubPolicy elapsed time: " + (System.currentTimeMillis() - time) + "ms");
    
            BigDecimal canceledCount = (BigDecimal) param.get("canceledCount");
    
            // return canceled task count;
    
            return canceledCount.longValue();
    
        } catch (PersistenceException pe) {
    
            log.error("cancelSubPolicy failed: subPolicyId=" + subPolicyId, pe);
    
            throw new SQLException(pe);
    
        } finally {
    
            if (null != sqlSession) sqlSession.close();
    
        }
    
    }