MyBatis BATCH模式

当使用MyBatis时，有两种批量插入的方式，一种是动态SQL中foreach拼接字符串，一种是使用MyBatis提供的BATCH模式。

首先查看一下org.apache.ibatis.session.ExecutorType类

public enum ExecutorType {
  SIMPLE, REUSE, BATCH
}

查看官方文档：https://mybatis.org/mybatis-3/configuration.html 关于ExecutorType的说明：

SIMPLE ：Configures the default executor. 使用默认执行器

REUSE ：reuses prepared statements 重用预编译语句 减少编译次数

BATCH ：reuses statements and batches updates. 重用预编译语句和匹配更新

Spring和MyBatis结合配置Batch方式

配置batch模式：

全局模式：所有的mapper接口使用Batch模式

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
	<property name="basePackage" value="com.xquant.platform.test.mapper" />
	<!-- optional unless there are multiple session factories defined -->
<!-- 		<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" /> -->
	<property name="sqlSessionTemplate">
		<bean class="org.mybatis.spring.SqlSessionTemplate">
			<constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory" />
			<constructor-arg name="executorType" value="BATCH"></constructor-arg>
		</bean>
	</property>
</bean>

单mapper：特定mapper接口使用Batch模式，其他mapper使用默认模式SIMPLE

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
	<property name="basePackage" value="com.xquant.platform.test.mapper" />
	<!-- <property name="annotationClass" value="org.springframework.stereotype.Repository" /> -->
	<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
</bean>


<!-- To change the default ExecutorType, a custom SqlSessionTemplate is all that is needed. -->
<bean id="ttrdExhDealMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
	<property name="mapperInterface" value="com.xquant.platform.test.mapper.TtrdExhDealMapper" />
	<property name="sqlSessionTemplate">
		<bean class="org.mybatis.spring.SqlSessionTemplate">
			<constructor-arg index="0" ref="sqlSessionFactory" />
			<constructor-arg index="1" value="BATCH" />
		</bean>
	</property>
</bean>

源码分析

MyBatis中BATCH模式和SIMPLE模式主要涉及到两个类：

org.apache.ibatis.executor.BatchExecutor

@Override
public int doUpdate(MappedStatement ms, Object parameterObject) throws SQLException {
	final Configuration configuration = ms.getConfiguration();
	final StatementHandler handler = configuration.newStatementHandler(this, ms, parameterObject, RowBounds.DEFAULT,
			null, null);
	final BoundSql boundSql = handler.getBoundSql();
	final String sql = boundSql.getSql();
    // 执行预编译
	final Statement stmt;
	if (sql.equals(currentSql) && ms.equals(currentStatement)) {
		int last = statementList.size() - 1;
        // 直接获取上一个预编译好的语句
		stmt = statementList.get(last);
		applyTransactionTimeout(stmt);
		handler.parameterize(stmt);// fix Issues 322
		BatchResult batchResult = batchResultList.get(last);
		batchResult.addParameterObject(parameterObject);
	} else {
		Connection connection = getConnection(ms.getStatementLog());
		stmt = handler.prepare(connection, transaction.getTimeout());
		handler.parameterize(stmt); // fix Issues 322
		currentSql = sql;
		currentStatement = ms;
		statementList.add(stmt);
		batchResultList.add(new BatchResult(ms, sql, parameterObject));
	}
	// handler.parameterize(stmt);
	handler.batch(stmt);
	return BATCH_UPDATE_RETURN_VALUE;
}

org.apache.ibatis.executor.SimpleExecutor

@Override
public int doUpdate(MappedStatement ms, Object parameter) throws SQLException {
	Statement stmt = null;
	try {
		Configuration configuration = ms.getConfiguration();
		StatementHandler handler = configuration.newStatementHandler(this, ms, parameter, RowBounds.DEFAULT, null,
				null);
        // 执行预编译
		stmt = prepareStatement(handler, ms.getStatementLog());
		return handler.update(stmt);
	} finally {
		closeStatement(stmt);
	}
}

private Statement prepareStatement(StatementHandler handler, Log statementLog) throws SQLException {
	Statement stmt;
	Connection connection = getConnection(statementLog);   // 获取连接
	stmt = handler.prepare(connection, transaction.getTimeout()); // 预编译
	handler.parameterize(stmt);  // 设置参数
	return stmt;
}

BatchExecutor与SimpleExecutor不同之处：

1. batch模式减少重复预编译的次数（优化预编译）
2. 执行数据库操作的接口不同，前者是batch，后者是update

在默认情况下，handler为PreparedStatementHandler类实例，最终都是调用jdk中的方法

@Override
public int update(Statement statement) throws SQLException {
	PreparedStatement ps = (PreparedStatement) statement;
    // 执行数据库操作
	ps.execute();
	int rows = ps.getUpdateCount();
	Object parameterObject = boundSql.getParameterObject();
	KeyGenerator keyGenerator = mappedStatement.getKeyGenerator();
	keyGenerator.processAfter(executor, mappedStatement, ps, parameterObject);
	return rows;
}

@Override
public void batch(Statement statement) throws SQLException {
	PreparedStatement ps = (PreparedStatement) statement;
    // 执行数据库操作
	ps.addBatch();
}

JDK中batch模式

jdk调用batch的用法

//记录1
statement.setInt(1, 1); 
statement.setString(2, "Cujo"); 
statement.addBatch(); 
//记录2
statement.setInt(1, 2); 
statement.setString(2, "Fred"); 
statement.addBatch(); 
//记录3
statement.setInt(1, 3); 
statement.setString(2, "Mark"); 
statement.addBatch(); 
int [] counts = statement.executeBatch(); 
connection.commit();

Batch与事务

当Spring与MyBatis结合并使用事务的时候，无论使用哪种ExecutorType，对外面都无影响。但是在同一个事务当中不能存在两种类型的ExecutorType。在SqlSessionUtils类源码中有如下代码：

private static SqlSession sessionHolder(ExecutorType executorType, SqlSessionHolder holder) {
	SqlSession session = null;
	if (holder != null && holder.isSynchronizedWithTransaction()) {
		if (holder.getExecutorType() != executorType) {
			throw new TransientDataAccessResourceException(
					"Cannot change the ExecutorType when there is an existing transaction");
		}

		holder.requested();

		if (LOGGER.isDebugEnabled()) {
			LOGGER.debug("Fetched SqlSession [" + holder.getSqlSession() + "] from current transaction");
		}

		session = holder.getSqlSession();
	}
	return session;
}

因此对于MyBatis中Batch模式有如下两种限制：

1. 不能在同一个事务中使用不同的模式，对于配置ExecutorType要特别注意
2. 比较batch模式与foreach动态sql方式，就发现batch无法细粒化到mapper的方法级别