当使用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不同之处:
- batch模式减少重复预编译的次数(优化预编译)
- 执行数据库操作的接口不同,前者是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模式有如下两种限制:
- 不能在同一个事务中使用不同的模式,对于配置ExecutorType要特别注意
- 比较batch模式与foreach动态sql方式,就发现batch无法细粒化到mapper的方法级别