JdbcTemplate大数据批量插入 多线程异步操作
配置线程池任务执行器@Configurationpublic class ThreadConfig {@Beanpublic ThreadPoolTaskExecutor executor(){ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();//配置核心线程数executor.setCorePoolSize(15);//
·
配置线程池任务执行器
@Configuration
public class ThreadConfig {
@Bean
public ThreadPoolTaskExecutor executor(){
ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
//配置核心线程数
executor.setCorePoolSize(15);
//配置最大线程数
executor.setMaxPoolSize(30);
//配置队列大小
executor.setQueueCapacity(1000);
//线程的名称前缀
executor.setThreadNamePrefix("Executor-");
//等待所有任务结束后再关闭线程池
executor.setWaitForTasksToCompleteOnShutdown(true);
//执行初始化
executor.initialize();
return executor;
}
}
## 单线程 批量插入4w条
StopWatch stopWatch = new StopWatch();
stopWatch.start("方式二:batchUpdate(String sql, List<Object[]> batchArgs)");
List<Object[]> mainList = new ArrayList<>();
for(int i =0;i<40000;i++){
Object[] object = new Object[]{UUID.randomUUID().toString(),"mainList",15};
mainList.add(object);
}
jdbcTemplate.batchUpdate("insert into test_jdbc_batch(ID, NAME, AGE) VALUES (?,?,?)",mainList);
stopWatch.stop();
多线程 异步插入4w条
stopWatch.start("方式二:batchUpdate(String sql, List<Object[]> batchArgs) 多线程版本");
List<Object[]> oneList = new ArrayList<>();
for(int i =0;i<40000;i++){
Object[] object = new Object[]{UUID.randomUUID().toString(),"oneList",20};
oneList.add(object);
}
executor.execute(()->executorbatchUpdate(jdbcTemplate,oneList));
stopWatch.stop();
System.out.println(stopWatch.prettyPrint());
//查看子线程情况
Thread.sleep(60*1000);
private void executorbatchUpdate(JdbcTemplate jdbcTemplate,List<Object[]> list) {
StopWatch stopWatch = new StopWatch();
stopWatch.start("子线程-" + Thread.currentThread().getId() + "执行写入操作");
jdbcTemplate.batchUpdate("insert into test_jdbc_batch(ID, NAME, AGE) VALUES (?,?,?)",list);
stopWatch.stop();
System.out.println(stopWatch.prettyPrint());
}
如果数据达到100w,前端一般就会超时,这时采用异步方式,还可以使用在多个线程操作不同的表,提高批量效率。
注:多个线程同时插入一张表,效率基本没有很大提升,数据库本身对同一张表写入本身也是需要锁的,需要排队的
更多推荐
已为社区贡献1条内容
所有评论(0)