AI专业者开发社区 1 号成员
  •  0 回帖  •  2 浏览  •  4 个月前
 

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 {


</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> SqlSessionFactory sqlSessionFactory;

</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 此方法是在执行@Test方法之前执行</span>



    @Before
public void setUp() throws Exception {
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
// 创建 SqlSessionFcatory
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}


@Test
</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> TestProceduce(){
     SqlSession sqlSession </span>=<span style="color: rgba(0, 0, 0, 1)"> sqlSessionFactory.openSession();
     </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 创建Usermapper对象，mybatis自动生成mapper代理对象</span>
     UserMapper mapper = sqlSession.getMapper(UserMapper.<span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)">);
     List userList </span>= mapper.findUserByProc("小"<span style="color: rgba(0, 0, 0, 1)">);
     System.out.println(userList);
     sqlSession.close();
}

@Test
</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> TestProceduce1(){
     SqlSession sqlSession </span>=<span style="color: rgba(0, 0, 0, 1)"> sqlSessionFactory.openSession();
     </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 创建Usermapper对象，mybatis自动生成mapper代理对象</span>
     UserMapper mapper = sqlSession.getMapper(UserMapper.<span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)">);
     Map map </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> HashMap();
     map.put(</span>"user_name", "小"<span style="color: rgba(0, 0, 0, 1)">);
     List userList </span>=<span style="color: rgba(0, 0, 0, 1)"> 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,'\'');

</span><span style="color: rgba(0, 0, 255, 1)">PREPARE</span> stmtsql <span style="color: rgba(0, 0, 255, 1)">FROM</span> <span style="color: rgba(0, 128, 0, 1)">@exeSql</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 0, 255, 1)">EXECUTE</span><span style="color: rgba(0, 0, 0, 1)"> stmtsql;
</span><span style="color: rgba(0, 0, 255, 1)">DEALLOCATE</span> <span style="color: rgba(0, 0, 255, 1)">PREPARE</span><span style="color: rgba(0, 0, 0, 1)"> stmtsql;

</span><span style="color: rgba(0, 0, 255, 1)">SET</span> <span style="color: rgba(0, 128, 0, 1)">@exeSql1</span> <span style="color: rgba(128, 128, 128, 1)">=</span> CONCAT(<span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(255, 0, 0, 1)">SELECT id,user_id,number,date_format(createtime,\</span><span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(128, 128, 128, 1)">%</span>Y<span style="color: rgba(128, 128, 128, 1)">-%</span>m<span style="color: rgba(128, 128, 128, 1)">-%</span>d <span style="color: rgba(128, 128, 128, 1)">%</span>H:<span style="color: rgba(128, 128, 128, 1)">%</span>i:<span style="color: rgba(128, 128, 128, 1)">%</span>s\<span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(255, 0, 0, 1)">)createtime </span><span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">,
        </span><span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(255, 0, 0, 1)">from orders where user_id=\</span><span style="color: rgba(255, 0, 0, 1)">''</span><span style="color: rgba(255, 0, 0, 1)">,user_id,</span><span style="color: rgba(255, 0, 0, 1)">'</span>\<span style="color: rgba(255, 0, 0, 1)">''</span><span style="color: rgba(0, 0, 0, 1)">);

</span><span style="color: rgba(0, 0, 255, 1)">PREPARE</span> stmtsql1 <span style="color: rgba(0, 0, 255, 1)">FROM</span> <span style="color: rgba(0, 128, 0, 1)">@exeSql1</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 0, 255, 1)">EXECUTE</span><span style="color: rgba(0, 0, 0, 1)"> stmtsql1;
</span><span style="color: rgba(0, 0, 255, 1)">DEALLOCATE</span> <span style="color: rgba(0, 0, 255, 1)">PREPARE</span><span style="color: rgba(0, 0, 0, 1)"> stmtsql1;

</span><span style="color: rgba(0, 0, 255, 1)">SET</span> <span style="color: rgba(0, 128, 0, 1)">@exeSql2</span> <span style="color: rgba(128, 128, 128, 1)">=</span> CONCAT(<span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(255, 0, 0, 1)">SELECT count(*) into @rowsCount from orders </span><span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">,
        </span><span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(255, 0, 0, 1)">where user_id=\</span><span style="color: rgba(255, 0, 0, 1)">''</span><span style="color: rgba(255, 0, 0, 1)">,user_id,</span><span style="color: rgba(255, 0, 0, 1)">'</span>\<span style="color: rgba(255, 0, 0, 1)">''</span><span style="color: rgba(0, 0, 0, 1)">);

</span><span style="color: rgba(0, 0, 255, 1)">PREPARE</span> stmtsql2 <span style="color: rgba(0, 0, 255, 1)">FROM</span> <span style="color: rgba(0, 128, 0, 1)">@exeSql2</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 0, 255, 1)">EXECUTE</span><span style="color: rgba(0, 0, 0, 1)"> stmtsql2;
</span><span style="color: rgba(0, 0, 255, 1)">DEALLOCATE</span> <span style="color: rgba(0, 0, 255, 1)">PREPARE</span><span style="color: rgba(0, 0, 0, 1)"> stmtsql2;
</span><span style="color: rgba(0, 0, 255, 1)">SET</span> order_count <span style="color: rgba(128, 128, 128, 1)">=</span> <span style="color: rgba(0, 128, 0, 1)">@rowsCount</span><span style="color: rgba(0, 0, 0, 1)">;



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 中去的。