Mybatis批量插入

三种批量插入的方式

• 循环插入
• foreach标签
• 批处理

数据库和实体类

 

package com.yang.model;

/**
 * Mybatis批量插入测试实体类
 *
 * @Author: chenyang
 * @Date: 2021/11/25 12:33
 */
public class Person {

    /**
     * 用户名
     */
    private String name;
    /**
     * 用户密码
     */
    private String password;

    // get set tostring
}

循环插入

for循环调用dao层的单条插入方法

insert into table ([列名],[列名])  values ([列值],[列值]));
或：
insert into table values ([列值],[列值]));

     <insert id="insertPerson" parameterType="person">
        INSERT INTO m_person(name, password, createtime)
        VALUES (#{name}, #{password}, now())
    </insert>

    @Test
    public void test1(){
        // 初始化数据
        ArrayList<Person> personList = new ArrayList<>();
        for (int i = 0; i < 1000; i++) {
            personList.add(new Person("N "+ i, "P "+i));
        }

        long start = System.currentTimeMillis();

        // 循环插入数据
        personList.forEach(personDao::insertPerson);
        
        //计算插入1000条数据所用时间
        System.out.println(System.currentTimeMillis() - start); // 144401
    }

foreach

原生批量插入方法是依靠 MyBatis 中的 foreach 标签，将数据拼接成一条原生的 insert 语句一次性执行的, 

好处：可以避免程序和数据库建立多次连接，增加服务器负荷。

insert into table  ([列名],[列名]) 
 VALUES
([列值],[列值]),
([列值],[列值]),
([列值],[列值]);

    <insert id="insertPersons" parameterType="person">
        INSERT INTO m_person(name, password, createtime) VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.name}, #{item.password}, now())
        </foreach>
    </insert>

参数解释：

foreach 的主要作用在构建 in 条件中，它可以在 sql 语句中进行迭代一个集合。foreach 元素的属性主要有 collection，item，separator，index，open，close。

1. collection：指定要遍历的集合。表示传入过来的参数的数据类型。该属性是必须指定的，要做 foreach 的对象。在使用 foreach 的时候最关键的也是最容易出错的就是 collection 属性。在不同情况 下，该属性的值是不一样的，主要有一下 3 种情况： a. 如果传入的是单参数且参数类型是一个 List 的时候，collection 属性值为 list。 b. 如果传入的是单参数且参数类型是一个数组的时候，collection 的属性值为 array。 c. 如果传入的参数是多个的时候，就需要把它们封装成 Map，当然单参数也可以封装成 Map。Map 对象没有默认的键。

2. item：表示集合中每一个元素进行迭代时的别名。将当前遍历出的元素赋值给指定的变量，然后用#{变量名}，就能取出变量的值，也就是当前遍历出的元素。

3. separator：表示在每次进行迭代之间以什么符号作为分隔符。select * from tab where id in(1,2,3)相当于1,2,3之间的","

4. index：索引。index 指定一个名字，用于表示在迭代过程中，每次迭代到的位置。遍历 list 的时候 index 就是索引，遍历 map 的时候 index 表示的就是 map 的 key，item 就是 map 的值。

    @Test
    public void test2(){
        // 初始化数据
        ArrayList<Person> personList = new ArrayList<>();
        for (int i = 0; i < 1000; i++) {
            personList.add(new Person("N "+ i, "P "+i));
        }

        long start = System.currentTimeMillis();

        // 循环插入数据
        personDao.insertPersons(personList);

        //计算插入1000条数据所用时间
        System.out.println(System.currentTimeMillis() - start); // 861
    }

可能出现的问题：

• 无法获取批量插入的id
• 拼接的SQL语句过大从而导致程序执行错误，因为默认情况下 MySQL 可以执行的最大 SQL（大小）为 4M。

这就是原生批量插入方法的缺点，也是为什么 MP 需要分批执行的原因，就是为了防止程序在执行时，因为触发了数据库的最大执行 SQL 而导致程序执行报错。

当然我们也可以通过设置 MySQL 的最大执行 SQL 来解决报错的问题，设置命令如下：

-- 设置最大执行 SQL 为 10M
set global max_allowed_packet=10*1024*1024;

对于批量插入中间有一个失败会怎么样

批量语句，只要有一个失败，就会全部失败。数据库会回滚全部数据。

