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

敲代码的哈士奇

已于 2023-08-11 10:18:18 修改

阅读量1.4w

点赞数 6

分类专栏：数据库文章标签： mybatis

于 2022-07-28 23:00:53 首次发布

本文链接：https://blog.csdn.net/manba_out/article/details/126023101

版权

数据库专栏收录该内容

2 篇文章 1 订阅

订阅专栏

demo

工具类

Mybatis原理简介

SqlSessionFactoryBuilder

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

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

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

demo

public void demo(){
    SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
    try {
        for (Dept dept : deptList) {
            baseManager.insert(dept);
        }
        session.commit();
    }catch (Exception e){
        session.rollback();
    }finally {
        session.close();
    }
}

工具类

package com.utils.sql;

import com.utils.SpringUtils;
import java.util.List;
import java.util.function.BiConsumer;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

/**
 * @author yms
 * @description:  Mybatis 批量insert、update工具类
 * @date: 2023/7/3 17:21
 */
@Slf4j
public class BatchDmlUtil {

  /**
   * 批量新增方法
   *
   * @param list       要新增的集合
   * @param clazz      Mapper类
   * @param biConsumer 对应的单条新增方法
   * @param <M>        mapper类型
   * @param <T>        结合元素类型
   */
  public static  <M, T> void batchOperate(List<T> list, Class<M> clazz, BiConsumer<M, T> biConsumer) {
    if (list == null || list.size() == 0) {
      log.info("BatchDmlUtil batchInsert list data is null!");
      return;
    }
    SqlSessionFactory sqlSessionFactory = SpringUtils.getBean(SqlSessionFactory.class);
    SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
    try {
      M mapper = session.getMapper(clazz);
      list.forEach(a -> {
        biConsumer.accept(mapper, a);
      });
      session.commit();
      session.clearCache();
    } catch (Exception e) {
      e.printStackTrace();
      log.error("BatchDmlUtil batchInsert is exception！clazz={}", clazz.getName(), e);
      session.rollback();
    } finally {
      session.close();
    }
  }

}


package com.utils;

import org.springframework.aop.framework.AopContext;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.NoSuchBeanDefinitionException;
import org.springframework.beans.factory.config.BeanFactoryPostProcessor;
import org.springframework.beans.factory.config.ConfigurableListableBeanFactory;
import org.springframework.stereotype.Component;

/**
 * spring工具类 方便在非spring管理环境中获取bean
 */
@Component
public final class SpringUtils implements BeanFactoryPostProcessor {

    /** Spring应用上下文环境 */
    private static ConfigurableListableBeanFactory beanFactory;

    @Override
    public void postProcessBeanFactory(ConfigurableListableBeanFactory beanFactory) throws BeansException {
        SpringUtils.beanFactory = beanFactory;
    }

    /**
     * 获取对象
     *
     * @param name
     * @return Object 一个以所给名字注册的bean的实例
     * @throws org.springframework.beans.BeansException
     */
    @SuppressWarnings("unchecked")
    public static <T> T getBean(String name) throws BeansException {
        return (T) beanFactory.getBean(name);
    }

    /**
     * 获取类型为requiredType的对象
     *
     * @param clz
     * @return
     * @throws org.springframework.beans.BeansException
     */
    public static <T> T getBean(Class<T> clz) throws BeansException {
        T result = (T) beanFactory.getBean(clz);
        return result;
    }

    /**
     * 如果BeanFactory包含一个与所给名称匹配的bean定义，则返回true
     *
     * @param name
     * @return boolean
     */
    public static boolean containsBean(String name) {
        return beanFactory.containsBean(name);
    }

    /**
     * 判断以给定名字注册的bean定义是一个singleton还是一个prototype。
     * 如果与给定名字相应的bean定义没有被找到，将会抛出一个异常（NoSuchBeanDefinitionException）
     *
     * @param name
     * @return boolean
     * @throws org.springframework.beans.factory.NoSuchBeanDefinitionException
     */
    public static boolean isSingleton(String name) throws NoSuchBeanDefinitionException {
        return beanFactory.isSingleton(name);
    }

    /**
     * @param name
     * @return Class 注册对象的类型
     * @throws org.springframework.beans.factory.NoSuchBeanDefinitionException
     */
    public static Class<?> getType(String name) throws NoSuchBeanDefinitionException {
        return beanFactory.getType(name);
    }

    /**
     * 如果给定的bean名字在bean定义中有别名，则返回这些别名
     *
     * @param name
     * @return
     * @throws org.springframework.beans.factory.NoSuchBeanDefinitionException
     */
    public static String[] getAliases(String name) throws NoSuchBeanDefinitionException {
        return beanFactory.getAliases(name);
    }

    /**
     * 获取aop代理对象
     *
     * @param invoker
     * @return
     */
    @SuppressWarnings("unchecked")
    public static <T> T getAopProxy(T invoker) {
        return (T) AopContext.currentProxy();
    }
}

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;

/**
 * @author Clinton Begin
 */
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;

/**
 * Creates an {@link SqlSession} out of a connection or a DataSource
 * SqlSessionFactory源码
 * @author Clinton Begin
 */
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;
  }