mybatis实现批量插入或更新

二掌柜，酒来！

已于 2024-04-19 17:28:41 修改

阅读量4.2k

点赞数 5

分类专栏： jdbc mybatis 文章标签： mybatis java 开发语言

于 2023-05-08 14:26:01 首次发布

本文链接：https://blog.csdn.net/qzibidog/article/details/130387383

版权

jdbc 同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

mybatis

4 篇文章 0 订阅

订阅专栏

批量插入

批处理实现思路与方法

批处理实现思路与方法

以下内容皆基于MySQL进行实现

目前，主要实现批处理主要包括两种手段(我就接触过这两种)。分别是，Statement批处理和动态拼接参数进行批处理。

Statement批处理

三种执行器

mybatis提供三种sql执行器，分别是SIMPLE（默认）、REUSE、BATCH。

SIMPLE（SimpleExecutor），相当于JDBC的stmt.execute(sql) 执行完毕即关闭即 stmt.close()
REUSE（ReuseExecutor），相当于JDBC的stmt.execute(sql) 执行完不关闭，而是将stmt存入 Map<String, Statement>中缓存，其中key为执行的sql模板；
BATCH（BatchExecutor），相当于JDBC语句的 stmt.addBatch(sql)，即仅将执行SQL加入到批量计划但是不真正执行，所以此时不会执行返回受影响的行数，而只有执行stmt.execteBatch()后才会真正执行sql。

三种方式各有利弊

方式	优势	劣势
SIMPLE	默认执行器，节约服务器资源	每次都要开关Statement
REUSE	提升后端接口处理效率	每次一个新sql都缓存，增大JVM内存压力
BATCH	专门用于更新插入操作，效率最快	对select 不适用，另外特殊要求，比如限制一次execteBatch的数量时需要写过滤器定制

实现

设置为batch模式
springboot 下开启 batch模式比较简单，

全局方式开通batch
在yml文件中添加如下配置即可。

mybatis:
  executor-type: batch

原因是mybatis-spring-boot源码，this.properties 就是读取了yml中的内容：

@Bean
    @ConditionalOnMissingBean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        ExecutorType executorType = this.properties.getExecutorType();
        if (executorType != null) {
            return new SqlSessionTemplate(sqlSessionFactory, executorType);
        } else {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

方法中直接指定batch
这个方式的缺陷就是事务方面不受spring管理了。

    @Autowired
    protected SqlSessionFactory sqlSessionFactory;
    
    public void saveOrder(Order t) {
		SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
		OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
		
		try{
			orderMapper.save(t);
			sqlSession.commit();
		}catch(Exception e){
			logger.error("批量导入数据异常，事务回滚", e);
			sqlSession.rollback();
		}finally {
			if (sqlSession != null) {
		  		sqlSession.close();
			}
	}
}

但是这个样子的代码过于丑陋，难道每写一个批处理就，写一串这个代码？不够优雅。jdk8之后，支持方法作为参数，就有了更优雅的写法。关于Function不太了解的同学，可以看看这篇文章。Java 8 之Function<T, R>和BiFunction<T,U,R>接口

 /**
    * 批量处理修改或者插入
    *
    * @param data     需要被处理的数据
    * @param mapperClass  Mybatis的Mapper类
    * @param function 自定义处理逻辑
    * @return int 影响的总行数
    */
public  <T,U,R> int batchUpdateOrInsert(List<T> data, Class<U> mapperClass, BiFunction<T,U,R> function) {
        int i = 1;
        SqlSession batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        try {
            U mapper = batchSqlSession.getMapper(mapperClass);
            int size = data.size();
            for (T element : data) {
                function.apply(element,mapper);
                if ((i % BATCH_SIZE == 0) || i == size) {
                    batchSqlSession.flushStatements();
                }
                i++;
            }
            // 非事务环境下强制commit，事务情况下该commit相当于无效
            batchSqlSession.commit(!TransactionSynchronizationManager.isSynchronizationActive());
        } catch (Exception e) {
            batchSqlSession.rollback();
            throw new RuntimeException(e);
        } finally {
            batchSqlSession.close();
        }
        return i - 1;
    }
}
// 代码来源：https://zhuanlan.zhihu.com/p/513842503

使用起来呢就是大概是这个样子

batchUtils.batchUpdateOrInsert(数据集合, xxxxx.class, (item, mapper实例对象) -> mapper实例对象.insert方法(item));

注意了，这里有一个点，是xxxxx.class，不要使用xxxmapper.getClass(),因为xxxmapper 一般加上自动注入注解，由spring容器注入代理对象，而不是原始类对象。会造成batchSqlSession 找不到 mapper 对象。

动态拼接参数

使用mybatis中为我们提供的标签，来动态拼接参数至执行的sql语句。

实现

对应mapper

public int batchInsert(@Param(sysUserList) List<SysUser> sysUserList) {
        return sysUserMapper.batchInsert(sysUserList);
}