关于测试过程可以看这篇博客：mysql批量插入语句执行失败的话，是部分失败还是全部失败

首先我们知道了mybatis <foreache>批量插入，是在程序内拼接sql 语句（拼接成多条同时插入的sql语句），拼接后发给数据库。

就相当于咱们自己在mysql的命令行中，执行一条多插入的语句。默认情况下 mysql 单条语句是一个事务，这在一个事务范围内，当中间的sql语句有问题，或者有一个插入失败，就会触发事务回滚。同时你也能看到错误提示。（命令行执行单条sql的情况）

所以有一个插入不成功肯定全部回滚。

所以一般情况下我们推荐即使使用批量插入，也要分批次。

每次批次设置多少？需要根据你的插入一条数据的参数量来做度量。因为受限条件是sql语句的长度。

而且分批插入更加合理，对于插入失败，回滚范围会缩小很多。

前面两者对比

循环插入：需要每次都获取session,获取连接，然后将sql 语句发给mysql 去执行（JDBC一般情况下是通过TCP/IP 进行连接和数据库进行通信的）。可以看这里 mysql四种通信协议

批量插入： 批量插入通过foreach 标签，将多条数据拼接在sql 语句后，一次执行只获取一次session,提交一条sql语句。减少了程序和数据库交互的准备时间

 

批处理

Mybatis内置的ExecutorType有3种，SIMPLE、REUSE、BATCH; 默认的是simple，该模式下它为每个语句的执行创建一个新的预处理语句，单条提交sql；而batch模式重复使用已经预处理的语句，并且批量执行所有更新语句，显然batch性能将更优；但batch模式也有自己的问题，比如在Insert操作时，在事务没有提交之前，是没有办法获取到自增的id，这在某型情形下是不符合业务要求的；

我在测试一开始，发现改成 Mybatis Batch提交的方法都不起作用，实际上在插入的时候仍然是一条条记录的插，而且速度远不如原来 foreach 拼接SQL的方法，这是非常不科学的。

后来才发现要批量执行的话，连接URL字符串中需要新增一个参数：rewriteBatchedStatements=true

• rewriteBatchedStatements参数介绍

MySql的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。MySql JDBC驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组sql语句拆散，一条一条地发给MySql数据库，批量插入实际上是单条插入，直接造成较低的性能。只有把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL。这个选项对INSERT/UPDATE/DELETE都有效。

    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    @Test
    public void test3(){
        // 初始化数据
        ArrayList<Person> personList = new ArrayList<>();
        for (int i = 0; i < 1000; i++) {
            personList.add(new Person("N "+ i, "P "+i));
        }

        long start = System.currentTimeMillis();

        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        PersonDao mapper = sqlSession.getMapper(PersonDao.class);

        for (int i = 0; i < 1000; i++) {
            mapper.insertPerson(personList.get(i));
            if (i % 500 == 0){
                // 每500条数据一起提交
                sqlSession.commit();
                // 清空缓存
                sqlSession.clearCache();
            }
        }
        sqlSession.commit();
        sqlSession.clearCache();
        //计算插入1000条数据所用时间
        System.out.println(System.currentTimeMillis() - start); // 632
    }

三者效率对比：批处理 > foreach >循环插入

SqlSessionFactory

SqlSessionFactory 有六个方法创建 SqlSession 实例。通常来说，当你选择其中一个方法时，你需要考虑以下几点：

• 事务处理：你希望在 session 作用域中使用事务作用域，还是使用自动提交（auto-commit）？（对很多数据库和/或 JDBC 驱动来说，等同于关闭事务支持）

• 数据库连接：你希望 MyBatis 帮你从已配置的数据源获取连接，还是使用自己提供的连接？

• 语句执行：你希望 MyBatis 复用 PreparedStatement 和/或批量更新语句（包括插入语句和删除语句）吗？

基于以上需求，有下列已重载的多个 openSession() 方法供使用。

 SqlSession openSession()
 SqlSession openSession(boolean autoCommit)
 SqlSession openSession(Connection connection)
 SqlSession openSession(TransactionIsolationLevel level)
 SqlSession openSession(ExecutorType execType, TransactionIsolationLevel level)
 SqlSession openSession(ExecutorType execType)
 SqlSession openSession(ExecutorType execType, boolean autoCommit)
 SqlSession openSession(ExecutorType execType, Connection connection)
 Configuration getConfiguration();