Mybatis优化：使用batch模式实现批量插入

目录

Mybatis原理简介

SqlSessionFactoryBuilder

SqlSessionFactory

SqlSession

Mybatis开启Batch模式

Batch模式效率高的原因

参考文档

Mybatis中有两种批量插入的方式：

1. 动态SQL使用foreach标签进行批量插入
2. 使用Mybatis的batch模式进行批量插入

相比之下，数据量较大的场景中后者效率更高

Mybatis原理简介

       每个基于 MyBatis 的应用都是以一个 SqlSessionFactory 的实例为核心的。SqlSessionFactory 的实例可以通过 SqlSessionFactoryBuilder 获得。而 SqlSessionFactoryBuilder 则可以从 XML 配置文件或一个预先配置的 Configuration 实例来构建出 SqlSessionFactory 实例。

//使用SqlSessionFactoryBuilder创建SqlSessionFactory
String resource = "org/mybatis/example/mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//使用SqlSessionTemplate创建SqlSessionFactory
@Autowired
private SqlSessionTemplate sqlSessionTemplate;

SqlSessionFactory sqlSessionFactory = sqlSessionTemplate.getSqlSessionFactory();

SqlSessionFactoryBuilder

        SqlSessionFactoryBuilder可以理解为SqlSessionFactory的建造者，其作用是用来创建SqlSessionFactory，需要注意的是SqlSessionFactoryBuilder不是线程安全的。在spring项目中我们可以使用SqlSessionTemplate来创建SqlSessionFactory，SqlSessionTemplate是线程安全的。

SqlSessionFactory

        SqlSessionFactory用来创建SqlSession实例。SqlSessionFactory 一旦被创建就应该在应用的运行期间一直存在，没有任何理由丢弃它或重新创建另一个实例。 使用 SqlSessionFactory 的最佳实践是在应用运行期间不要重复创建多次，多次重建 SqlSessionFactory 被视为一种代码“坏习惯”。因此 SqlSessionFactory 的最佳作用域是应用作用域。 有很多方法可以做到，最简单的就是使用单例模式或者静态单例模式。

SqlSession

        SqlSession是mybatis的顶层API，其作用是访问数据库，完成增删改查以及事务的提交和回滚等操作。SqlSession实例不是线程安全的，不能被多个线程共享。

Mybatis开启Batch模式

Mybatis在枚举中提供了三种类型的执行器

package org.apache.ibatis.session;


public enum ExecutorType {
  SIMPLE, //mybatis的默认执行器，它为每个语句的执行创建一个新的预处理语句
  REUSE,  //会复用预处理语句
  BATCH   //会批量执行所有更新语句，不需要对同样的SQL进行多次预编译
}

从org.apache.ibatis.session.Configuration类中可以看到Mybatis模式是simple模式

使用SqlSessionFactory创建一个Batch类型的SqlSession实例

SqlSessionFactory sqlSessionFactory = sqlSessionTemplate.getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);

SqlSessionFactory中有多个创建SqlSession实例的方法，我们可以根据自己的实际需求调用不同的方法

package org.apache.ibatis.session;

import java.sql.Connection;


public interface SqlSessionFactory {

  SqlSession openSession();

  SqlSession openSession(boolean autoCommit);

  SqlSession openSession(Connection connection);

  SqlSession openSession(TransactionIsolationLevel level);

  SqlSession openSession(ExecutorType execType);

  SqlSession openSession(ExecutorType execType, boolean autoCommit);

  SqlSession openSession(ExecutorType execType, TransactionIsolationLevel level);

  SqlSession openSession(ExecutorType execType, Connection connection);

  Configuration getConfiguration();

}

SqlSessionFactory有两个实现类：DefaultSqlSessionFactory和SqlSessionManager

  最终在DefaultSqlSessionFactory.openSessionFromDataSource()方法中生成Mybatis的执行器

  private SqlSession openSessionFromDataSource(ExecutorType execType, TransactionIsolationLevel level, boolean autoCommit) {
    Transaction tx = null;
    try {
      final Environment environment = configuration.getEnvironment();
      final TransactionFactory transactionFactory = getTransactionFactoryFromEnvironment(environment);
      tx = transactionFactory.newTransaction(environment.getDataSource(), level, autoCommit);
      //创建Executor   
      final Executor executor = configuration.newExecutor(tx, execType);
      return new DefaultSqlSession(configuration, executor, autoCommit);
    } catch (Exception e) {
      closeTransaction(tx); // may have fetched a connection so lets call close()
      throw ExceptionFactory.wrapException("Error opening session.  Cause: " + e, e);
    } finally {
      ErrorContext.instance().reset();
    }
  }

 org.apache.ibatis.session.Configuration.newExecutor()

  public Executor newExecutor(Transaction transaction, ExecutorType executorType) {
    executorType = executorType == null ? defaultExecutorType : executorType;
    executorType = executorType == null ? ExecutorType.SIMPLE : executorType;
    Executor executor;
    //判断，如果是BATCH模式，则创建BatchExecutor
    if (ExecutorType.BATCH == executorType) {
      executor = new BatchExecutor(this, transaction);
    } else if (ExecutorType.REUSE == executorType) {
      executor = new ReuseExecutor(this, transaction);
    } else {
      executor = new SimpleExecutor(this, transaction);
    }
    if (cacheEnabled) {
      executor = new CachingExecutor(executor);
    }
    executor = (Executor) interceptorChain.pluginAll(executor);
    return executor;
  }

Batch模式效率高的原因

        Batch中减少了SQL预编译的次数，单次插入数据量特别大，字段特别多的情况下更适合使用Mybatis的Batch模式。

BatchExecutor.doUpdate()

  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;
    //判断如果当前SQL与上一次传入的SQL一样则不会创建新的Statement
    if (sql.equals(currentSql) && ms.equals(currentStatement)) {
      int last = statementList.size() - 1;
      //直接取上次的Statement
      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.batch(stmt);
    return BATCH_UPDATE_RETURN_VALUE;
  }

SimpleExecutor.doUpdate()

  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);
      //创建新的Statement
      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;
  }

参考文档

MyBatis中文网

mybatis-spring –