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

2023-08-08 20:57:10

一、返回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);

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

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;

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

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;

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

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

上一篇：ios 修改程序显示名称


下一篇：three.js 微信小游戏