Mybatis之批处理DML操作

1 批处理插入更新

1.1 简介

1.1.1 定义

处理批处理的方式有很多种，这里不分析各种方式的优劣，只是概述 ExecutorType.BATCH这种的用法

简单了解一下批处理背后的秘密，BatchExecutor批处理是 JDBC 编程中的另一种优化手段。JDBC 在执行 SQL 语句时，会将 SQL 语句以及实参通过网络请求的方式发送到数据库，一次执行一条 SQL 语句，一方面会减小请求包的有效负载，另一个方面会增加耗费在网络通信上的时间。

通过批处理的方式，我们就可以在 JDBC 客户端缓存多条 SQL语句，然后在 flush 或缓存满的时候，将多条 SQL 语句打包发送到数据库执行，这样就可以有效地降低上述两方面的损耗，从而提高系统性能。

不过，有一点需要特别注意：

每次向数据库发送的 SQL 语句的条数是有上限的，如果批量执行的时候超过这个上限值，数据库就会抛出异常，拒绝执行这一批 SQL 语句，所以我们需要控制批量发送 SQL 语句的条数和频率

1.1.2 ExecutorType.BATCH使用步骤

以下是如何在 MyBatis 中使用 ExecutorType.BATCH 的示例：

• 需要获取一个批处理类型的 SqlSession：

SqlSessionFactory sqlSessionFactory = ... // 获取 SqlSessionFactory
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);

• 通过这个 SqlSession 获取 Mapper，并执行批处理操作：

YourMapper mapper = sqlSession.getMapper(YourMapper.class);
for (YourEntity entity : entities) {
    mapper.insert(entity); // 或者 update/delete
}

• 手动提交事务，并关闭 SqlSession：

sqlSession.commit();
sqlSession.close();

1.1.3 注意事项

需要注意的是：

• 在使用 ExecutorType.BATCH 时，MyBatis 不会立即执行 SQL，而是将 SQL 缓存起来，等到调用 SqlSession.commit() 或 SqlSession.flushStatements() 时才会一次性执行。
• 如果在批处理过程中出现异常，需要确保进行适当的错误处理，例如回滚事务。
• 在使用 ExecutorType.BATCH 时，insert、update 和 delete 操作返回的结果可能不正确（通常为 -2147482646）。如果需要获取这些操作的结果，可以在 SqlSession.flushStatements() 之后调用 SqlSession.clearCache()，然后再执行查询操作。
• 如果批处理的数据量非常大，可能需要在每处理一定数量的数据后调用一次 SqlSession.flushStatements() 和 SqlSession.clearCache()，以避免内存溢出。

1.2 JDBC使用批量

使用Batch批量处理数据库，当需要向数据库发送一批SQL语句执行时，应避免向数据库一条条的发送执行，而应采用JDBC的批处理机制，以提升执行效率

1.2.1 Statement批处理

使用Statement批处理

Statement.addBatch(sql) list 执行批处理SQL语句
executeBatch()方法：执行批处理命令
clearBatch()方法：清除批处理命令

Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JDBCManager.getConnection();
String sql1 = "insert into user(name,password,email,birthday) 
values('kkk','123','abc@sina.com','1978-08-08')";
String sql2 = "update user set password='自定义密码' where id=3";
st = conn.createStatement();
st.addBatch(sql1);  //把SQL语句加入到批命令中
st.addBatch(sql2);  //把SQL语句加入到批命令中
st.executeBatch();
} finally{
JDBCManager.DBclose(con,st,rs);
}

采用Statement.addBatch(sql)方式实现批处理：
优点：可以向数据库发送多条不同的SQL语句。
缺点：SQL语句没有预编译。
当向数据库发送多条语句相同，但仅参数不同的SQL语句时，需重复写上很多条SQL语句。例如：

Insert into user(name,password) values(‘aa’,’111’);
Insert into user(name,password) values(‘bb’,’222’);
Insert into user(name,password) values(‘cc’,’333’);
Insert into user(name,password) values(‘dd’,’444’);

1.2.2 PreparedStatement批处理

PreparedStatement批处理

PreparedStatement.addBatch();

