思路:
(1)使用JDBC的batchUpdate实现批量更新。
(2)对原集合进行指定数量的切割处理(例如:原集合有10万数据量,切割数据量是一万,需切割十次),切割方法参考java.util.List的subList方法。
(3)使用Java线程池执行操作。
1. batchUpdate批量处理
1.1 引入JDBC的maven依赖
点击查看代码
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
1.2使用JdbcTemplate 操作
点击查看代码
public void batchUpdate(List<User> entities) {
String sql = "UPDATE User SET cloum1 = ?, cloum2 = ?, " +
"time = now() WHERE id = ?";
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
User entity = entities.get(i);
ps.setString(1, entity.getCloum1);
ps.setString(2, entity.getCloum2);
ps.setInt(3, entity.getId());
}
@Override
public int getBatchSize() {
return entities.size();
}
});
}
2. 分批次处理
示例如下:
点击查看代码
public static final int BATCH_SIZE = 1000;
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private AsyncConfig asyncConfig;
public void updateQaInBatches(List<User> repeatList) {
int size = repeatList.size();
int times = (size + BATCH_SIZE - 1) / BATCH_SIZE; // 计算需要循环的次数,向上取整
for (int i = 0; i < times; i++) {
int fromIndex = i * BATCH_SIZE;
int toIndex = Math.min(size, (i + 1) * BATCH_SIZE);
List<User> batchList = repeatList.subList(fromIndex, toIndex);
asyncConfig.getAsyncExecutor().execute(new Thread(new Runnable(){
@Override
public void run() {
// 执行批量更新
batchUpdate(batchList);
}
}));
}
}
3. Java线程池
示例如下:
点击查看代码
@Configuration
@EnableAsync
public class AsyncConfig implements AsyncConfigurer {
@Override
public Executor getAsyncExecutor() {
ThreadPoolTaskExecutor taskExecutor = new ThreadPoolTaskExecutor();
taskExecutor.setCorePoolSize(8);
taskExecutor.setMaxPoolSize(16);
taskExecutor.setQueueCapacity(1024);
taskExecutor.initialize();
return taskExecutor;
}
}