<insert id="batchInsert" useGeneratedKeys="true" keyProperty="id">
        insert into sys_user (user_name, user_code, user_info, status) values
        <foreach collection="sysUserList" item="user" separator=",">
            (#{user.userName}, #{user.userCode}, #{user.userInfo}, #{user.status})
        </foreach>
    </insert>

collection=“sysUserList”,其实可以直接写成 collection=“list”，但是如果有多个list，你就要像上面那样干了

乍看上去这个foreach没有问题，但是经过项目实践发现，当表的列数较多（20+），以及一次性插入的行数较多（5000+）时，整个插入的耗时十分漫长，达到了14分钟，这是不能忍的。在资料中也提到了一句话：

Of course don’t combine ALL of them, if the amount is HUGE. Say you have 1000 rows you need to insert, then don’t do it one at a time. You shouldn’t equally try to have all 1000 rows in a single query. Instead break it into smaller sizes.

它强调，当插入数量很多时，不能一次性全放在一条语句里。可是为什么不能放在同一条语句里呢？这条语句为什么会耗时这么久呢？我查阅了资料发现：

Insert inside Mybatis foreach is not batch, this is a single (could become giant) SQL statement and that brings drawbacks:
some database such as Oracle here does not support.
in relevant cases: there will be a large number of records to insert and the database configured limit (by default around 2000 parameters per statement) will be hit, and eventually possibly DB stack error if the statement itself become too large.
Iteration over the collection must not be done in the mybatis XML. Just execute a simple Insertstatement in a Java Foreach loop. The most important thing is the session Executor type.

从资料中可知，默认执行器类型为Simple，会为每个语句创建一个新的预处理语句，也就是创建一个PreparedStatement对象。
在我们的项目中，会不停地使用批量插入这个方法，而因为MyBatis对于含有的语句，无法采用缓存，那么在每次调用方法时，都会重新解析sql语句。

Internally, it still generates the same single insert statement with many placeholders as the JDBC code above.
MyBatis has an ability to cache PreparedStatement, but this statement cannot be cached because it containselement and the statement varies depending on the parameters. As a result, MyBatis has to 1) evaluate the foreach part and 2) parse the statement string to build parameter mapping [1] on every execution of this statement. And these steps are relatively costly process when the statement string is big and contains many placeholders.
[1] simply put, it is a mapping between placeholders and the parameters.

从上述资料可知，耗时就耗在，由于我foreach后有5000+个values，所以这个PreparedStatement特别长，包含了很多占位符，对于占位符和参数的映射尤其耗时。并且，查阅相关资料可知，values的增长与所需的解析时间，是呈指数型增长的。

一下数据，本人未验证，原文连接 https://mp.weixin.qq.com/s/-hY7tu5HGkpeOcW0SnYCPg，有兴趣的同学，可以试试。

在这里插入图片描述

所以，如果非要使用 foreach 的方式来进行批量插入的话，可以考虑减少一条 insert 语句中 values 的个数，最好能达到上面曲线的最底部的值，使速度最快。一般按经验来说，一次性插20~50行数量是比较合适的，时间消耗也能接受。

两者结合

如果我们认真看一看的话，会发现这两种实现其实并无冲突，有没有一种可能，这两种方法还能结合一下，变得更快(✪ω✪)。实现思路就是，外面采用批处理，里面又使用了拼接多个参数。那么，看好了

/**
     * 批量处理修改或者插入
     *
     * @param data        需要被处理的数据
     * @param mapperClass Mybatis的Mapper类
     * @param function    自定义处理逻辑
     * @return void
     */
    public <T, U, R> void batchUpdateOrInsert(List<T> data, Class<U> mapperClass, BiFunction<List<T>, U, R> function) throws Exception {
        if (data.size() == 0) {
            log.warn("批插入的数据为空！");
            return ;
        }
        int batchCount = 500;
        SqlSession batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        try {
            U mapper = batchSqlSession.getMapper(mapperClass);
            int size = data.size();
            // 每批最后一个的下标
            int batchLastIndex = batchCount;

            for (int index = 0; index < size; ) {

                if (batchLastIndex >= size) {
                    //一波流
                    batchLastIndex = size;
                    //执行批处理
                    function.apply(data.subList(index, batchLastIndex), mapper);
                    batchSqlSession.commit(!TransactionSynchronizationManager.isSynchronizationActive());
                    batchSqlSession.clearCache();
                    LogHelper.info(log,"batchInsertWpRealOverdueDetail", "index:" + index + " batchLastIndex:" + batchLastIndex);
                    // 数据插入完毕，退出循环
                    break;
                } else {
                    // 多插几次5000
                    function.apply(data.subList(index, batchLastIndex), mapper);
                    batchSqlSession.commit(!TransactionSynchronizationManager.isSynchronizationActive());
                    batchSqlSession.clearCache();
                    LogHelper.info(log, "batchInsertWpRealOverdueDetail", "index:" + index + " batchLastIndex:" + batchLastIndex);
                    // 设置下一批下标
                    index = batchLastIndex;
                    batchLastIndex = index + batchCount;
                }
            }
             非事务环境下强制commit，事务情况下该commit相当于无效
            batchSqlSession.commit(!TransactionSynchronizationManager.isSynchronizationActive());
        } catch (Exception e) {
            log.error("批处理失败！",e);
            e.printStackTrace();
            batchSqlSession.rollback();
            throw e;
        } finally {
            batchSqlSession.close();
        }
    }