conn = JDBCManager.getConnection();//获取工具；
String sql = "insert into user(name,password,email,birthday) values(?,?,?,?)";
st = conn.prepareStatement(sql);//预处理sql语句；
for(int i=0;i<50000;i++){
st.setString(1, "aaa" + i);
st.setString(2, "123" + i);
st.setString(3, "aaa" + i + "@sina.com");
st.setDate(4,new Date(1980, 10, 10));
 
st.addBatch();//将一组参数添加到此 PreparedStatement 对象的批处理命令中。
if(i%1000==0){
st.executeBatch();
st.clearBatch();清空此 Statement 对象的当前 SQL 命令列表。 
}
}

st.executeBatch();将一批命令提交给数据库来执行，如果全部命令执行成功，则返回更新计数组成的数组。返回数组的 int 元素的排序对应于批中的命令，批中的命令根据被添加到批中的顺序排序

采用PreparedStatement.addBatch()实现批处理
优点：发送的是预编译后的SQL语句，执行效率高。
缺点：只能应用在SQL语句相同，但参数不同的批处理中。因此此种形式的批处理经常用于在同一个表中批量插入数据，或批量更新表的数据。

1.3 Mybatis初级使用批量

废话不多说，早先时候项目的代码里就已经存在了批处理的代码，伪代码的样子大概是这样子的：

@Autowired
private SqlSessionFactory sqlSessionFactory;
@Resource
private TestMapper testMapper;

private int BATCH = 1000;
  private void doUpdateBatch(Date accountDate, List<某实体类> data) {
   
    try (SqlSession batchSqlSession =sqlSessionFactory.openSession(ExecutorType.BATCH, false);){
      if (data == null || data.size() == 0) {
        return;
      }
    TestMapper  mapper = batchSqlSession .getMapper(TestMapper.class);
      for (int index = 0; index < data.size(); index++) {
        mapper.更新/插入Method(accountDate, data.get(index).getOrderNo());
        if (index != 0 && index % BATCH == 0) {
          batchSqlSession.commit();
          batchSqlSession.clearCache();
        }
      }
      batchSqlSession.commit();
    } catch (Exception e) {
      batchSqlSession.rollback();
      log.error(e.getMessage(), e);
    } 
  }

我们先来看看上述这种写法的几种问题

然后我们结合上述写法，它会在判断批处理条数达到1000条的时候会去手动commit，然后又手动clearCache，我们先来看看commit到底都做了一些什么，以下为调用链

@Override
public void commit() {
    commit(false);
}  

@Override
public void commit(boolean force) {
    try {
      executor.commit(isCommitOrRollbackRequired(force));
      dirty = false;
    } catch (Exception e) {
      throw ExceptionFactory.wrapException("Error committing transaction.  Cause: " + e, e);
    } finally {
      ErrorContext.instance().reset();
    }
  }

private boolean isCommitOrRollbackRequired(boolean force) {
// autoCommit默认为false，调用过插入、更新、删除之后的dirty值为true
    return (!autoCommit && dirty) || force;
}

  @Override
  public void commit(boolean required) throws SQLException {
    if (closed) {
      throw new ExecutorException("Cannot commit, transaction is already closed");
    }
    clearLocalCache();
    flushStatements();
    if (required) {
      transaction.commit();
    }
  }

我们会发现，其实你直接调用commit的情况下，它就已经做了clearLocalCache这件事情，所以大可不必在commit后加上一句clearCache

flushStatements的作用就是将前面所有执行过的INSERT、UPDATE、DELETE语句真正刷新到数据库中。底层调用了JDBC的statement.executeBatch方法。

这个方法的返回值通俗来说如果执行的是同一个方法并且执行的是同一条SQL，注意这里的SQL还没有设置参数，也就是说SQL里的占位符?还没有被处理成真正的参数，那么每次执行的结果共用一个BatchResult，真正的结果可以通过BatchResult中的getUpdateCounts方法获取。

另外如果执行了SELECT操作，那么会将先前的UPDATE、INSERT、DELETE语句刷新到数据库中。这一点去看BatchExecutor中的doQuery方法即可。

1.4 Mybatis升级使用

由于项目中用到批处理的地方肯定不止一个，那每用一次就需要CV一下，能不能一劳永逸

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;
import java.util.function.ToIntFunction;

@Slf4j
@Component
public class MybatisBatchUtils {

    /**
     * 每次处理1000条
     */
    private static final int BATCH = 1000;

    @Resource
    private SqlSessionFactory sqlSessionFactory;

