在一个项目中需要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
第一次写文章，写的不好的地方，多多见谅，觉得有点帮助，请多多点赞，谢谢 _
