一、存储过程1

CREATE PROCEDURE `select_user_by_id`(
	IN userId BIGINT,
	OUT userName VARCHAR(50),
	OUT userPassword VARCHAR(50),
	OUT userEmail VARCHAR(50),
	OUT userInfo TEXT,
	OUT headImg BLOB,
	OUT createTime DATETIME)
BEGIN
  -- 根据用户id查询数据
	SELECT user_name,user_password,user_email,user_info,head_img,create_time
	INTO userName,userPassword,userEmail,userInfo,headImg,createTime
	FROM sys_user
	WHERE id = userId;
END

在UserMapper.xml 映射文件中添加如下方法：

	<select id="selectUserById" statementType="CALLABLE" useCache="false">
		{call select_user_by_id(
				#{id, mode=IN},
				#{userName, mode=OUT, jdbcType=VARCHAR},
				#{userPassword, mode=OUT, jdbcType=VARCHAR},
				#{userEmail, mode=OUT, jdbcType=VARCHAR},
				#{userInfo, mode=OUT, jdbcType=VARCHAR},
				#{headImg, mode=OUT, jdbcType=BLOB, javaType=_byte[]},
				#{createTime, mode=OUT, jdbcType=TIMESTAMP}
			)}
	</select>

        在调用存储过程的方法中， 需要把statementType 设置为CALLABLE，在使用select标签调用存储过程时，由于存储过程方式不支持MyBatis 的二级缓存，因此为了避免缓存配置出错，直接将select 标签的useCache 属性设置为false。

        在存储过程中使用参数时， 除了写上必要的属性名，还必须指定参数的mode （模式），可选值为IN、 OUT、INOUT 三种。入参使用IN，出参使用OUT， 输入输出参数使用INOUT， OUT 模式的参数必须指定jdbcType。 这是因为在IN 模式下， MyBatis 提供了默认的jdbcType，在OUT 模式下没有提供。

在UserMapper.xml映射文件对应的接口文件中添加方法：

    /**
     * 使用存储过程查询用户信息
     * @param user
     */
    void selectUserById (SysUser user);

        因为这个存储过程没有返回值（不要和出参混淆），所以返回值类型使用void。 把返回值设置为SysUser 或List 也不会报错，但是任何时候返回值都是null。我们使用出参的方式得到用户的信息。

测试代码：

    @Test
    public void SelectUserByid(){
        //获取SqlSession
        SqlSession sqlSession = getSqlSession();
        try{
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            SysUser user = new SysUser();
            user.setId(1L);
            userMapper.selectUserById(user);
            System.out.println("用户名："+user.getUserName());
        }finally {
            sqlSession.close();
        }
    }

        使用出参方式时，通常情况下会使用对象中的属性接收出参的值，或者使用Map 类型接收返回值。这两种情况有很大的区别。当使用 JavaBean 对象接收出参时，必须保证所有出参在JavaBean 中都有对应的属性存在，否则就会抛出类似“Could not set property ’ xxx ’ 这样的错误。这是由于JavaBean 对象中不存在出参对应的setter 方法，使用Map 类型时就不需要保证所有出参都有对应的属性，当Map 接收了存储过程的出参时，可以通过Map 对象的get （ ”属性名”） 方法获取出参的值。 

 二、存储过程2

CREATE PROCEDURE `select_user_page`(
	IN userName VARCHAR(50),
	IN _offset BIGINT,
	IN _limit BIGINT,
	OUT total BIGINT)
BEGIN
	-- 查询数据总数
	SELECT Count(*) INTO total 
	FROM sys_user
	WHERE user_name LIKE CONCAT('%',userName,'%');

	# 分页查询数据
	SELECT * 
	FROM sys_user 
	where user_name like CONCAT('%',userName,'%') 
	LIMIT _offset, _limit;
END

 在UserMapper.xml映射文件中添加如下代码：

	<select id="selectUserPage" statementType="CALLABLE" useCache="false" resultMap="userMap">
	    {call select_user_page(
	    	#{userName, mode=IN},
	    	#{offset, mode=IN},
	    	#{limit, mode=IN},
	    	#{total, mode=OUT, jdbcType=BIGINT}
	    )}
	</select>

         该方法和上例方法的区别在于， select 标签设置了resultMap，因为该方法通过total 出参得到了查询的总数，通过存储过程返回了最后的结果集，所以需要设置返回值信息。