    /**
     * 批量处理修改或者插入
     *
     * @param data     需要被处理的数据
     * @param function 自定义处理逻辑
     * @return int 影响的总行数
     */
    public  <T> int batchUpdateOrInsert(List<T> data, ToIntFunction<T> function) {
        int count = 0;
        SqlSession batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        try {
            for (int index = 0; index < data.size(); index++) {
                count += function.applyAsInt(data.get(index));
                if (index != 0 && index % BATCH == 0) {
                    batchSqlSession.flushStatements();
                }
            }
            batchSqlSession.commit();
        } catch (Exception e) {
            batchSqlSession.rollback();
            log.error(e.getMessage(), e);
        } finally {
            batchSqlSession.close();
        }
        return count;
    }
}

伪代码使用案例

@Resource
private 某Mapper类 mapper实例对象;

batchUtils.batchUpdateOrInsert(数据集合, item -> mapper实例对象.insert方法(item));

1.5 Mybatis批量标准写法

我们知道上面我们提到了BatchExecutor执行器，我们知道每个SqlSession都会拥有一个Executor对象，这个对象才是执行 SQL 语句的幕后黑手，我们也知道Spring跟Mybatis整合的时候使用的SqlSession是SqlSessionTemplate，默认用的是ExecutorType.SIMPLE，这个时候你通过自动注入获得的Mapper对象其实是没有开启批处理的

那么我们实际上是需要通过sqlSessionFactory.openSession(ExecutorType.BATCH)得到的sqlSession对象（此时里面的Executor是BatchExecutor）去获得一个新的Mapper对象才能生效

所以更改一下这个通用的方法，把MapperClass也一块传递进来

public class MybatisBatchUtils {
    
    /**
    * 每次处理1000条
    */
    private static final int BATCH_SIZE = 1000;
    
    @Resource
    private SqlSessionFactory sqlSessionFactory;
    
    /**
    * 批量处理修改或者插入
    *
    * @param data     需要被处理的数据
    * @param mapperClass  Mybatis的Mapper类
    * @param function 自定义处理逻辑
    * @return int 影响的总行数
    */
    public  <T,U,R> int batchUpdateOrInsert(List<T> data, Class<U> mapperClass, BiFunction<T,U,R> function) {
        int i = 1;
        SqlSession batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        try {
            U mapper = batchSqlSession.getMapper(mapperClass);
            int size = data.size();
            for (T element : data) {
                function.apply(element,mapper);
                if ((i % BATCH_SIZE == 0) || i == size) {
                    batchSqlSession.flushStatements();
                }
                i++;
            }
            // 非事务环境下强制commit，事务情况下该commit相当于无效
            batchSqlSession.commit(!TransactionSynchronizationManager.isSynchronizationActive());
        } catch (Exception e) {
            batchSqlSession.rollback();
            throw new CustomException(e);
        } finally {
            batchSqlSession.close();
        }
        return i - 1;
    }
}

这里会判断是否是事务环境，不是的话会强制提交，如果是事务环境的话，这个commit设置force值是无效的

使用案例：

batchUtils.batchUpdateOrInsert(数据集合, xxxxx.class, (item, mapper实例对象) -> mapper实例对象.insert方法(item));

1.6 使用rewriteBatchedStatements

rewriteBatchedStatements=true 是 MyBatis 配置文件中的一个属性，用于优化批量插入或更新操作的性能。
当 rewriteBatchedStatements=true 时，MyBatis 会将批量插入或更新的 SQL 语句重写为单条语句，然后发送给数据库执行。这样可以减少网络传输的次数，提高插入或更新操作的效率。

默认情况下，rewriteBatchedStatements 的值为 false，即不启用该优化。这是因为在某些数据库（如 MySQL）中，启用该优化可能会导致主键冲突或其他异常。因此，如果使用的数据库支持该优化并且不会引起问题，可以将该属性设置为 true，以提高批量插入或更新操作的性能。

需要注意的是，不是所有的数据库和驱动程序都支持 rewriteBatchedStatements。因此，在配置该属性之前，应该先了解你所使用的数据库的特性和限制。

具体配置如下：

spring:
  application:
    name: batch-demo
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: xxxx
    password: xxxx
    url: jdbc:mysql://127.0.0.1:3306/test?useSSL=false&serverTimezone=GMT&characterEncoding=utf8&rewriteBatchedStatements=true