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; 
     
    // [email protected] 
    @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中去的。

原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/17276.html

Mybatis学习总结（七）:调用存储过程详解编程语言

相关推荐

发表回复