Mybatis调用存储过程

一、返回结果集

以MySql分页为例

1.存储过程

DROP PROCEDURE IF EXISTS sp_pager;CREATE PROCEDURE sp_pager(IN currentPage int ,/*当前页*/IN pageSize int,/*每页的记录数*/OUT totalCount int /*总记录数*/-- IN tableName varchar(100), /*表名*/-- IN p_field varchar(300), /*查询的字段，逗号分隔*/-- IN p_where varchar(500),/*查询条件*/-- IN orderby varchar(300) /*排序*/)BEGINSET @startIndex=(currentPage-1)*pageSize;SET @endIndex=pageSize;SET @strSql=CONCAT('select id,user_name,user_age,user_sex',' from ','tb_user',-- CASE IFNULL(p_where,'') WHEN '' THEN '' ELSE CONCAT(' where ',p_where) END,-- CASE IFNULL(orderby,'') WHEN '' THEN '' ELSE CONCAT(' order by ',orderby) END,' limit ',@startIndex,',',@endIndex);/*预定义一个语句，并将它赋给stmtsql*/PREPARE stmtsql FROM @strSql;EXECUTE stmtsql;/*释放一个预定义语句的资源*/DEALLOCATE PREPARE stmtsql;SET @strCount=CONCAT('select count(*) into @Rows_Total from ','tb_user');PREPARE stmtcount FROM @strCount;EXECUTE stmtcount;DEALLOCATE PREPARE stmtcount;SET totalCount=@Rows_Total;/*计算总数也可以是下面这种方法*/-- SELECT COUNT(*) INTO totalCount FROM tb_user;END

测试存储过程：

CALL sp_pager(1,20,@totalCount);SELECT @totalCount;

 

2.Mybatis配置文件

 <resultMap type="java.util.HashMap" id="resultMap">    <result column="id" property="id" javaType="java.lang.Integer" jdbcType="INTEGER"/>      <result column="user_name" property="user_name" javaType="java.lang.String" jdbcType="VARCHAR"/>      <result column="user_sex" property="user_sex" javaType="java.lang.String" jdbcType="VARCHAR"/>      <result column="user_age" property="user_age" javaType="java.lang.Integer" jdbcType="INTEGER"/>    </resultMap>    <select id="getUserList" parameterType="java.util.Map" resultMap="resultMap" statementType="CALLABLE">           {call sp_pager(#{currentPage,jdbcType=INTEGER,mode=IN},              #{pageSize,jdbcType=INTEGER,mode=IN},              #{totalCount,jdbcType=INTEGER,mode=OUT})}    </select>  

3.Action调用

@RequestMapping(value="/getUserList")public ModelAndView getUserList(){Map map=new HashMap();map.put("currentPage", "1");map.put("pageSize", "20");List userList=userService.getUserList(map);System.out.println(map.get("totalCount"));ModelAndView mv=new ModelAndView();//添加模型数据可以是任意的POJO对象mv.addObject("userList",userList);mv.setViewName("user/helloWorld");return mv;}

totalCount 从map中取出即可。

二、带输入输出参数的存储过程

DROP PROCEDURE IF EXISTS sp_sum;CREATE PROCEDURE sp_sum(IN num1 int,IN num2 int,OUT sum int)BEGINSET sum=num1+num2;END

配置文件：

<parameterMap type="java.util.HashMap" id="testParameterMap">    <parameter property="num1" jdbcType="INTEGER" mode="IN"/>    <parameter property="num2" jdbcType="INTEGER" mode="IN"/>    <parameter property="sum" jdbcType="INTEGER" mode="OUT"/>  </parameterMap>  <update id="Sum" parameterMap="testParameterMap" statementType="CALLABLE">    {call sp_sum(?,?,?)}  </update>

Action：

@RequestMapping(value="/Sum")public ModelAndView Sum(){Map map=new HashMap();map.put("num1", "1");map.put("num2", "2");userService.Sum(map);System.out.println(map.get("sum"));ModelAndView mv=new ModelAndView();mv.setViewName("user/helloWorld");return mv;}

三、返回多个结果集

DROP PROCEDURE IF EXISTS sp_query;CREATE PROCEDURE sp_query(IN num1 int,IN num2 int,OUT sum int)BEGINSELECT id,user_name,user_sex,user_age from tb_user;SELECT id,dept_name from tb_dept;SET sum=num1+num2;END

配置文件：

 <resultMap type="java.util.HashMap" id="resultMap">    <result column="id" property="id" javaType="java.lang.Integer" jdbcType="INTEGER"/>      <result column="user_name" property="user_name" javaType="java.lang.String" jdbcType="VARCHAR"/>      <result column="user_sex" property="user_sex" javaType="java.lang.String" jdbcType="VARCHAR"/>      <result column="user_age" property="user_age" javaType="java.lang.Integer" jdbcType="INTEGER"/>    </resultMap>         <resultMap type="java.util.HashMap" id="deptMap">    <result column="id" property="id" javaType="java.lang.Integer" jdbcType="INTEGER"/>      <result column="dept_name" property="dept_name" javaType="java.lang.String" jdbcType="VARCHAR"/>    </resultMap>        <select id="getMutiList" parameterType="java.util.Map" resultMap="resultMap,deptMap" statementType="CALLABLE">           {call sp_query(#{num1,jdbcType=INTEGER,mode=IN},         #{num2,jdbcType=INTEGER,mode=IN},         #{sum,jdbcType=INTEGER,mode=OUT})}    </select>  

Action：

@RequestMapping(value="/QueryList")public ModelAndView QueryList(){Map map=new HashMap();map.put("num1", "1");map.put("num2", "2");List<List<Map>> dataList=userService.getMutiList(map);if(dataList!=null && dataList.size()>0){//总数System.out.println("dataList.size="+dataList.size());//遍历for(int i=0;i<dataList.size();i++){List<Map> list=dataList.get(i);for(int j=0;j<list.size();j++){Map m=(Map)list.get(j);Iterator it=m.keySet().iterator();while(it.hasNext()){String key=it.next().toString();System.out.println(key+"--------"+m.get(key));}System.out.println("");}}}System.out.println("sum="+map.get("sum"));ModelAndView mv=new ModelAndView();mv.setViewName("user/helloWorld");return mv;}

运行结果：

总结：

如果sql中用的是select出结果，不需要配置out参数。多个结果集/结果集可以配置resultMap 来返回LIST，主要是调用selectList方法会自动把结果集加入到list中去的。