存储过程
存储过程在数据库中比较常见，虽然大多数存储过程的调用比较复杂，但是使用 MyBatis 调用时，用法都一样，因此这一节将通过几个简单的存储过程带大家了解 MyBatis 中存储过程的使用方法。
先创建几个不同的存储过程，代码如下。
# 第一个存储过程
# 根据用户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 ;
# 第二个存储过程
# 简单根据用户名和分页参数进行查询，返回总数和分页数据
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 ;
# 第三个存储过程
# 保存用户信息和角色关联信息
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 ;下面针对上述代码中的 4 个简单存储过程分别来使用 MyBatis。
第一个存储过程
在 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。从上面的代码可以很容易看出，IN 和 OUT 两种模式的区别是，OUT 模式的参数必须指定 jdbcType。这是因为在 IN 模式下，MyBatis 提供了默认的 jdbcType，在 OUT 模式下没有提供。另外在使用 Oracle 数据库时，如果入参存在 null 的情况，那么入参也必须指定 jdbcType。
除了上面提到的这几点，headImg 还特别设置了 javaType。在 MyBatis 映射的 Java 类中，不推荐使用基本类型，数据库 BLOB 类型对应的 Java 类型通常都是写成 byte[] 字节数组的形式的，因为 byte[] 数组不存在默认值的问题，所以不影响一般的使用。但是在不指定 javaType 的情况下，MyBatis 默认使用 Byte 类型。由于 byte 是基本类型，所以设置 javaType 时要使用带下画线的方式，在这里就是 _byte[]。_byte 对应的是基本类型，byte 对应的是 Byte 类型，在使用 javaType 时一定要注意。
在 UserMapper 接口中添加相应的方法，代码如下。
/**
* 使用存储过程查询用户信息
* @param user
*/
void selectUserById(SysUser user);因为这个存储过程没有返回值（不要和出参混淆），所以返回值类型使用 void。把返回值设置为 SysUser 或 List 也不会报错，但是任何时候返回值都是 null。
针对该方法，在 UserMapperTest 中编写如下测试。
@Test
public void testSelectUserById(){
//获取 sqlSession
SqlSession sqlSession = getSqlSession();
try {
//获取 UserMapper 接口
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
SysUser user = new SysUser();
user.setId(1L);
userMapper.selectUserById(user);
Assert.assertNotNull(user.getUserName());
System.out.println("用户名：" + user.getUserName());
} finally {
//不要忘记关闭 sqlSession
sqlSession.close();
}
}该测试执行后，输出日志如下。
从日志可以看到，这个存储过程没有返回值，我们使用出参的方式得到了该用户的信息。使用出参方式时，通常情况下会使用对象中的属性接收出参的值，或者使用 Map 类型接收返回值。这两种情况有很大的区别。当使用 JavaBean 对象接收出参时，必须保证所有出参在 JavaBean 中都有对应的属性存在，否则就会抛出类似 “Could not set property’xxx'” 这样的错误。这是由于 JavaBean 对象中不存在出参对应的 setter 方法，使用 Map 类型时就不需要保证所有出参都有对应的属性，当 Map 接收了存储过程的出参时，可以通过 Map 对象的 get("属性名") 方法获取出参的值。
|
除了上面提到的错误，在执行存储过程时还可能会遇到下以下错误。 Parameter number x is not an OUT parameter 产生这个错误可能是因为调用的存储过程不存在，或者 MyBatis 中写的出参和数据库存储过程的出参无法对应。 |
第二个存储过程
继续在 UserMapper.xml 映射文件中添加如下方法。
<select id="selectUserPage" statementType="CALLABLE" useCache="false" resultMap="baseResultMap">
{call select_user_page(
#{userName,mode=IN},
#{offset,mode=IN},
#{limit,mode=IN},
#{total,mode=OUT,jdbcType=BIGINT}
)}
</select>这个方法和第一个方法的区别在于，select 标签还设置了 resultMap，因为该方法通过 total 出参得到了查询的总数，通过存储过程返回了最后的结果集，所以需要设置返回值信息。
在 UserMapper 接口中添加如下方法。
/**
* 使用存储过程分页查询
* @param params userName/pageNum/pageSize/total
* @return 用户集合
*/
List<SysUser> selectUserPage(Map<String,Object> params);由于需要多个入参和一个出参，而入参中除了 userName 属性在 SysUser 中，其他 3 个参数都和 SysUser 无关，因此为了使用 SysUser 而增加 3 个属性也是可以的。这里为了实现方法多样化，也为了印证上一个方法中使用 Map 接收返回值的用法是正确的，因此使用 Map 类型作为参数。
在 UserMapperTest 中添加如下的测试代码。
@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);
Long total = (Long) params.get("total");
System.out.println("总数:" + total);
for(SysUser user : userList){
System.out.println("用户名：" + user.getUserName());
}
} finally {
//不要忘记关闭 sqlSession
sqlSession.close();
}
}测试代码的 Map 参数中不存在 total，执行了存储过程方法后，通过 get 方法得到了 total 的值。上面测试代码的输出日志如下。
为了更有效地测试这个分页查询的存储过程，可以向数据库中增加大量数据进行测试。
第三个和第四个存储过程
由于后面两个存储过程一个是插入用户和用户角色关联数据的，一个是删除用户和用户角色关联数据的，因此可以将这两部分放到一起来介绍。
在 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>这里要注意的是，我们分别使用了 insert 和 delete，同样设置 statementType 属性为 CALLABLE。
在 UserMapper 接口中添加如下两个方法。
/**
* 使用存储过程保存用户信息和角色关联信息
* @param user 用户信息
* @param roleIds 角色ID集合（多个ID之间用英文逗号分隔）
*/
void insertUserAndRoles(@Param("user")SysUser user,@Param("roleIds")String roleIds);
/**
* 根据用户ID删除用户和用户的角色信息
* @param id 用户ID
*/
void deleteUserById(Long id);在 insertUserAndRoles 方法中，SysUser 参数存储了用户基本信息，roleIds 参数存储了该用户的角色 id 字符串，如 "1,2,3"，注意要使用逗号隔开多个 id。
在 UserMapperTest 中添加如下测试。
@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.close();
}
}
执行该方法，输出的日志如下。
在 insertUserAndRoles 存储过程中，我们通过出参实现了主键和日期的回写，之后通过返回的主键删除了对应的全部数据。
在Oracle中使用游标参数的存储过程
由于 MySQL 不支持游标参数，因此除了上面4种简单的存储过程，本节将针对 Oracle 数据库介绍一种简单的游标参数。首先在 Oracle 中创建第 1 章中提到的 Country 表，然后添加如下存储过程。
# 在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。这个存储过程可以返回两个 List 结果，这里都是 country 对应的类型，实际上可以是任何能映射的类型。
在 CountryMapper.xml 中添加如下方法。
<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。除此之外，由于返回的游标是一个多列的复杂结果，因此要使用 resultMap 配置游标结果列的映射。
在 CountryMapper 接口中添加如下方法。
/**
* 执行Oracle中的存储过程
* @param params 入数
* @return 出参
*/
Object selectCountries(Map<String,Object> params);可以增加 Oracle 的 JDBC 驱动，然后修改 mybatis-config.xml 中的数据库配置，在可以连接到 Oracle 数据库的前提下，执行下面的测试代码。
@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();
}
}执行该测试，输出的日志如下。
常见的存储过程大概就是以上几种，只要掌握了这几种用法，几乎就可以应对所有的情况了。