在对应接口文件添加方法：

    /**
     * 使用存储过程分页查询
     * @param params
     * @return
     */
    List<SysUser> selectUserPage(Map<String,Object> params) ;

        由于需要多个入参和一个出参，而入参中除了userName 属性在SysUser 中，其他3 个参数都和SysUser 无关，除了为了使用SysUser 而增加3 个属性之外，这里我们为了实现方法多样化，也为了印证上一个方法中使用 Map 接收返回值的用法是正确的， 因此使用 Map类型作为参数。

 测试代码：

    @Test
    public void selectUserPage(){
        //获取SqlSession
        SqlSession sqlSession = getSqlSession();
        try{
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            Map<String,Object> params = new HashMap<>();
            params.put("userName","ad");
            params.put("offset",0);
            params.put("limit",10);
            List<SysUser> userList = userMapper.selectUserPage(params);
            Long totle = (Long)params.get("total");
            System.out.println("总数："+totle);
        }finally {
            sqlSession.close();
        }
    }

        测试代码的 Map 参数中不存在 total，执行了存储过程方法后，通过 get 方法得到了total 的值。

三、存储过程3，4

-- 保存用户信息和角色关联信息
CREATE PROCEDURE `insert_user_and_roles`(
	OUT userId BIGINT,
	IN userName VARCHAR(50),
	IN userPassword VARCHAR(50),
	IN userEmail VARCHAR(50),
	IN userInfo TEXT,
	IN headImg BLOB,
	OUT createTime DATETIME,
	IN roleIds VARCHAR(200)
)
BEGIN
  #设置当前时间
	SET createTime = NOW();
	#插入数据
	INSERT INTO sys_user(user_name, user_password, user_email, user_info, head_img, create_time)
	VALUES (userName, userPassword, userEmail, userInfo, headImg, createTime);

	# 获取自增主键
	SELECT LAST_INSERT_ID() INTO userId;
	
	# 保存用户和角色关系数据
	SET roleIds = CONCAT(',',roleIds,',');
	INSERT INTO sys_user_role(user_id, role_id)
	select userId, id from sys_role
	where INSTR(roleIds, CONCAT(',',id,',')) > 0;

END
CREATE PROCEDURE `delete_user_by_id`(IN userId BIGINT)
BEGIN
	DELETE FROM sys_user_role where user_id = userId; 
	DELETE FROM sys_user where id = userId; 
END

在UserMapper.xml文件中添加如下代码：

	<insert id="insertUserAndRoles" statementType="CALLABLE">
		{call insert_user_and_roles(
				#{user.id, mode=OUT, jdbcType=BIGINT},
				#{user.userName, mode=IN},
				#{user.userPassword, mode=IN},
				#{user.userEmail, mode=IN},
				#{user.userInfo, mode=IN},
				#{user.headImg, mode=IN, jdbcType=BLOB},
				#{user.createTime, mode=OUT, jdbcType=TIMESTAMP},
				#{roleIds, mode=IN}
			)}
	</insert>

	<delete id="deleteUserById" statementType="CALLABLE">
		{call delete_user_by_id(#{id, mode=IN})}
	</delete>

在对应接口文件添加如下方法：

	/**
	 * 保存用户信息和角色关联信息
	 * 
	 * @param user
	 * @param roleIds
	 * @return
	 */
	int insertUserAndRoles(@Param("user")SysUser user, @Param("roleIds")String roleIds);
	
	/**
	 * 根据用户 id 删除用户和用户的角色信息
	 * 
	 * @param id
	 * @return
	 */
	int deleteUserById(Long id);

测试代码：

    @Test
    public void testInsertAndDelete(){
        //获取 sqlSession
        SqlSession sqlSession = getSqlSession();
        try {
            //获取 UserMapper 接口
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

            SysUser user = new SysUser();
            user.setUserName("test1");
            user.setUserPassword("123456");
            user.setUserEmail("test@mybatis.tk");
            user.setUserInfo("test info");
            //正常情况下应该读入一张图片存到 byte 数组中
            user.setHeadImg(new byte[]{1,2,3});
            //插入数据
            userMapper.insertUserAndRoles(user, "1,2");
            Assert.assertNotNull(user.getId());
            Assert.assertNotNull(user.getCreateTime());
            //可以执行下面的 commit 后查看数据库中的数据
            //sqlSession.commit();
            //测试删除刚刚插入的数据
            userMapper.deleteUserById(user.getId());
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.commit();
            sqlSession.close();
        }
    }

# sql # 数据库 # database
Logo
华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

cover

GaussDB轻量化运维管理工具介绍

avatar 华为云开发者联盟
cover

产教融合专家大讲堂·第①期《高校人才培养创新模式经验分享》

avatar 华为云开发者联盟
cover

鲲鹏技术汇 创未来享非凡

avatar 华为云开发者联盟