mybatis 调用存储过程

参数形式： 

create procedure sptest.adder(in addend1 integer, in addend2 integer, out theSum integer)
begin atomic
  set theSum = addend1 + addend2;
end
go

<parameterMap type="map" id="testParameterMap">
   <parameter property="addend1" jdbcType="INTEGER" mode="IN"/>
   <parameter property="addend2" jdbcType="INTEGER" mode="IN"/>
   <parameter property="sum" jdbcType="INTEGER" mode="OUT"/>
 </parameterMap>
lt;update id="adderWithParameterMap" parameterMap="testParameterMap" statementType="CALLABLE">
   {call sptest.adder(?, ?, ?)}
 </update>

 

public void testAdderAsUpdateWithParameterMap() {
       SqlSession sqlSession = sqlSessionFactory.openSession();
       try {
           Map<String, Object> parms = new HashMap<String, Object>();
           parms.put("addend1", 3);
           parms.put("addend2", 4);

           SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

           spMapper.adderWithParameterMap(parms);
           assertEquals(7, parms.get("sum"));

           parms = new HashMap<String, Object>();
           parms.put("addend1", 2);
           parms.put("addend2", 3);
           spMapper.adderWithParameterMap(parms);
           assertEquals(5, parms.get("sum"));

       } finally {
           sqlSession.close();
       }

带输入输出参数的存储过程： 
sql代码：

create procedure sptest.getnames(in lowestId int, out totalrows integer)
reads sql data
dynamic result sets 1
BEGIN ATOMIC
  declare cur cursor for select * from sptest.names where id >= lowestId;
  select count(*) into totalrows from sptest.names where id >= lowestId;
  open cur;
END
go

<select id="getNamesAndItems" statementType="CALLABLE"
    <select id="getNames" parameterType="java.util.Map" statementType="CALLABLE"
  resultMap="nameResult">
  {call sptest.getnames(
    #{lowestId,jdbcType=INTEGER,mode=IN},
    #{totalRows,jdbcType=INTEGER,mode=OUT})}
</select>
</select>

public void testCallWithResultSet2_a1() {
       SqlSession sqlSession = sqlSessionFactory.openSession();
       try {
           SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

           Map<String, Object> parms = new HashMap<String, Object>();
           parms.put("lowestId", 1);
           List<Name> names = spMapper.getNamesAnnotated(parms);
           assertEquals(3, names.size());
           assertEquals(3, parms.get("totalRows"));
       } finally {
           sqlSession.close();
       }
   }

返回多个结果集 
sql代码：

create procedure sptest.getnamesanditems()
reads sql data
dynamic result sets 2
BEGIN ATOMIC
  declare cur1 cursor for select * from sptest.names;
  declare cur2 cursor for select * from sptest.items;
  open cur1;
  open cur2;
END
go

<resultMap type="org.apache.ibatis.submitted.sptests.Name" id="nameResult">
    <result column="ID" property="id"/>
    <result column="FIRST_NAME" property="firstName"/>
    <result column="LAST_NAME" property="lastName"/>
  </resultMap>

  <resultMap type="org.apache.ibatis.submitted.sptests.Item" id="itemResult">
    <result column="ID" property="id"/>
    <result column="ITEM" property="item"/>
  </resultMap>

  <select id="getNamesAndItems" statementType="CALLABLE"
    resultMap="nameResult,itemResult">
    {call sptest.getnamesanditems()}
  </select>

@Test
    public void testGetNamesAndItems() throws SQLException {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

            List<List<?>> results = spMapper.getNamesAndItems();
            assertEquals(2, results.size());
            assertEquals(4, results.get(0).size());
            assertEquals(3, results.get(1).size());
        } finally {
            sqlSession.close();
        }
    }

注意： 

上面就是几种常用的了。 
1 sqlserver oracle sqlserver返回结果集是可以不要out参数的。如果sql中用的是select出结果，不需要配置out参数。多个结果集/结果集可以配置resultMap 来返回LIST，主要是调用selectList方法会自动把结果集加入到list中去的。 
2 sql有返回值 用select标签 
3 注意sql参数和mybatis参数的对应关系，这个这里就不讲了。 
4 注意参数个数 

我遇到的异常： 

参数不匹配的原因，因为sqlserver 中我是直接返回select临时表结果，不需要配置存储过程输出参数。 
list中的内容形式：