调用方法工具类
用到了google的guava包来切分集合,效率很高,依赖如下
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
</dependency>
/**
* 版权: taylor
* 描述: 优化批量操作
* 创建时间:2020年08月26日
*/
package com.taylor.common.batch;
import com.google.common.collect.Lists;
import org.apache.ibatis.executor.BatchResult;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.boot.autoconfigure.MybatisAutoConfiguration;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.context.annotation.DependsOn;
import org.springframework.stereotype.Component;
import javax.annotation.PostConstruct;
import java.util.List;
/**
* 〈功能简述〉优化批量操作工具类--需要调用方开启事务才能保证一致性
*
* @author Taylor
* @version [V1.0, 2020年08月26日]
* @since [产品/模块版本]
*/
@ConditionalOnClass({
MybatisAutoConfiguration.class})
@DependsOn("sqlSessionFactory")
@Component
public class BatchSqlSessionUtil {
@Autowired
private SqlSessionFactory sqlSessionFactory;
private static SqlSessionFactory SQL_SESSION_FACTORY;
@PostConstruct
private void construct() {
BatchSqlSessionUtil.setSessionFactory(sqlSessionFactory);
}
/**
* 解决当执行批量新增/批量更新时会触发SQLSERVER的最大参数限制(2100)的问题
* 目前使用到批量插入/批量新增的方法中入参都为LIST集合,故此方法会将LIST按等份切割后遍历执行,如SIZE=100 batchSize=50 则会分割为50 50 执行两次
*
*
* {@see https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver15}
*
* @param statement MYBATIS-STATEMENT {@see org.apache.ibatis.session.SqlSession} 需为批量插入sql,见后续示例
* @param listT LIST[T]
* @param batchSize 每次影响的记录条数,以30-50条为佳(sqlserver需保证 每条记录入参*batchSize<2100) {@see https://blog.csdn.net/huanghanqian/article/details/83177178}
* @param <T> 业务实体
* @return resultCount
*/
public static <T> int batchInsert(String statement, List<T> listT,final int batchSize) {
List<List<T>> list = Lists.partition(listT, batchSize);
int resultSize = 0;
try (SqlSession sqlSession = SQL_SESSION_FACTORY.openSession(ExecutorType.BATCH, false)) {
int size = list.size();
for (int i = 0; i < size;) {
sqlSession.insert(statement, list.get(i));
i++;
resultSize = flush(resultSize, sqlSession);
}
sqlSession.commit();
}
return resultSize;
}
/**
* 解决当执行批量新增/批量更新时会触发SQLSERVER的最大参数限制(2100)的问题
* 目前使用到批量插入/批量新增的方法中入参都为LIST集合,故此方法会将LIST按等份切割后遍历执行,如SIZE=100 batchSize=50 则会分割为50 50 执行两次
*
*
* {@see https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver15}
*
* @param statement MYBATIS-STATEMENT {@see org.apache.ibatis.session.SqlSession} 需为批量修改sql,见后续示例
* @param listT LIST[T]
* @param batchSize 每次刷新影响的记录条数,以30-50条为佳 {@see https://blog.csdn.net/huanghanqian/article/details/83177178}
* @param <T> 业务实体
* @return resultCount
*/
public static <T> int batchUpdate(String statement, List<T> listT,final int batchSize) {
List<List<T>> list = Lists.partition(listT, batchSize);
int resultSize = 0;
try (SqlSession sqlSession = SQL_SESSION_FACTORY.openSession(ExecutorType.BATCH, false)) {
int size = list.size();
for (int