MyBatis调用存储过程与存储函数

电玩女神 2021-07-25 01:55 653阅读 0赞

# 1、MyBatis调用存储过程 #

MyBatis支持使用存储过程的配置。当使用存储过程时，需要设置一个参数“mode”，其值有IN（输入参数）、OUT（输出参数）和INOUT（输入/输出参数）。MyBatis定义存储过程如下：

<select id="selectSomeThing" statementType="CALLABLE" parameterType="hashmap" resultType="com.pjb.mybatis.po.User">
        {CALL PROC_FOR_INPUT(#{information,mode=IN,jdbcType=VARCHAR})}
    </select>

**【示例】**创建存储过程，实现分页查询用户列表，并返回数据总数和总页数，通过MyBatis调用该存储过程。

（1）在MySQL数据库中创建用户信息表（tb\_user）。

-- 创建“用户信息”数据表
    CREATE TABLE IF NOT EXISTS tb_user
    ( 
    	id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
    	user_name VARCHAR(50) NOT NULL COMMENT '用户姓名',
    	sex CHAR(2) DEFAULT '男' COMMENT '性别'
    ) COMMENT = '用户信息表';

（2）创建存储过程，实现分页查询用户列表，并返回数据总数和总页数。

-- 将结束标志符更改为$$
    DELIMITER $$
    
    /*
      -- 存储过程：分页查询用户列表，并返回数据总数和总页数
      -- 输入参数：page_index：当前页码
      -- 输入参数：page_size：分页大小
      -- 输出参数：total_count：数据总数
      -- 输出参数：total_page：总页数
    */
    CREATE PROCEDURE proc_search_user(IN page_index INT,IN page_size INT, OUT total_count INT, OUT total_page INT)
    BEGIN
    	DECLARE begin_no INT;
    	SET begin_no = (page_index-1)*page_size;
    
    	-- 分页查询列表
    	SELECT * FROM tb_user
    	WHERE id >= (
    		SELECT id FROM tb_user
    		ORDER BY id ASC
    		LIMIT begin_no,1
    	)
    	ORDER BY id ASC
    	LIMIT page_size;
    
    	-- 计算数据总数
    	SELECT COUNT(1) INTO total_count FROM tb_user;
    
    	-- 计算总页数
    	SET total_page = FLOOR((total_count + page_size - 1) / page_size);
    END$$
    
    -- 将结束标志符更改回分号
    DELIMITER ;

（3）创建用户信息持久化类（User.java）。

package com.pjb.mybatis.po;
    
    /**
     * 用户信息的持久化类
     * @author pan_junbiao
     **/
    public class User
    {
        private int id; //用户编号
        private String userName; //用户姓名
        private String sex; //性别
    
        //省略getter与setter方法...
    }

（4）编写SQL映射配置。

<select id="proc_search_user" statementType="CALLABLE" parameterType="hashmap" resultType="com.pjb.mybatis.po.User">
        {CALL proc_search_user(#{page_index,mode=IN,jdbcType=INTEGER},
          #{page_size,mode=IN,jdbcType=INTEGER},
          #{total_count,mode=OUT,jdbcType=INTEGER},
          #{total_page,mode=OUT,jdbcType=INTEGER})}
    </select>

（5）编写执行方法。

/**
     * 使用MyBatis调用存储过程：分页查询用户列表，并返回数据总数和总页数
     * @author pan_junbiao
     */
    @Test
    public void procSearchUser()
    {
        DataConnection dataConnection = new DataConnection();
        SqlSession sqlSession = dataConnection.getSqlSession();
        //封装查询参数
        Map params = new HashMap();
        params.put("page_index",2);  //输入参数：当前页码
        params.put("page_size",10);  //输入参数：分页大小
        params.put("total_count",0); //输出参数：数据总数
        params.put("total_page",0);  //输出参数：总页数
        //调用存储过程
        List<User> userList = sqlSession.selectList("test.proc_search_user",params);
        System.out.println("查询第"+ params.get("page_index") +"页的数据，每页共"+params.get("page_size")+"条数据");
        //遍历用户列表
        for (User user : userList)
        {
            System.out.println("编号：" + user.getId() +" 姓名：" + user.getUserName() + " 性别：" + user.getSex());
        }
        //获取输出参数
        System.out.println("数据总数：" + params.get("total_count"));
        System.out.println("总页数：" + params.get("total_page"));
        sqlSession.close();
    }

**执行结果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70][]

**【示例】**创建存储过程，实现新增用户信息，并返回自增主键，通过MyBatis调用该存储过程。

（1）创建存储过程。

-- 将结束标志符更改为$$
    DELIMITER $$
    
    /*
      -- 存储过程：新增用户信息，返回自增主键
      -- 输入参数：user_name：用户姓名
      -- 输入参数：sex：性别
      -- 输出参数：user_id：自增主键
    */
    CREATE PROCEDURE proc_add_user(IN user_name VARCHAR(50),IN sex CHAR(2), OUT user_id INT)
    BEGIN
    	-- 新增用户
    	INSERT INTO tb_user(user_name,sex) VALUE (user_name,sex);
    	
    	-- 获取自增主键
    	SELECT LAST_INSERT_ID() INTO user_id;
    END$$
    
    -- 将结束标志符更改回分号
    DELIMITER ;

（2）编写SQL映射配置。

<insert id="proc_add_user" statementType="CALLABLE" parameterType="com.pjb.mybatis.po.User">
      {CALL proc_add_user(#{userName,mode=IN,jdbcType=VARCHAR},
        #{sex,mode=IN,jdbcType=CHAR},
        #{id,mode=OUT,jdbcType=INTEGER})}
    </insert>

（3）编写执行方法。

/**
     * 使用MyBatis调用存储过程：新增用户信息，返回自增主键
     * @author pan_junbiao
     */
    @Test
    public void procAddUser()
    {
        DataConnection dataConnection = new DataConnection();
        SqlSession sqlSession = dataConnection.getSqlSession();
        //新增的用户对象
        User user = new User();
        user.setUserName("pan_junbiao的博客");
        user.setSex("男");
        //调用存储过程执行新增
        int reuslt = sqlSession.insert("test.proc_add_user",user);
        sqlSession.commit();
        //打印结果
        System.out.println("执行结果："+reuslt);
        System.out.println("自增主键："+user.getId());
        sqlSession.close();
    }

**执行结果：**

![20191217151033152.jpg][]

其实，新增数据后，获取自增主键是可以使用MyBatis提供的<selectKey>标签，SQL映射配置如下：

<insert id="proc_add_user" statementType="CALLABLE" parameterType="com.pjb.mybatis.po.User">
      <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
          SELECT LAST_INSERT_ID()
      </selectKey>
      {CALL proc_add_user(#{userName,mode=IN,jdbcType=VARCHAR},
        #{sex,mode=IN,jdbcType=CHAR})}
    </insert>

但上述示例是为了能让该存储过程拥有一个返回的参数。

# 2、MyBatis调用存储函数 #

**【示例】**创建存储函数，根据用户编号，获取用户名称，通过MyBatis调用该存储函数。

（1）创建存储函数，根据用户编号，获取用户名称。

-- 将结束标志符更改为$$
    DELIMITER $$
    
    /*
      -- 存储函数：根据用户编号，获取用户名称
      -- 输入参数：in_id：用户编号
      -- 返回结果：用户名称
    */
    CREATE FUNCTION func_get_user_name(in_id INT)
    RETURNS VARCHAR(50)
    BEGIN
    	-- 定义返回变量
    	DECLARE out_name VARCHAR(50);
    
    	-- 查询用户信息，获取用户名称
    	SELECT user_name INTO out_name FROM tb_user WHERE id = in_id;
    
    	-- 返回结果
    	RETURN out_name;
    END$$
    
    -- 将结束标志符更改回分号
    DELIMITER ;

（2）编写SQL映射配置。

<select id="func_get_user_name" statementType="CALLABLE" parameterType="hashMap" >
        {#{userName,mode=OUT,jdbcType=VARCHAR} = CALL func_get_user_name(#{userId,mode=IN,jdbcType=INTEGER})}
    </select>

（3）编写执行方法。

/**
     * 使用MyBatis调用存储函数：根据用户编号，获取用户名称
     * @author pan_junbiao
     */
    @Test
    public void funcGetUserName()
    {
        DataConnection dataConnection = new DataConnection();
        SqlSession sqlSession = dataConnection.getSqlSession();
        //封装参数
        Map userMap = new HashMap();
        userMap.put("userName","");
        userMap.put("userId",8);
        sqlSession.selectOne("test.func_get_user_name",userMap);
        System.out.println("用户名称：" + userMap.get("userName"));
        sqlSession.close();
    }

**执行结果：**

![20191217165718530.jpg][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70]: /images/20210724/5876c5b393ba4d11ab2eb77c05772cae.png
[20191217151033152.jpg]: /images/20210724/69209e4e433642dba17fdafd29bfc39d.png
[20191217165718530.jpg]: /images/20210724/a945c3472920443698d397d888be6f3e.png