文章目录
for循环执行sql
很慢,不推荐
foreach动态拼接sql来插入
通用的拼接方式(mysql和oracle都适用)
mysql和oracle都适用
<insert id="batchInsert" parameterType="java.util.List">
INSERT INTO T_FUND_OPERATE_LOG
(CHANGE_RECORD_ID , CHANGE_RECORD_GROUP_ID)
<foreach collection="list" item="item" separator="UNION ALL">
SELECT #{item.changeRecordId,jdbcType=VARCHAR},
#{item.changeRecordGroupId,jdbcType=VARCHAR}
FROM DUAL
</foreach>
</insert>
只适用于mysql的语法
INSERT INTO TABLE () VALUE (),()
<insert id="batchInsert">
INSERT INTO T_SYS_GROUP (GROUP_ID, GROUP_NAME)
VALUES
<foreach collection="sysGroups" item="item" separator=",">
(#{item.groupId,jdbcType=VARCHAR},
#{item.groupName,jdbcType=VARCHAR})
</foreach>
</insert>
只适用于oracle的语法
INSERT ALL INTO TABLE () VALUE () INTO TABLE () VALUE () SELECT 1 FROM DUAL
<insert id="batchInsert" parameterType="java.util.List">
INSERT ALL
<foreach collection="list" item="item" separator=" ">
INTO T_I18N_DYNAMIC
(I18N_DYNAMIC_ID, DATA_ID)
VALUE (
#{item.id,jdbcType=VARCHAR},
#{item.dataId,jdbcType=VARCHAR}
)
</foreach>
SELECT 1 FROM DUAL
</insert>
直接多条sql拼接(插入、更新、删除)
如果是mysql数据库,需要在Mysql连接字符串里添加allowMultiQueries=true
mysql
<update id="batchUpdate">
<foreach collection="list" item="item" separator=";">
UPDATE T_I18N_DYNAMIC SET
DATA_ID = #{item.dataId, jdbcType=VARCHAR}
where I18N_DYNAMIC_ID = #{item.id, jdbcType=VARCHAR}
</foreach>
</update>
oracle
<update id="batchUpdate" parameterType="java.util.List">
begin
<foreach collection="list" item="item" separator=";">
UPDATE T_I18N_DYNAMIC SET
DATA_ID = #{item.dataId, jdbcType=VARCHAR}
where I18N_DYNAMIC_ID = #{item.id, jdbcType=VARCHAR}
</foreach>
;end;
</update>
mybatis批处理
mysql和oracle都适用
如果是mysql数据库,需要在mysql连接字符串里添加rewriteBatchedStatements=true
批处理工具类
import org.apache.commons.collections.CollectionUtils;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionHolder;
import org.springframework.transaction.support.TransactionSynchronizationManager;
import org.springframework.util.Assert;
import java.util.Collection;
import java.util.function.BiConsumer;
/**
* Mybatis批处理执行sql的工具类
* <pre>
* 参考自com.baomidou.mybatisplus.extension.toolkit.SqlHelper#executeBatch
* 使用该工具类的前提是:
* 如果是Mysql环境:
* 需要在连接字符串里加入&rewriteBatchedStatements=true
* </pre>
*
* @author 肖浩然
* @version fssc1.0
* @since 3.4.0
*/
public class SqlBatchUtils {
/**
* 默认批量执行的数量
*/
public static final int BATCH_SIZE = 999;
/**
* sqlSessionFactory
*/
private static final SqlSessionFactory sqlSessionFactory =
ApplicationContextAwareUtils.getBean(SqlSessionFactory.class);
/**
* 执行批量操作,默认每批次执行{@value BATCH_SIZE}条
*
* @param list 待执行的整个数据集合
* @param mapperMethod 单条sql执行的具体实现
* @param <Entity> 待执行的DO对象的类型
* @return 成功条数
* @see SqlBatchUtils#executeBatch(Collection, int, BiConsumer)
* @since 2022/1/21 17:40
*/
public static <Entity> int executeBatch(
Collection<Entity> list, BiConsumer<SqlSession, Entity> mapperMethod) {
return executeBatch(list, BATCH_SIZE, mapperMethod);
}
/**
* 执行批量操作
*
* @param list 待执行的整个数据集合
* @param batchSize 批次大小
* @param mapperMethod 单条sql执行的具体实现
* <pre>
* 如:{@code (batchSqlSession,recordDO) ->
* batchSqlSession.getMapper(IRecordDAO.class).saveRecord(recordDO)}
* </pre>
* @param <Entity> 待执行的DO对象类型
* @return 成功条数
* @since 3.4.0
*/
public static <Entity> int executeBatch(
Collection<Entity> list, int batchSize, BiConsumer<SqlSession, Entity> mapperMethod) {
Assert.isTrue(batchSize > 0, "批次大小必须大于0");
if (CollectionUtils.isEmpty(list)) {
return 0;
}
SqlSessionHolder sqlSessionHolder =
(SqlSessionHolder) TransactionSynchronizationManager.getResource(sqlSessionFactory);
boolean transaction = TransactionSynchronizationManager.isSynchronizationActive();
if (sqlSessionHolder != null) {
SqlSession sqlSession = sqlSessionHolder.getSqlSession();
//原生无法支持执行器切换,当存在批量操作时,会嵌套两个session的,优先commit上一个session
//按道理来说,这里的值应该一直为false。
//如果外层有spring事务,这里不真正提交事务,而是交由spring管理
sqlSession.commit(!transaction);
}
//获取能够批量执行的SqlSession
SqlSession batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
int size = list.size();
int i = 1;
for (Entity element : list) {
mapperMethod.accept(batchSqlSession, element);
if ((i % batchSize == 0) || i == size) {
batchSqlSession.flushStatements();
}
i++;
}
//非事务情况下,强制commit
//如果外层有spring事务,这里不真正提交事务,而是交由spring管理
batchSqlSession.commit(!transaction);
} catch (Throwable t) {
//如果外层有spring事务,这里不真正回滚事务,而是交由spring管理
batchSqlSession.rollback();
throw new RuntimeException(t);
} finally {
batchSqlSession.close();
}
return list.size();
}
}
使用例子
一定要在在批量操作的方法上加上事务!
class Test{
@Transactional(rollbackFor = Exception.class)
public int saveVoucherOperationBatch(List<VoucherOperationDO> list) {
int result = SqlBatchUtils.executeBatch(list,
(batchSqlSession, entity) -> {
IVoucherOperationDAO mapper = batchSqlSession.getMapper(IVoucherOperationDAO.class);
mapper.saveVoucherOperation(entity);
}
);
return result;
}
}
性能比较
测试
数据库:mysql
连接url加上rewriteBatchedStatements=true
总数:6660条
每批次执行:999条
批量执行方式 | jdbc批处理 | mybatis批处理 | foreach拼接sql | for循环执行sql |
---|---|---|---|---|
执行时间 | 1300ms | 1377ms | 1800ms | 78423ms |
结论
执行速度:
JDBC批处理 略快于 Mybatis批处理 略快于 Foreach拼接sql 远快于 for循环执行sql