多线程批量数据入库

在一个项目中需要Excel批量导入数据，然后就写了一个测试程序。
这次测试的数据是九千条数据最快7秒左右可以全部入库，用的是Mysql数据库v5.6，Mybatis框架。我试验了下，不用多线程需要47秒。

用 Executor 创建线程池

@Configuration
@EnableAsync
public class ExecutorConfig {

    @Bean
    public Executor asyncServiceExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        //配置核心线程数
        executor.setCorePoolSize(10);
        //配置最大线程数
        executor.setMaxPoolSize(10);
        //配置队列大小
        executor.setQueueCapacity(99999);
        //配置线程池中的线程的名称前缀
        executor.setThreadNamePrefix("async-service-remit-");
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        //执行初始化
        executor.initialize();
        return executor;
    }
}

使用多线程插入数据库

@Async("asyncServiceExecutor")
    @Override
    public void staffIntoStore(String filePath,Long companyUuid) {
        ExcelHandle handle = new ExcelHandle();
        try {
            File file = new File(filePath);
            List<String> list = new ArrayList<>();
            list.add("name");
            list.add("idcard");
            // 从excel表中获取数据
            List<Map<String,Object>> data = handle.getListValue("D:\\test.xlsx",list,0,file);
            int dataSize = data.size();
            // 这个是记录插入数据的进度
            staffEnteringProcessCache.clearProgress(companyUuid);
            staffEnteringProcessCache.setTotal(companyUuid,dataSize);
            // 每次插入50条数据
            int step = 50;
            int totalTasks = (dataSize % step == 0 ? dataSize/step : (dataSize/step + 1));
            final CountDownLatch countDownLatch = new CountDownLatch(totalTasks);
            long startTime1 = System.currentTimeMillis();
            for(int j = 0; j < dataSize; j=j+step){
                final int start = j;
                final int perCount = (dataSize - start) < step ? (dataSize - start) : step;
                asyncServiceExecutor.execute(new Runnable() {
                    public void run() {
                        try {
                            System.out.println("多线程开始: start == " + start + " , 多线程个数count" + perCount);
                            int count = staffMapper.insertBatch(data.subList(start,perCount+start));
                            System.out.println(staffEnteringProcessCache.incre(companyUuid,count));
                            countDownLatch.countDown();
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                });
            }
            try {
                countDownLatch.await();
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            System.out.println("线程池循环耗时=======" + (System.currentTimeMillis() - startTime1));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

打印日志.jpg

第一次写文章，写的不好的地方，多多见谅，觉得有点帮助，请多多点赞，谢谢 ^_