springBoot+mybatisPlus批量插入,千万数据导入,真实可用

     java开发springBoot+mybatisPlus已经成为主流,最近遇到新旧系统的切换,批量插入读取原来的数据插入到新的系统的表中,使用mybatisPlus的方法

saveBatch(),但是发现巨慢,查询源码和sql发现其实里面也是遍历之后再单条插入,难怪慢呢.其实就是伪批量,可能再事务提交上有处理,但是还是不能容忍.查资料整理出来一套正式的批量插入方法,生产已经使用,实测10W数据.1-3分钟左右可插入,完全可以满足需求,有需要的小伙伴可以参考,简简单单集成到自己的系统,废话不多说,直接上代码:

第一步:创建 EasySqlInjector到spring容器

@Bean
public EasySqlInjector easySqlInjector() {
    return new EasySqlInjector();
}

第二部:创建自定义mapper接口

自定义 mapper接口,扩展BaseMapper,并使用mybatis的真实批量插入方法

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import java.util.Collection;

/**
 * @description:批量插入
 * @author: xxx
 * @create: 2020-12-21 14:27
 **/
public interface EasyBaseMapper<T> extends BaseMapper<T> {
    /**
     * 批量插入
     *
     * @param entityList 实体列表
     * @return 影响行数
     */
    Integer insertBatchSomeColumn(Collection<T> entityList);
}

第三步:创建批量插入的实现类  BaseIServiceImpl

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.atomic.AtomicReference;

/**
 * @description:批量插入的方法
 
 **/
@Service
@Slf4j
public class BaseIServiceImpl<M, T> {

    @Autowired
    @Qualifier(value = "defaultThreadPoolExecutor")
    private ThreadPoolExecutor threadPoolExecutor;


    /**
     * 开启多线程的插入,size单次sql大小
     *
     * @param entityList
     * @param mapper
     * @param size
     * @return
     */
    public Integer insertBatchSomeColumnByThread(List<T> entityList, Class<M> mapper, int size) {
        EasyBaseMapper easyBaseMapper = (EasyBaseMapper) SpringContextUtils.getBean(mapper);
        //返回结果
        AtomicReference<Integer> resultInteger = new AtomicReference<>(0);
        //数据总长度
        int length = entityList.size();
        // 余数
        int r = length % size;
        // 截取次数
        int index = length / size;
        // 如果余数大于0，截取次数+1
        if (r > 0) {
            index = index + 1;
        }
        long startTime = System.currentTimeMillis();
        for (int i = 0; i < index; i++) {
            // 注意下标越界的问题，尤其最后一轮截取
            List<T> subSaveList = null;
            if (r > 0 && i == index - 1) {
                subSaveList = entityList.subList(i * size, length);
            } else {
                subSaveList = entityList.subList(i * size, (i + 1) * size);
            }

            List<T> finalSubSaveList = subSaveList;
            threadPoolExecutor.execute(() -> {
                        log.info("当前执行线程" + Thread.currentThread().getName());
                        Integer integer = easyBaseMapper.insertBatchSomeColumn(finalSubSaveList);
                        resultInteger.set(integer + resultInteger.get());
                    }
            );
        }
        long endTime = System.currentTimeMillis();
        log.info(mapper.getName() + "数据总长度," + length + " 多线程插入用时" + (endTime - startTime) + " ms");
        return resultInteger.get();
    }

    /**
     * 开启多线程的插入,size单次sql大小,默认1000
     *
     * @param entityList
     * @param mapper
     * @return
     */
    public Integer insertBatchSomeColumnByThread(List<T> entityList, Class<M> mapper) {
        return insertBatchSomeColumnByThread(entityList, mapper, 1000);
    }

    /**
     * 批量插入,size单次sql大小,默认1000
     *
     * @param entityList
     * @param mapper
     * @return
     */
    public Integer insertBatchSomeColumn(List<T> entityList, Class<M> mapper) {
        return insertBatchSomeColumn(entityList, mapper, 1000);
    }

    /**
     * 批量插入,size单次sql大小
     *
     * @param entityList
     * @param mapper
     * @param size
     * @return
     */
    public Integer insertBatchSomeColumn(List<T> entityList, Class<M> mapper, int size) {
        EasyBaseMapper easyBaseMapper = (EasyBaseMapper) SpringContextUtils.getBean(mapper);
        //返回结果
        Integer resultInteger = 0;
        //数据总长度
        int length = entityList.size();
        // 余数
        int r = length % size;
        // 截取次数
        int index = length / size;
        // 如果余数大于0，截取次数+1
        if (r > 0) {
            index = index + 1;
        }
        long startTime = System.currentTimeMillis();
        for (int i = 0; i < index; i++) {
            // 注意下标越界的问题，尤其最后一轮截取
            if (r > 0 && i == index - 1) {
                resultInteger += easyBaseMapper.insertBatchSomeColumn(entityList.subList(i * size, length));
            } else {
                resultInteger += easyBaseMapper.insertBatchSomeColumn(entityList.subList(i * size, (i + 1) * size));
            }
        }
        long endTime = System.currentTimeMillis();
        log.info(mapper.getName() + "数据总长度," + length + " 插入用时" + (endTime - startTime) + " ms");
        return resultInteger;
    }
}

SpringContextUtils 工具包 

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

@Component
public class SpringContextUtils implements ApplicationContextAware {
    private static ApplicationContext applicationContext;

    public SpringContextUtils() {
    }

    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        SpringContextUtils.applicationContext = applicationContext;
    }

    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }

    public static Object getBean(String name) {
        return applicationContext.getBean(name);
    }

    public static <T> T getBean(Class<T> requiredType) {
        return applicationContext.getBean(requiredType);
    }

    public static <T> T getBean(String name, Class<T> requiredType) {
        return applicationContext.getBean(name, requiredType);
    }

    public static boolean containsBean(String name) {
        return applicationContext.containsBean(name);
    }

    public static boolean isSingleton(String name) {
        return applicationContext.isSingleton(name);
    }

    public static Class<?> getType(String name) {
        return applicationContext.getType(name);
    }

线程池配置 ThreadPoolExecutor

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.concurrent.LinkedBlockingDeque;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;

@Configuration
public class ThreadPoolExecutorConfig {

  
    @Bean(name = "defaultThreadPoolExecutor")
    public ThreadPoolExecutor threadPoolExecutor() {
        return new ThreadPoolExecutor(20, 40, 30, TimeUnit.SECONDS, new LinkedBlockingDeque<>());
    }

第五步:测试使用

mapper接口集成EasyBaseMapper

注入

@Autowired
private BaseIServiceImpl baseIService;

xxxMapper:自己的mapper接口
baseIService.insertBatchSomeColumnByThread(list, xxxMapper.class);

参数可调整,视自己的单个数据的sql长度确定.一般2000条数据一个sql比较合理,单个sql较长可配置1000,默认1000,如果参数不可能可能报错,sql超长的异常,调整参数即可.