代理模式實(shí)現(xiàn)mybatis批量插入更新，解決：PacketTooBigException: Packet for query is too large (4,850,051 > 4,194,30...

一、問(wèn)題

數(shù)據(jù)庫(kù)mysql，在使用mybatis進(jìn)行批量插入的時(shí)候，報(bào)錯(cuò)：### 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

二、原因

在對(duì)mysql進(jìn)行插入、更新或查詢操作時(shí)，mysql server接收處理的數(shù)據(jù)包大小是有限制的，如果太大超過(guò)了設(shè)置的max_allowed_packet參數(shù)的大小，會(huì)導(dǎo)致操作失敗，我們可以通過(guò)命令：show VARIABLES like '%max_allowed_packet%';查看參數(shù)值，如：

image.png

三、解決方案

  1. 數(shù)據(jù)庫(kù)層面解決：調(diào)整max_allowed_packet參數(shù)，改大，額，多大合適呢？

  2. 應(yīng)用層面解決：批量插入時(shí)，將入?yún)ist進(jìn)行分割，分批批量插入，控制每次批量插入的大小，避免超過(guò)最大限制

我們這里選擇第2種解決方案

四、代碼實(shí)現(xiàn)

1、v0.1版本（每個(gè)mapper對(duì)應(yīng)的service方法中，都進(jìn)行分割處理）
1.1、我們需要用到google提供的方法：Lists.partition()對(duì)list進(jìn)行分割，引入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：即每次最大插入記錄數(shù)，為方便測(cè)試，我這里取3，大家可以根據(jù)具體場(chǎng)景、數(shù)據(jù)庫(kù)、業(yè)務(wù)需求指定，沒有唯一

其他service以此類推，這樣可以實(shí)現(xiàn)，但是會(huì)發(fā)現(xiàn)重復(fù)代碼一堆，需改進(jìn)

2、v0.2版本（代理模式實(shí)現(xiàn)，對(duì)方法進(jìn)行增強(qiáng)，批量插入前，統(tǒng)一對(duì)集合進(jìn)行分割）
2.1、首先定義一個(gè)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實(shí)現(xiàn)BatchProcessMapper：

package com.wangzaiplus.test.mapper;

import com.wangzaiplus.test.service.batch.BatchProcessMapper;

public interface UserMapper extends BatchProcessMapper<User> {

}

2.3、然后定義MapperProxy也實(shí)現(xiàn)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中直接調(diào)用批量插入方法：

@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同上，一行代碼搞定，省卻重復(fù)代碼

五、mybatis批量插入與更新

1、批量插入：
1.1、語(yǔ)法：

    <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實(shí)際執(zhí)行語(yǔ)句：


image.png

2、批量更新：
2.1、語(yǔ)法：

    <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實(shí)際執(zhí)行語(yǔ)句：


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)

六、總結(jié)

在使用mybatis批量操作數(shù)據(jù)庫(kù)時(shí)，需要注意集合大小，太大會(huì)導(dǎo)致sql執(zhí)行異常，所以在批量操作前，應(yīng)該對(duì)集合進(jìn)行分割處理，然后分批操作，為了減少重復(fù)代碼，可以通過(guò)代理模式對(duì)批量處理方法進(jìn)行增強(qiáng)，客戶端調(diào)用時(shí)只需將具體子類傳給代理，由代理在執(zhí)行批量語(yǔ)句前統(tǒng)一進(jìn)行分割處理

代碼已更新至Github，歡迎大家clone、交流，謝謝
Github
https://github.com/wangzaiplus/springboot/tree/wxw

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

友情鏈接更多精彩內(nèi)容