Mybatis-Spring批量插入异常：未调用flush却自动执行

问题现象 #

使用MyBatis实现批量插入时，循环调用batchMap.insert()后，每条插入语句在未手动调用flush或commit的情况下就被即时执行，日志显示每次插入操作后都触发了executeBatch。

相关配置（applicationContext.xml） #

<bean id="refDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  <property name="driverClassName">
    <value>com.mysql.cj.jdbc.Driver</value>
  </property>
  <property name="url">
    <value>jdbc:mysql://localhost:3306/somedatabase?rewriteBatchedStatements=true</value>
  </property>
  <property name="username"><value>root</value></property>
  <property name="password"><value>root</value></property>
</bean>
<bean id="refSqlMapClientIbatis" class="org.mybatis.spring.SqlSessionFactoryBean">
  <property name="configLocation" value="/WEB-INF/jsp/ref/sql-map-config.xml" />
  <property name="dataSource" ref="refDataSource" />
</bean>

<bean id="refSqlSession" class="org.mybatis.spring.SqlSessionTemplate">
  <constructor-arg index="0" ref="refSqlMapClientIbatis" />
</bean>
<bean id="refSqlMapClient" class="mypackage.base.IbatisSqlMapClient">
  <property name="sqlSession" ref="refSqlSession" />
</bean>

<bean id="refBatchSqlSession" class="org.mybatis.spring.SqlSessionTemplate">
  <constructor-arg index="0" ref="refSqlMapClientIbatis" />
  <constructor-arg index="1" value="BATCH" />
</bean>
<bean id="refBatchSqlMapClient" class="mypackage.base.MybatisSqlSession">
  <property name="batchSqlSession" ref="refBatchSqlSession" />
</bean>

<bean id="RefBaseDAO" abstract="true">
  <property name="sqlMap" ref="refSqlMapClient"/>
  <property name="batchMap" ref="refBatchSqlMapClient"/>
</bean>

日志信息 #

java.sql.PreparedStatement.addBatch: INSERT INTO  fz_ref_um_rates
        (um_rate_value, um_id, to_um_id, relation_between_prod_spec, um_rate_product_id, um_rate_product_assigned_spec_id, brch_id, created_by, create_date)
        VALUES
        (2.0, '952', '391', N, null, null, '284', 2, NOW());

[09/12/2022 11:48:52] org.slf4j.jul.JDK14LoggerAdapter.innerNormalizedLoggingCallHandler(JDK14LoggerAdapter.java:156)
 [INFO]: java.sql.Statement.executeBatch: 

[09/12/2022 11:48:52] org.slf4j.jul.JDK14LoggerAdapter.innerNormalizedLoggingCallHandler(JDK14LoggerAdapter.java:156)
 [INFO]: java.sql.PreparedStatement.addBatch: INSERT INTO  fz_ref_um_rates
        (um_rate_value, um_id, to_um_id, relation_between_prod_spec, um_rate_product_id, um_rate_product_assigned_spec_id, brch_id, created_by, create_date)
        VALUES
        (3.0, '952', '339', N, null, null, '284', 2, NOW());

[09/12/2022 11:48:52] org.slf4j.jul.JDK14LoggerAdapter.innerNormalizedLoggingCallHandler(JDK14LoggerAdapter.java:156)
 [INFO]: java.sql.Statement.executeBatch: 

[09/12/2022 11:48:52] org.slf4j.jul.JDK14LoggerAdapter.innerNormalizedLoggingCallHandler(JDK14LoggerAdapter.java:156)
 [INFO]: java.sql.PreparedStatement.addBatch: INSERT INTO  fz_ref_um_rates
        (um_rate_value, um_id, to_um_id, relation_between_prod_spec, um_rate_product_id, um_rate_product_assigned_spec_id, brch_id, created_by, create_date)
        VALUES
        (45.0, '952', '338', N, null, null, '284', 2, NOW());

[09/12/2022 11:48:52] org.slf4j.jul.JDK14LoggerAdapter.innerNormalizedLoggingCallHandler(JDK14LoggerAdapter.java:156)
 [INFO]: java.sql.Statement.executeBatch: 

