http://www.broadview.com.cn/book/3643
https://github.com/mybatis-book/book

根据用户 id 查询用户其他信息

# 根据用户 id 查询用户其他信息
# 方法看着很奇葩，但是展示了多个输出参数
DROP PROCEDURE IF EXISTS `select_user_by_id`;
DELIMITER ;;
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
;;
DELIMITER ;

<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 标签调用存储过程时，由于存储过程不支持二级缓存，因此避免出错将 useCache 设置为 false。

在存储过程中使用参数时，除了写上必要的属性名，还必须指定参数的 mode，可选值 IN（入参），OUT（出参），INOUT（输入输出）。OUT 模式必须指定 jdbcType。在使用 Oracle 时，如果入参存在 null 的情况，那么入参也必须指定 jdbcType。

void selectUserById(SysUser user);

这个存储过程没有返回值（不要和出参混淆）。

使用出参方式时，使用 JavaBean 对象接收出参时，必须保证所有出参在 JavaBean 中都有对应的属性存在，否则会抛出类似 “Could not set property 'xxx'” 这样的错误。使用 Map 类型接收出参时不需要保证所有出参都有对应的属性。

简单的根据用户名和分页参数进行查询，返回总数和分页数据

# 简单的根据用户名和分页参数进行查询，返回总数和分页数据
DROP PROCEDURE IF EXISTS `select_user_page`;
DELIMITER ;;
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
;;
DELIMITER ;

<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 userName
* @param pageNum
* @param pageSize
* @param total
* @return
*/
List<SysUser> selectUserPage(Map<String, Object> params);

@Test
public void testSelectUserPage(){
    // 获取 sqlSession
    SqlSession sqlSession = getSqlSession();
    try {
        // 获取 UserMapper 接口
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("userName", "ad");
        params.put("offset", 0);
        params.put("limit", 10);
        List<SysUser> userList = userMapper.selectUserPage(params);  // 也可以使用 JavaBean
        Long total = (Long) params.get("total");
        System.out.println("总数:" + total);
        for(SysUser user : userList){
            System.out.println("用户名：" + user.getUserName());
        }
    } finally {
        // 不要忘记关闭 sqlSession
        sqlSession.close();
    }
}

添加删除用户

# 保存用户信息和角色关联信息
DROP PROCEDURE IF EXISTS `insert_user_and_roles`;
DELIMITER ;;
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
;;
DELIMITER ;

# 删除用户信息和角色关联信息
DROP PROCEDURE IF EXISTS `delete_user_by_id`;
DELIMITER ;;
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
;;
DELIMITER ;

<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>

/**
* 保存用户信息和角色关联信息
*/
int insertUserAndRoles(@Param("user")SysUser user, @Param("roleIds")String roleIds);

/**
* 根据用户 id 删除用户和用户的角色信息
*/
int deleteUserById(Long id);

在 Oracle 中使用游标参数的存储过程

create or replace procedure SELECT_COUNTRIES(
    ref_cur1 out sys_refcursor,
    ref_cur2 out sys_refcursor) is
begin
  open ref_cur1 for select * from country where id < 3;
  open ref_cur2 for select * from country where id >= 3;
end SELECT_COUNTRIES;

这个存储过程只有两个游标类型的出参，游标对应的值就是两个简单的 SQL。

<select id="selectCountries" statementType="CALLABLE" useCache="false">
    {call SELECT_COUNTRIES(
        #{list1, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=BaseResultMap},
        #{list2, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=BaseResultMap}
    )}
</select>

使用游标类型时，注意设置 jdbcType=CURSOR, javaType=ResultSet

/**
* 执行 Oracle 中的存储过程
*/
Object selectCountries(Map<String, Object> params);

@Test
public void testMapperWithStartPage3() {
    SqlSession sqlSession = getSqlSession();
    CountryMapper countryMapper = sqlSession.getMapper(CountryMapper.class);
    try {
        // 获取第1页，10条内容，默认查询总数count
        Map<String, Object> params = new HashMap<String, Object>();
        countryMapper.selectCountries(params);
        List<Country> list1 = (List<Country>) params.get("list1");
        List<Country> list2 = (List<Country>) params.get("list2");
        Assert.assertNotNull(list1);
        Assert.assertNotNull(list2);
    } finally {
        sqlSession.close();
    }
}