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) /*排序*/
)
BEGIN
	SET @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
)
BEGIN
	SET 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
)
BEGIN
	SELECT 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中去的。