mybatis plus批量插入(MySQL)

越来越多的人不喜欢在service中继承mybatis plus提供的service了, 理由主要有以下两点:

1. 继承破坏封装, 直接使用mybatis plus提供的service, 会失去很多校验
2. dao层分离问题

场景

如果不继承IService, 批量插入将无法使用了, 所以要找一个替代方案

经百度得知, 可以通过这个方法来实现com.baomidou.mybatisplus.extension.injector.methods.InsertBatchSomeColumn

但根据实验, 如果数据库中配置了默认值, 是无法实现的, 也就是字段设置为:

create table test(
    id     bigint primary key comment 'id',
    field1 varchar(10) not null default 'f1' comment '测试字段1'
) comment '测试表';

此时执行批量插入, 会执行SQL:

insert into test (id, field1) values (1, null); 
# 报错[23000][1048] Column 'field1' cannot be null

这里我说其中一种方案, 就是把null换成default

这里提一嘴, 如果id是自增的, 为null没影响

insert into test (id, field1) values (1, default);

解决方案

1. 根据InsertBatchSomeColumn写个自己的InsertList(函数名起的不太理想, 欢迎各位提意见)

   package kim.nzxy.ly.common.config.method;
   
   import com.baomidou.mybatisplus.annotation.FieldFill;
   import com.baomidou.mybatisplus.annotation.IdType;
   import com.baomidou.mybatisplus.core.enums.SqlMethod;
   import com.baomidou.mybatisplus.core.injector.AbstractMethod;
   import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
   import com.baomidou.mybatisplus.core.metadata.TableInfo;
   import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
   import com.baomidou.mybatisplus.core.toolkit.StringPool;
   import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
   import org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator;
   import org.apache.ibatis.executor.keygen.KeyGenerator;
   import org.apache.ibatis.executor.keygen.NoKeyGenerator;
   import org.apache.ibatis.mapping.MappedStatement;
   import org.apache.ibatis.mapping.SqlSource;
   
   import java.util.List;
   import java.util.function.Predicate;
   
   /**
    * 批量插入方法支持
    *
    * @author ly-chn
    */
   public class InsertList extends AbstractMethod {
       private static final Predicate<TableFieldInfo> PREDICATE = i -> i.getFieldFill() != FieldFill.UPDATE;
   
       public InsertList() {
           super("insertList");
       }
   
       @Override
       public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
           KeyGenerator keyGenerator = NoKeyGenerator.INSTANCE;
           SqlMethod sqlMethod = SqlMethod.INSERT_ONE;
           List<TableFieldInfo> fieldList = tableInfo.getFieldList();
           String insertSqlColumn = tableInfo.getKeyInsertSqlColumn(true, false) +
                   this.filterTableFieldInfo(fieldList, null, TableFieldInfo::getInsertSqlColumn, "");
           String columnScript = "(" + insertSqlColumn.substring(0, insertSqlColumn.length() - 1) + ")";
           String insertSqlProperty = tableInfo.getKeyInsertSqlProperty(true, "et.", false) + this.filterTableFieldInfo(fieldList, PREDICATE, (i) -> {
               String splitter = fieldList.indexOf(i) == fieldList.size() - 1 ? StringPool.SPACE : StringPool.COMMA;
               return "<choose>\n" +
                       "<when test=\"et." + i.getProperty() +
                       "!=null \">#{et." + i.getProperty() +
                       "}" + splitter +
                       "</when>\n" +
                       "<otherwise>default" + splitter +
                       "</otherwise>" +
                       "</choose>";
           }, "");
           insertSqlProperty = "(" + insertSqlProperty + ")";
   
           String valuesScript = SqlScriptUtils.convertForeach(insertSqlProperty, "list", null, "et", ",");
           String keyProperty = null;
           String keyColumn = null;
           if (tableInfo.havePK()) {
               if (tableInfo.getIdType() == IdType.AUTO) {
                   keyGenerator = Jdbc3KeyGenerator.INSTANCE;
                   keyProperty = tableInfo.getKeyProperty();
                   keyColumn = tableInfo.getKeyColumn();
               } else if (null != tableInfo.getKeySequence()) {
                   keyGenerator = TableInfoHelper.genKeyGenerator(this.methodName, tableInfo, this.builderAssistant);
                   keyProperty = tableInfo.getKeyProperty();
                   keyColumn = tableInfo.getKeyColumn();
               }
           }
   
           String sql = String.format(sqlMethod.getSql(), tableInfo.getTableName(), columnScript, valuesScript);
           SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, modelClass);
           return this.addInsertMappedStatement(mapperClass, modelClass, methodName, sqlSource, keyGenerator, keyProperty, keyColumn);
       }
   }
   

   稍作解释, 具体自己debug一下, 项目启动时自动生成mybatis的实现方式, 如果是上述的表, 大致生成的SQL如下:

   insert into test (id, field1) values 
    <foreach collection="list" item="et" separator=",">
       (#{et.id},
       <choose>
           <when test="et.field1!=null ">#{et.field1}</when>
           <otherwise>default</otherwise>
       </choose>
       )
   </foreach>
   

2. 将改方法添加到mybatis plus中

   @Bean
   public DefaultSqlInjector sqlInjector() {
       return new DefaultSqlInjector() {
           @Override
           public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
               List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
               // 批量插入
               methodList.add(new InsertList());
               return methodList;
           }
       };
   }
   

3. 在自定义的baseMapper中添加

   package kim.nzxy.ly.common.mapper;
   
   import com.baomidou.mybatisplus.core.mapper.BaseMapper;
   import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
   
   import java.util.ArrayList;
   import java.util.Collection;
   import java.util.Iterator;
   import java.util.List;
   
   /**
    * 自定义基础mapper
    *
    * @param <T> 实体类
    * @author ly-chn
    */
   public interface LyMapper<M, T> extends BaseMapper<T> {
       /**
        * 批插, 但是不自动分批
        *
        * @param entityList 要插入的数据
        */
       void insertList(Collection<T> entityList);
   
       /**
        * 批量插入, 分批, 每次1000条
        *
        * @param entityList 要插入的实体类列表
        */
       default void insertBatch(Collection<T> entityList) {
           insertBatch(entityList, 1000);
       }
       
       /**
        * 批量插入(包含限制条数)
        *
        * @param entityList 要插入的数据
        * @param batchSize  每次插入条数
        */
       default void insertBatch(Collection<T> entityList, int batchSize) {
           if (CollectionUtils.isEmpty(entityList)) {
               return;
           }
           int size = entityList.size();
           int idxLimit = Math.min(batchSize, size);
           int i = 1;
           // 保存单批提交的数据集合
           List<T> oneBatchList = new ArrayList<>();
           for (Iterator<T> it = entityList.iterator(); it.hasNext(); ++i) {
               T element = it.next();
               oneBatchList.add(element);
               if (i == idxLimit) {
                   insertList(oneBatchList);
                   // 每次提交后需要清空集合数据
                   oneBatchList.clear();
                   idxLimit = Math.min(idxLimit + batchSize, size);
               }
           }
       }
   }