Mybatis数据插入——批处理

曾经终败给现在 2023-09-27 16:47 7阅读 0赞

场景：有时需要从A表中获取数据，经过处理/或直接插入到B表中\[不管是否同库\]，但是数据量较大时，一般两种1.分批次查询、插入2.一次查询结果，多次分批插入表中

### 一、Lists.partition方法 ###

演示多次分批次插入表中

小实例演示：

ArrayList<String> list = new ArrayList<>();
            list.add("11");
            list.add("12");
            list.add("13");
            list.add("14");
            list.add("15");
            list.add("16");
            list.add("17");
            list.add("18");
            List<List<String>> partition = Lists.partition(list, 3);
            for (List<String> strings : partition) {
                System.out.println(strings);
            }
    结果：
    [11, 12, 13]
    [14, 15, 16]
    [17, 18]

**实际演示：**

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.swsmu.ebs.ebsbackend.ebsuser.mapper.TestMapper">
        <insert id="saveBatch">
            INSERT INTO product_test1 (`fund_code`,`fund_name`,`interview`,`deleted`,`born_date`) VALUES
            <foreach collection="list" separator="," item="item">
                (#{item.fundCode},#{item.fundName},#{item.interview},0,#{item.bornDate})
            </foreach>
        </insert>

因为Mybatis原生批量插入有限制大小。故数据量大时不能一下拼接插入，需要分批次

> **com.mysql.jdbc.PacketTooBigException: Packet for query is too large (8349802 > 4194304). You can change this value on the server by setting the max\_allowed\_packet’ variable.**
> 
> **查询默认/设置的 字符串大小：**
> 
>     select @@max_allowed_packet;  数值/1024/1024  (M)
> 
> **修改大小：\[很少修改！基本都是设置好的\]**
> 
>     SET GLOBAL max_allowed_packet=268435456;

Java代码 **核心Lists.partition(list, 500);**

**目标集合按照传入的size进行分组**

System.out.println("一共插入"+list.size()+"条数据");
     // 分片批量插入
    // 分为 n 份，每份 500条
     List<List<FundProduct>> listPartition = Lists.partition(list, 500);
     // 分片批量插入
      for (List<FundProduct> item : listPartition) {
         mapper.saveBatch(item);
       }

**对应pom文件**

<dependency>
          <groupId>com.google.guava</groupId>
          <artifactId>guava</artifactId>
          <version>28.2-jre</version>
     </dependency>

***注意：***当上图中 list.clear()时，list.size=0 —》 listPartition.size=0

原始集合与目标集合相互影响

#### 延伸Lists.partiton(),源码中的 RandomAccess ####

public static <T> List<List<T>> partition(List<T> list, int size) {
        checkNotNull(list);
        checkArgument(size > 0);
        return (list instanceof RandomAccess)
            ? new RandomAccessPartition<>(list, size)
            : new Partition<>(list, size);
      }

疑问：为什么对集合做是否为RandomAccess的子类判断？

![baaed8bc27a14f8a98fc89cf6ebf404f.png][]

发现RandomAccess接口是一个标志接口，作用支持快速随机访问

通过查看Collections类中的binarySearch（）源代码，发现实现RandomAccess接口的List集合采用一般的for循环遍历，而未实现这接口则采用迭代器

**直接结论：**ArrayList用for循环遍历比iterator迭代器遍历快，LinkedList用iterator迭代器遍历比for循环遍历快

故集合要判断是不是实现了RandomAccess接口，来做决定用for循环还是迭代器Iterator

> if( list instanceof RandomAcess )\{
> 
> for循环
> 
> \}else\{
> 
> Iterator iterator= list.iterator();
> 
> \}

Java8后。stream流用的较多。确实蛮方便

### 二、批量新增 ps.addBatch(); ###

public void batchUpdate() {
            long begin = System.currentTimeMillis();
            try {
                Class.forName(driverClass);
            } catch (ClassNotFoundException e) {
                return;
            }
    
            //插入sql语句
            StringBuilder sql = new StringBuilder();
            sql.append("update batch_test set test_name = ?, create_time = ?, update_time = ? where id = ?");
            int count = 0;
            try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
                 PreparedStatement ps = conn.prepareStatement(sql.toString())) {
                conn.setAutoCommit(false);
                for (int i = 0; i < 50000; i++) {
                    ps.setString(1, "name" + (i));
                    ps.setDate(2, new Date(System.currentTimeMillis()));
                    ps.setDate(3, new Date(System.currentTimeMillis()));
                    ps.setLong(4, i);
                    ps.addBatch();
                    count++;
                    if ((i + 1) % 5000 == 0) {
                        ps.executeBatch();
                        conn.commit();
                        ps.clearBatch();
                    }
                }
                ps.executeBatch();
                conn.commit();
            } catch (SQLException e) {
                e.printStackTrace();
                return;
            }
            // 结束时间
            Long end = System.currentTimeMillis();
            // 耗时
            System.out.println("总计单条循环更新" + count + "条，共计耗时" + (end - begin) / 1000 + "秒");
        }

注意jdbcUrl连接

> &allowMultiQueries=true&rewriteBatchedStatements=true  
> allowMultiQueries：true时一次多条sql语句，中间;号隔开，不报错否则报错
> 
> rewriteBatchedStatements：true时允许批量更新\[inseret/delete/update\]均有效，否则jdbc驱动将一组sql自动拆分单条sql形式【实际还是单条更新】

### 三、ExecutorType ###

单条插入，只是手动提交。

//我们使用的是springboot，sqlSessionTemplate是可以自己注入的
    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;
     
    public void insertExcelData(List<User> list) {
        //如果自动提交设置为true,将无法控制提交的条数，改为最后统一提交，可能导致内存溢出
        SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
        //不自动提交
        try {
            UserDao userDao = session.getMapper(UserDao.class);
            for (int i = 0; i < list.size(); i++) {
                userDao.insert(list.get(i));
                if (i % 400 == 0 || i == list.size() - 1) {
                    //手动每400条提交一次，提交后无法回滚
                    session.commit();
                    //清理缓存，防止溢出
                    session.clearCache();
                }
            }
        } catch (Exception e) {
            //没有提交的数据可以回滚
            session.rollback();
        } finally {
            session.close();
        }
    }
     
    userDao.insert(User user);
     
    <insert id="insert" parameterType="com.echo.UserPo">
        insert into USER
        (id
        <if test="age != null">
            ,age
        </if>
        <if test="name != null">
            ,name
        </if>
        <if test="email != null">
            ,email
        </if>
        )
        values (
        sys_guid()
        <if test="age != null">
            ,#{age}
        </if>
        <if test="name != null">
            ,#{name}
        </if>
        <if test="email != null">
            ,#{email}
        </if>)
    </insert>

[baaed8bc27a14f8a98fc89cf6ebf404f.png]: https://img-blog.csdnimg.cn/baaed8bc27a14f8a98fc89cf6ebf404f.png