自定义MybatisSqlSession类代码 #

public class MybatisSqlSession {
    private SqlSession batchSqlSession = null;

    public Object insert(String string, Object object) throws FZDAOException {
        try {
            System.out.println("insert called");
            return batchSqlSession.insert(string, object);

        } catch (SqlSessionException ex) {
            throw new MyException(ex.getMessage(), ex);
        }
    }
}

批量插入调用代码 #

public void insertUmRates(List<UmRatesVO> addList) throws FZDAOException
{
    for(UmRatesVO umRatesVO : addList)
    {
        batchMap.insert("UmRatesQueries.insertUmRates", umRatesVO);
    }
}

解决方案 #

1. 手动触发批处理执行与提交 #

MyBATS的BATCH模式下，insert()仅将语句加入队列，但如果未在事务管理范围内，可能会自动执行。需在循环结束后手动调用flush和commit：

方式一：修改调用代码

public void insertUmRates(List<UmRatesVO> addList) throws FZDAOException
{
    try {
        for(UmRatesVO umRatesVO : addList)
        {
            batchMap.insert("UmRatesQueries.insertUmRates", umRatesVO);
        }
        // 手动触发批处理执行
        ((MybatisSqlSession) batchMap).getBatchSqlSession().flushStatements();
        // 提交事务
        ((MybatisSqlSession) batchMap).getBatchSqlSession().commit();
    } catch (Exception e) {
        ((MybatisSqlSession) batchMap).getBatchSqlSession().rollback();
        throw new FZDAOException(e.getMessage(), e);
    }
}

方式二：扩展MybatisSqlSession类

在MybatisSqlSession中添加flush、commit、rollback方法：

public void flush() {
    batchSqlSession.flushStatements();
}

public void commit() {
    batchSqlSession.commit();
}

public void rollback() {
    batchSqlSession.rollback();
}

// 新增getter方法
public SqlSession getBatchSqlSession() {
    return batchSqlSession;
}

调用时：

public void insertUmRates(List<UmRatesVO> addList) throws FZDAOException
{
    try {
        for(UmRatesVO umRatesVO : addList)
        {
            batchMap.insert("UmRatesQueries.insertUmRates", umRatesVO);
        }
        batchMap.flush();
        batchMap.commit();
    } catch (Exception e) {
        batchMap.rollback();
        throw new FZDAOException(e.getMessage(), e);
    }
}

2. 配置事务管理 #

确保批量插入方法被Spring事务管控（添加@Transactional注解），事务会统一控制提交时机，避免单条语句自动执行。

3. 禁用MyBatis自动提交 #

在sql-map-config.xml中确认关闭自动提交：

<settings>
    <setting name="autoCommit" value="false"/>
</settings>

4. 使用MyBatis原生批量插入语法（更优方案） #

直接通过foreach标签实现单条SQL批量插入，性能更高且避免批处理模式的自动执行问题：

Mapper XML配置

<insert id="insertUmRatesBatch" parameterType="java.util.List">
    INSERT INTO fz_ref_um_rates
    (um_rate_value, um_id, to_um_id, relation_between_prod_spec, um_rate_product_id, um_rate_product_assigned_spec_id, brch_id, created_by, create_date)
    VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.umRateValue}, #{item.umId}, #{item.toUmId}, #{item.relationBetweenProdSpec}, #{item.umRateProductId}, #{item.umRateProductAssignedSpecId}, #{item.brchId}, #{item.createdBy}, NOW())
    </foreach>
</insert>

调用代码

public void insertUmRates(List<UmRatesVO> addList) throws FZDAOException
{
    try {
        batchMap.insert("UmRatesQueries.insertUmRatesBatch", addList);
        batchMap.flush();
        batchMap.commit();
    } catch (Exception e) {
        batchMap.rollback();
        throw new FZDAOException(e.getMessage(), e);
    }
}

内容的提问来源于stack exchange，提问作者Dany Allaw