前言:公司项目在适配oracle的时候,有一堆批量的增删改语句,是在Mapper的xml里编写的,通过case when语句来判断数据库类型生成不同的批量sql,为了简化开发,但又不想直接引入mybatis-plus,所以参考mybatis-plus的批量操作,编写了一个工具类,用于处理批量操作,只需要写单个的增删改语句即可。
MapperBatchOpUtil
@Slf4j
@Component
public class MapperBatchOpUtil {
/**
* 默认批次提交数量
*/
private static final int DEFAULT_BATCH_SIZE = 1000;
private static SqlSessionTemplate sqlSessionTemplate;
public MapperBatchOpUtil(SqlSessionTemplate sqlSessionTemplate) {
MapperBatchOpUtil.sqlSessionTemplate = sqlSessionTemplate;
}
/**
* 批量执行新增/更新/删除操作
*
* @param mapperClass 实际执行的mapper类class
* @param dataList 待执行的数据列表
* @param callback 具体执行方法
* @param <M> 实际执行的mapper类
* @param <T> 待执行的数据类型
* @return 执行结果
*/
public static <M, T> long batchOp(Class<M> mapperClass, List<T> dataList, DoUpdateCallback<M, T> callback) {
long count = 0;
if (dataList == null || dataList.isEmpty()) {
return count;
}
SqlSessionFactory sqlSessionFactory = sqlSessionTemplate.getSqlSessionFactory();
SqlSessionHolder sqlSessionHolder = (SqlSessionHolder) TransactionSynchronizationManager.getResource(sqlSessionFactory);
boolean transaction = TransactionSynchronizationManager.isSynchronizationActive();
if (sqlSessionHolder != null) {
SqlSession sqlSession = sqlSessionHolder.getSqlSession();
//原生无法支持执行器切换,当存在批量操作时,会嵌套两个session的,优先commit上一个session
//按道理来说,这里的值应该一直为false。
sqlSession.commit(!transaction);
}
try (SqlSession batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
if (!transaction) {
log.warn("BatchSqlSession [" + batchSqlSession + "] was not registered for synchronization because DataSource is not transactional");
}
try {
int size = dataList.size();
int i = 1;
M mapper = batchSqlSession.getMapper(mapperClass);
for (T data : dataList) {
callback.execute(mapper, data);
if ((i % DEFAULT_BATCH_SIZE == 0) || i == size) {
List<BatchResult> batchResults = batchSqlSession.flushStatements();
for (BatchResult batchResult : batchResults) {
for (int updateCount : batchResult.getUpdateCounts()) {
//获取受影响的行数:此处 insert 语句一直返回的是-2;update和delete会返回正确的受影响行数
count += updateCount;
}
}
}
i++;
}
//非事物情况下,强制commit。
batchSqlSession.commit(!transaction);
} catch (Throwable t) {
batchSqlSession.rollback(); // 发生异常时回滚事务
Throwable unwrapped = ExceptionUtil.unwrapThrowable(t);
if (unwrapped instanceof RuntimeException) {
MyBatisExceptionTranslator myBatisExceptionTranslator
= new MyBatisExceptionTranslator(sqlSessionFactory.getConfiguration().getEnvironment().getDataSource(), true);
throw Objects.requireNonNull(myBatisExceptionTranslator.translateExceptionIfPossible((RuntimeException) unwrapped));
}
throw t;
}
}
//如果count < 0,则返回实际数据量
return count < 0 ? dataList.size() : count;
}
}
DoUpdateCallback
public interface DoUpdateCallback<M, T> {
long execute(M mapper, T data);
}
使用:
SysLogRepository 里使用工具类,调用SysLogMapper里的单个save操作
@Repository
public class SysLogRepository {
public boolean batchSave(List<SysLogDO> list) {
return MapperBatchOpUtil.batchOp(SysLogMapper.class, list, SysLogMapper::save) > 0;
}
}
SysLogMapper 中只有单个的save操作
public interface SysLogMapper {
long save(@Param("log") SysLogDO logDO);
}