本人实测，确实很快啊！500条数据量的情况下，就比foreach快上一点，数据量更大的时候，优势会更明显。

当然要注意,当数据表字段过多时，注意削减foreach连接长度

这是5000条数据量的插入测试（只包含foreach 和 foreach+batch两种方式，其他时间太长了）
5000条数据实测

潜在问题

当然，如果我们细心一点，会发现其实这里面有一个大坑！前面的成功插入了BATCH_SIZE条数据，并commit了，但是如果后面的插入出现了异常，岂不是出现了部分插入的情况！！！

其实对于这种情况，我们只需要加上@Transactional 注解就可以了。因为我们在catch里面 throw e;, spring 会自动帮我们回滚的。

再次封装

对于上面针对类方法的封装还是不够简洁，于是我后面讲这个功能做了一个工具类。



import lombok.Data;
import lombok.NonNull;
import lombok.RequiredArgsConstructor;
import lombok.experimental.Accessors;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import java.util.List;
import java.util.function.BiFunction;

/**
 * @Author: liuql
 * @Description: 执行批量方法的工具类
 */
@Slf4j
@Data
public class BatchInsertOrUpdateUtil {

    @NonNull
    private SqlSessionFactory sqlSessionFactory;

    @NonNull
    private DataSourceTransactionManager manager;

    public BatchInsertOrUpdateUtil(@NonNull SqlSessionFactory sqlSessionFactory, @NonNull DataSourceTransactionManager manager) {
        this.sqlSessionFactory = sqlSessionFactory;
        this.manager = manager;
    }

    /**
     * 事务声明
     */
    private DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    {
        // 非只读模式
        def.setReadOnly(false);
        //事务隔离级别 采用数据库默认的
        def.setIsolationLevel(DefaultTransactionDefinition.ISOLATION_DEFAULT);
        //事务传播行为 - required  支持当前事务，如果当前没有事务，就新建一个事务。调用这个方式时，如果外层方法上加了@Transactional注解，事务由外层方法控制。否则
        def.setPropagationBehavior(DefaultTransactionDefinition.PROPAGATION_REQUIRED);
    }


    /**
     * 一次性插入最大 行数
     */
    private final int BATCH_COUNT = 300;

    /**
     * 批量处理修改或者插入
     *
     * @param data        需要被处理的数据
     * @param mapperClass Mybatis的Mapper类
     * @param function    自定义处理逻辑
     * @return int 影响的总行数
     */
    public <T, U, R> int batchUpdateOrInsert(List<T> data, Class<U> mapperClass, BiFunction<List<T>, U, R> function) {
        if (data.size() == 0) {
            log.warn("批插入的数据为空！");
            return 0;
        }
        TransactionStatus status = manager.getTransaction(def);
        int batchCount = BATCH_COUNT;
        SqlSession batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        try {
            U mapper = batchSqlSession.getMapper(mapperClass);
            int size = data.size();
            // 每批最后一个的下标
            int batchLastIndex = batchCount;

            for (int index = 0; index < size; ) {

                if (batchLastIndex >= size) {
                    //一波流
                    batchLastIndex = size;
                    //执行批处理
                    function.apply(data.subList(index, batchLastIndex), mapper);
                    batchSqlSession.commit();
                    batchSqlSession.clearCache();
                    LogHelper.info(log,"batchInsertWpRealOverdueDetail", "index:" + index + " batchLastIndex:" + batchLastIndex);
                    // 数据插入完毕，退出循环
                    break;
                } else {
                    // 多插几次 BATCH_COUNT
                    function.apply(data.subList(index, batchLastIndex), mapper);
                    batchSqlSession.commit();
                    batchSqlSession.clearCache();
                    LogHelper.info(log, "batchInsertWpRealOverdueDetail", "index:" + index + " batchLastIndex:" + batchLastIndex);
                    // 设置下一批下标
                    index = batchLastIndex;
                    batchLastIndex = index + batchCount;
                }
            }
            manager.commit(status);
        } catch (Exception e) {
            log.error("批处理失败！",e);
            manager.rollback(status);
            throw e;
        } finally {
            batchSqlSession.close();
            return data.size();
        }
    }
}

使用

    BatchInsertOrUpdateUtil insertOrUpdateUtil = new BatchInsertOrUpdateUtil(xxxSqlSessionFactory,xxxTransactionManager);        
    insertOrUpdateUtil.batchUpdateOrInsert(xxList, xxMapper.class,(data,mapper)->{
        return mapper.insertEntityByList(data);
    });