外贸网站知名做外链/广州网站排名优化公司

 

最近，有个开发提了个需求，希望中间件支持调用mysql存储过程时支持多结果集返回，因为某些原因我们使用了不少的存储过程，很多复杂的逻辑目前来看交互非常的多，所以从当前的现状来说，这个需求还是蛮合理的。中午抽空特地搜了下，整合完整示例如下：

1、创建测试存储过程

delimiter $$
CREATE PROCEDURE sp_multi_resultset(IN p_operator_company_no int,IN p_operator_no int,OUT p_error_code varchar(32),OUT p_error_info varchar(255))
BEGINselect 1 as col1,2 as col2;select 11 as col11,22 as col21;
end $$delimiter ;call sp_multi_resultset(1,1,@error_code,@error_info);

2、mapper文件

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="MultiResultset"><resultMap type="test.Test" id="test1">  <result column="col1" property="col1" jdbcType="INTEGER" javaType="Integer" /> <result column="col2" property="col2" jdbcType="INTEGER" javaType="Integer" />   </resultMap>  <resultMap type="test.Test2" id="test2">   <result column="col1" property="col1" jdbcType="INTEGER" javaType="Integer" /> <result column="col3" property="col3" jdbcType="INTEGER" javaType="Integer" />   </resultMap> <select id="getTests" statementType="CALLABLE" parameterType="Map" resultMap="test1,test2" >{call sp_multi_resultset(#{param1,mode=IN,jdbcType=INTEGER},#{param2,mode=IN,jdbcType=INTEGER},#{errorCode,mode=OUT,jdbcType=VARCHAR},#{errorInfo,mode=OUT,jdbcType=VARCHAR})}</select>
</mapper>

 

3、java代码

/*** */
package com.medsoft.top10.dao;import java.util.HashMap;
import java.util.List;
import java.util.Map;import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import com.cyl.kernel.util.JsonUtils;/*** @author zhjh256@163.com* {@link} http://www.cnblogs.com/zhjh256*/
@Service
public class TestMultiResultSet implements InitializingBean {@Autowiredprivate SqlSessionTemplate sqlSession;/* (non-Javadoc)* @see org.springframework.beans.factory.InitializingBean#afterPropertiesSet()*/@Overridepublic void afterPropertiesSet() throws Exception {Map<String,String> map = new HashMap<String,String>();map.put("param1", "1");map.put("param2", "1");map.put("errorCode", "");map.put("errorInfo", "");List<List<?>> multiResult = sqlSession.selectList("MultiResultset.getTests",map);System.out.println(JsonUtils.toJson(multiResult.get(0)));System.out.println(JsonUtils.toJson(multiResult.get(1)));}
}

输出：

[{"col1":1,"col2":2}]
[{"col1":1,"col3":2}]

昨天测试的时候，漏掉了property="colX"导致返回的是[null]，罪过啊。。。。