Mybatis学习总结（七）——调用存储过程

一、返回select结果集

1、创建存储过程

DELIMITER //
DROP PROCEDURE IF EXISTS proc_queryUser;
CREATE PROCEDURE proc_queryUser(
    IN user_name VARCHAR(50) CHARACTER SET utf8
)
BEGIN
    SET @exeSql = CONCAT('SELECT id,username,sex,birthday,address ',
        'from t_user where username like \'',user_name,'%\' order by id');
    -- 打印sql
    -- SELECT @exeSql;
    -- 预定义一个语句，并将它赋给stmtsql
    PREPARE stmtsql FROM @exeSql;
    EXECUTE stmtsql;
    -- 释放一个预定义语句的资源
    DEALLOCATE PREPARE stmtsql;

END //
DELIMITER ;

2、存储过程调用

SET @user_name='小';
CALL proc_queryUser(@user_name);

3、在UserMapper.java中添加接口方法

/**调用存储过程查询用户**/
public List findUserByProc(String user_name);
    
/**调用存储过程查询用户**/
public List findUserByProc1(Map map);

4、在UserMapper.xml中添加如下配置项：

<!-- 调用存储过程 -->
<!-- 第一种方式，参数使用parameterType -->
<select id="findUserByProc" parameterType="java.lang.String" statementType="CALLABLE" 
    resultType="com.mybatis.entity.User">
    {call proc_queryUser(#{user_name,jdbcType=VARCHAR,mode=IN})}
</select>

 <parameterMap type="java.util.Map" id="userMap">
     <parameter property="user_name" mode="IN" jdbcType="VARCHAR"/>
</parameterMap>

<!-- 调用存储过程 -->
<!-- 第二种方式，参数使用parameterMap -->
<select id="findUserByProc1" parameterMap="userMap" statementType="CALLABLE" 
    resultType="com.mybatis.entity.User">
    {call proc_queryUser(?)}
</select>

说明：这里使用两种方式调用存储过程，两种方式的区别主要在于参数的使用方式上，第一种方式使用parameterType，第二种方式使用parameterMap。

5、测试代码：

package com.mybatis.test;

import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import com.mybatis.entity.User;
import com.mybatis.mapper.UserMapper;

public class TestMybatisProceduce {
    
    private SqlSessionFactory sqlSessionFactory;
    
    // 此方法是在执行@Test方法之前执行
    @Before
    public void setUp() throws Exception {
        String resource = "SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        // 创建SqlSessionFcatory
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }
    
    @Test
    public void TestProceduce(){
         SqlSession sqlSession = sqlSessionFactory.openSession();
         // 创建Usermapper对象，mybatis自动生成mapper代理对象
         UserMapper mapper = sqlSession.getMapper(UserMapper.class);
         List userList = mapper.findUserByProc("小");
         System.out.println(userList);
         sqlSession.close();
    }
    
    @Test
    public void TestProceduce1(){
         SqlSession sqlSession = sqlSessionFactory.openSession();
         // 创建Usermapper对象，mybatis自动生成mapper代理对象
         UserMapper mapper = sqlSession.getMapper(UserMapper.class);
         Map map = new HashMap();
         map.put("user_name", "小");
         List userList = mapper.findUserByProc1(map);
         System.out.println(userList);
         sqlSession.close();
    }
}

6、运行结果：

[User [id=8, username=小A, sex=2, address=北京, birthday=Sat Jun 27 00:00:00 CST 2015], 
User [id=9, username=小B, sex=2, address=北京, birthday=Sat Jun 27 00:00:00 CST 2015], 
User [id=10, username=小C, sex=1, address=北京, birthday=Sat Jun 27 00:00:00 CST 2015], 
User [id=11, username=小D, sex=2, address=北京, birthday=Sat Jun 27 00:00:00 CST 2015]]

[User [id=8, username=小A, sex=2, address=北京, birthday=Sat Jun 27 00:00:00 CST 2015], 
User [id=9, username=小B, sex=2, address=北京, birthday=Sat Jun 27 00:00:00 CST 2015], 
User [id=10, username=小C, sex=1, address=北京, birthday=Sat Jun 27 00:00:00 CST 2015], 
User [id=11, username=小D, sex=2, address=北京, birthday=Sat Jun 27 00:00:00 CST 2015]]

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

1、创建存储过程

DELIMITER //
DROP PROCEDURE IF EXISTS proc_queryUserCount;
CREATE PROCEDURE proc_queryUserCount(
    IN user_name VARCHAR(50) CHARACTER SET utf8,
    OUT count INT
)
BEGIN
    SET @exeSql = CONCAT('SELECT count(*) into @rowsCount from t_user where username like \'',user_name,'%\'');
    -- 打印sql
    -- SELECT @exeSql;
    -- 预定义一个语句，并将它赋给stmtsql
    PREPARE stmtsql FROM @exeSql;
    EXECUTE stmtsql;
    -- 释放一个预定义语句的资源
    DEALLOCATE PREPARE stmtsql;
    SET count = @rowsCount;

END //
DELIMITER ;

2、存储过程调用：

SET @user_name='小';
CALL proc_queryUserCount(@user_name,@count);
SELECT @count;

3、在UserMapper.java中添加接口方法

 /**调用存储过程(带输入输出参数的存储过程)**/
public void findUserCountByProc(Map map);

4、在UserMapper.xml中添加如下配置项：

<parameterMap type="java.util.HashMap" id="userMap1">
    <parameter property="user_name" jdbcType="VARCHAR" mode="IN"/>
    <parameter property="count" jdbcType="INTEGER" mode="OUT"/>
</parameterMap>

<!-- 调用存储过程 (带输入输出参数的存储过程)-->
<select id="findUserCountByProc" parameterMap="userMap1" statementType="CALLABLE">
    {call proc_queryUserCount(?,?)}
</select>

5、测试代码：

@Test
public void TestProceduce2(){
     SqlSession sqlSession = sqlSessionFactory.openSession();
     // 创建Usermapper对象，mybatis自动生成mapper代理对象
     UserMapper mapper = sqlSession.getMapper(UserMapper.class);
     Map map = new HashMap();
     map.put("user_name", "小");
     mapper.findUserCountByProc(map);
     System.out.println("userCount="+map.get("count"));
     sqlSession.close();
}

6、运行结果：

userCount=4

三、返回多个结果集

1、创建存储过程

DELIMITER //
DROP PROCEDURE IF EXISTS proc_query;
CREATE PROCEDURE proc_query(
    IN user_id INT,
    OUT order_count INT
)
BEGIN
    SET @exeSql = CONCAT('SELECT id,username,sex,date_format(birthday,\'%Y-%m-%d\')birthday,address ',
            'from t_user where id=\'',user_id,'\'');

    PREPARE stmtsql FROM @exeSql;
    EXECUTE stmtsql;
    DEALLOCATE PREPARE stmtsql;
    
    SET @exeSql1 = CONCAT('SELECT id,user_id,number,date_format(createtime,\'%Y-%m-%d %H:%i:%s\')createtime ',
            'from orders where user_id=\'',user_id,'\'');

    PREPARE stmtsql1 FROM @exeSql1;
    EXECUTE stmtsql1;
    DEALLOCATE PREPARE stmtsql1;

    SET @exeSql2 = CONCAT('SELECT count(*) into @rowsCount from orders ',
            'where user_id=\'',user_id,'\'');

    PREPARE stmtsql2 FROM @exeSql2;
    EXECUTE stmtsql2;
    DEALLOCATE PREPARE stmtsql2;
    SET order_count = @rowsCount;

END //
DELIMITER ;

2、存储过程调用：

SET @user_id=2;
CALL proc_query(@user_id,@order_count);
SELECT @order_count;

3、在UserMapper.java中添加接口方法

/**调用存储过程(返回多个结果集)**/
public List<List<?>> findUserOrdersByProc(Map map);

4、在UserMapper.xml中添加如下配置项：

<resultMap type="java.util.HashMap" id="userInfoMap">
    <result column="id" property="id" javaType="java.lang.Integer" jdbcType="INTEGER"/>
      <result column="username" property="username" javaType="java.lang.String" jdbcType="VARCHAR"/>
      <result column="birthday" property="birthday" javaType="java.lang.String" jdbcType="DATE"/>
      <result column="sex" property="sex" javaType="java.lang.String" jdbcType="CHAR"/>
      <result column="address" property="address" javaType="java.lang.String" jdbcType="VARCHAR"/>
</resultMap>

<resultMap type="java.util.HashMap" id="ordersMap">
    <result column="id" property="id" javaType="java.lang.Integer" jdbcType="INTEGER"/>
      <result column="user_id" property="user_id" javaType="java.lang.Integer" jdbcType="INTEGER"/>
      <result column="number" property="number" javaType="java.lang.String" jdbcType="VARCHAR"/>
      <result column="createtime" property="createtime" javaType="java.lang.String" jdbcType="TIMESTAMP"/>
</resultMap>

<!-- 调用存储过程 (返回多个结果集)-->
<select id="findUserOrdersByProc" parameterType="java.util.Map" resultMap="userInfoMap,ordersMap" 
    statementType="CALLABLE">
    {call proc_query(#{user_id,jdbcType=INTEGER,mode=IN},
                     #{order_count,jdbcType=INTEGER,mode=OUT})}
</select>

5、测试代码：

@Test
public void TestProceduce3(){
     SqlSession sqlSession = sqlSessionFactory.openSession();
     // 创建Usermapper对象，mybatis自动生成mapper代理对象
     UserMapper mapper = sqlSession.getMapper(UserMapper.class);
     Map map = new HashMap();
     map.put("user_id", "2");
     List<List<?>> resultList = mapper.findUserOrdersByProc(map);
     List<Map> list1 = (List<Map>)resultList.get(0);
     List<Map> list2 = (List<Map>)resultList.get(1);
     System.out.println(list1);
     System.out.println(list2);
     System.out.println("orderCount="+map.get("order_count"));
     sqlSession.close();
}

6、运行结果：

[{id=2, birthday=2014-07-10, sex=1, username=张三, address=北京市}]
[{createtime=2015-07-17 14:13:23, id=3, number=1000012, user_id=2}]
orderCount=1

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