一、问题
数据库mysql，在使用mybatis进行批量插入的时候，报错：### Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (4,850,051 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.，如：
image.png
二、原因
在对mysql进行插入、更新或查询操作时，mysql server接收处理的数据包大小是有限制的，如果太大超过了设置的max_allowed_packet参数的大小，会导致操作失败，我们可以通过命令：show VARIABLES like '%max_allowed_packet%';查看参数值，如：
image.png
三、解决方案
-
数据库层面解决：调整
max_allowed_packet参数，改大，额，多大合适呢？ -
应用层面解决：批量插入时，将入参list进行分割，分批批量插入，控制每次批量插入的大小，避免超过最大限制
我们这里选择第2种解决方案
四、代码实现
1、v0.1版本（每个mapper对应的service方法中，都进行分割处理）
1.1、我们需要用到google提供的方法：Lists.partition()对list进行分割，引入maven：
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.1</version>
</dependency>
1.2、UserMapper.xml：
<insert id="batchInsert" parameterType="list">
insert into user(username, password)
values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.username}, #{item.password})
</foreach>
</insert>
<insert id="batchUpdate" parameterType="list">
update user
<trim prefix="set" suffixOverrides=",">
<trim prefix="username=case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id = #{item.id} then #{item.username}
</foreach>
</trim>
<trim prefix="password=case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id = #{item.id} then #{item.password}
</foreach>
</trim>
</trim>
where id in
<foreach collection="list" item="item" index="index" separator="," open="(" close=")">
#{item.id}
</foreach>
</insert>
1.3、UserServiceImpl：
@Override
public void batchInsert(List<User> list) {
if (CollectionUtils.isEmpty(list)) {
return;
}
List<List<User>> partition = Lists.partition(list, MAX_SIZE_PER_TIME);
for (List<User> batchList : partition) {
userMapper.batchInsert(batchList);
}
}
@Override
public void batchUpdate(List<User> list) {
if (CollectionUtils.isEmpty(list)) {
return;
}
List<List<User>> partition = Lists.partition(list, MAX_SIZE_PER_TIME);
for (List<User> batchList : partition) {
userMapper.batchUpdate(batchList);
}
}
MAX_SIZE_PER_TIME：即每次最大插入记录数，为方便测试，我这里取3，大家可以根据具体场景、数据库、业务需求指定，没有唯一
其他service以此类推，这样可以实现，但是会发现重复代码一堆，需改进
2、v0.2版本（代理模式实现，对方法进行增强，批量插入前，统一对集合进行分割）
2.1、首先定义一个mapper的批量处理接口：BatchProcessMapper：
package com.wangzaiplus.test.service.batch;
import java.util.List;
public interface BatchProcessMapper<T> {
void batchInsert(List<T> list);
void batchUpdate(List<T> list);
}
2.2、然后UserMapper等mapper实现BatchProcessMapper：
package com.wangzaiplus.test.mapper;
import com.wangzaiplus.test.service.batch.BatchProcessMapper;
public interface UserMapper extends BatchProcessMapper<User> {
}
2.3、然后定义MapperProxy也实现BatchProcessMapper：
package com.wangzaiplus.test.service.batch.mapperproxy;
import com.google.common.collect.Lists;
import com.wangzaiplus.test.service.batch.BatchProcessMapper;
import org.apache.commons.collections4.CollectionUtils;
import java.util.List;
import static com.wangzaiplus.test.common.Constant.MAX_SIZE_PER_TIME;
public class MapperProxy<T> implements BatchProcessMapper<T> {
private BatchProcessMapper batchProcessMapper;
public MapperProxy(BatchProcessMapper batchProcessMapper) {
this.batchProcessMapper = batchProcessMapper;
}
@Override
public void batchInsert(List<T> list) {
if (CollectionUtils.isEmpty(list)) {
return;
}
List<List<T>> partition = Lists.partition(list, MAX_SIZE_PER_TIME);
for (List<T> batchList : partition) {
batchProcessMapper.batchInsert(batchList);
}
}
@Override
public void batchUpdate(List<T> list) {
if (CollectionUtils.isEmpty(list)) {
return;
}
List<List<T>> partition = Lists.partition(list, MAX_SIZE_PER_TIME);
for (List<T> batchList : partition) {
batchProcessMapper.batchUpdate(batchList);
}
}
}
2.4、使用：UserServiceImpl中直接调用批量插入方法：
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public void batchInsert(List<User> list) {
new MapperProxy<User>(userMapper).batchInsert(list);
}
@Override
public void batchUpdate(List<User> list) {
new MapperProxy<User>(userMapper).batchUpdate(list);
}
}
其他service同上，一行代码搞定，省却重复代码
五、mybatis批量插入与更新
1、批量插入：
1.1、语法：
<insert id="batchInsert" parameterType="list">
insert into user(username, password)
values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.username}, #{item.password})
</foreach>
</insert>
1.2、sql实际执行语句：
image.png
2、批量更新：
2.1、语法：
<insert id="batchUpdate" parameterType="list">
update user
<trim prefix="set" suffixOverrides=",">
<trim prefix="username=case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id = #{item.id} then #{item.username}
</foreach>
</trim>
<trim prefix="password=case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id = #{item.id} then #{item.password}
</foreach>
</trim>
</trim>
where id in
<foreach collection="list" item="item" index="index" separator="," open="(" close=")">
#{item.id}
</foreach>
</insert>
2.2、sql实际执行语句：
image.png
格式化一下是这样子：
UPDATE USER
SET username = CASE
WHEN id = ? THEN
?
WHEN id = ? THEN
?
WHEN id = ? THEN
?
END,
PASSWORD = CASE
WHEN id = ? THEN
?
WHEN id = ? THEN
?
WHEN id = ? THEN
?
END
WHERE
id IN (?, ?, ?)
434616(Integer), batchUpdate_3366361bc15048129adfcf5dc851d7e5(String),
434617(Integer), batchUpdate_76370f7c845642f2be0ab1c35440f5bd(String),
434618(Integer), batchUpdate_6e06394ccbbd49c8b97c3e0600a7cada(String),
434616(Integer), 123456(String),
434617(Integer), 123456(String),
434618(Integer), 123456(String),
434616(Integer),
434617(Integer),
434618(Integer)
六、总结
在使用mybatis批量操作数据库时，需要注意集合大小，太大会导致sql执行异常，所以在批量操作前，应该对集合进行分割处理，然后分批操作，为了减少重复代码，可以通过代理模式对批量处理方法进行增强，客户端调用时只需将具体子类传给代理，由代理在执行批量语句前统一进行分割处理
代码已更新至Github，欢迎大家clone、交流，谢谢
Github
https://github.com/wangzaiplus/springboot/tree/wxw
网友评论