今天来分享使用 MyBatis 批量操作可能会遇到的一些坑。下面我们以一张员工信息表为例进行讲解，建表 SQL 如下(MySQL)：
CREATE TABLE `staff` (
`staff_id` tinyint(3) NOT NULL COMMENT '员工编号',
`name` varchar(20) DEFAULT NULL COMMENT '员工姓名',
`age` tinyint(3) DEFAULT NULL COMMENT '年龄',
`sex` tinyint(1) DEFAULT '0' COMMENT '性别，0:男 1:女',
`address` varchar(300) DEFAULT NULL COMMENT '家庭住址',
`email` varchar(200) DEFAULT NULL COMMENT '邮件地址',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1.查询条数
<select id="getStaffList" parameterType="int" resultType="Admin">
select * from staff limit #{offset},50000
select>
对应 Java 代码如下：
public List processStaffList(){
int offset = 0;
List staffList = staffDao.getStaffList(offset);
while(true){
//...处理逻辑
if(staffList.size() 50000){
break;
}
offset += 50000;
staffList = staffDao.getStaffList(offset);
}
}
上面的查询想一次想查回 50000 条数据，很有可能数据库不能返回 50000 条。一般数据库都有查询结果集限制，比如 MySQL 会受两个参数的限制：
-
max_allowed_packet，返回结果集大小，默认 4M，超过这个大小结果集就会被截断；
-
max_execution_time，一次查询执行时间，默认值是 0 表示没有限制，如果超过这个时间，MySQL 会终止查询，返回结果。
所以，如果结果集太大不能全部返回，而我们在代码中每次传入的 offset 都是基于上次的 offset 加 50000，那必定会漏掉部分数据。
2.分页问题
<select id="getStaffList" resultType="Staff">
select * from staff limit #{offset},1000
select>
如果单表数据量非常大，offset 会很大造成深度分页问题，查询效率低下。我们可以通过传入一个起始的 staffId 来解决深度分页问题。
我们修改一下 xml 中的代码：
<select
id="getStaffList" resultType="Staff">
select * from staff
<if test="staffId != null">
WHERE staff_id > #{staffId}
if>
order by staff_id limit 1000
select>
对应 Java 代码如下：
public List processStaffList(){
List staffList = staffDao.getStaffList(null);
while(true){
//...处理逻辑
if(staffList.size() 1000){
break;
}
Staff lastStaffInPage = staffList.get(staffList.size() - 1);
staffList = staffDao.getStaffList(lastStaffInPage.getStaffId());
}
}
3.参数数量
下面看一下这一条插入 SQL：
"batchInsertStaff">
INSERT INTO staff (`staff_id`, `name`, `age`, `sex`, `address`, `email`) VALUES
="staffList" index="" item="item" separator=",">
(#{item.staffId,}, #{item.name},#{item.age},#{item.sex},#{item.address},#{item.email})
上面的代码如果 staffList 数量太大，会导致整条语句参数过多。如果使用 Oracle 数据库，参数数量超过 65535，会报 ORA-7445([opiaba]when using more than 65535 bind variables) 的错误，导致数据库奔溃。一定要对参数数量进行限制。
参数太多，也可能会抛出下面异常。
4.参数类型
上一节的代码中，插入语句并没有指定参数类型。这样会有一个问题，虽然一个字段我们定义成可以为空，但是通过参数传进来的这个字段值是空，就会抛出下面异常导致插入失败。
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping:
ParameterMapping{property='_frch_item_50.name', mode=IN, javaType=class java.lang.Object, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null',
expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #5 with JdbcType OTHER .
Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 无效的列类型: 1111
要保证程序健壮性，就要给插入语句中参数指定类型，上面代码优化后如下：
"batchInsertStaff">
INSERT INTO staff (`staff_id`, `name`, `age`, `sex`, `address`, `email`) VALUES
="staffList" index="" item="item" separator=",">
(#{item.staffId,jdbcType=TINYINT}, #{item.name,jdbcType=VARCHAR},#{item.age,jdbcType=TINYINT},#{item.sex,jdbcType=TINYINT},#{item.address,jdbcType=VARCHAR},#{item.email,jdbcType=VARCHAR})
5.批量条数
批量操作是为了减少应用和数据库的交互，提高操作效率。但是如果对插入、更新这些批量操作不做条数限制，很可能会导致操作效率低下甚至数据库 hang 住。我们可以通过分页操作对批量条数做一些限制，看下面示例代码：
public List processStaffList(){
List staffList = ...;
int pageSize = 500;
int pageCount = staffList / pageSize;
for(int i = 0; i 1; i++){
List subList = (i == pageCount)? staffList.subList(i * pageSize, staffList.size()) :
staffList.subList(i * pageSize, (i + 1) * pageSize);
staffDao.batchInsertStaff(subList);
}
}
总结
作为一个 orm 框架，无论我们选择 JDBC、MyBatis 还是 MyBatis-Plus，批量操作最终都是要操作底层数据库，批量性能怎么样、会不会出问题，主要还得参考底层数据库的能力。因此，想用好批量，首先要了解数据库的特性。
