掘金 后端 ( ) • 2024-05-07 13:48
MybatisPlus自定义insertBatchSomeColumn实现真正批量插入

一、批量插入数据SQL

  • MySQL批量插入数据SQL
INSERT INTO TABLE_NAME(COLUMN1,COLUMN2...,COLUMNN)VALUES(VALUE1,VALUE2...,VALUEN),(VALUE1,VALUE2...,VALUEN),(VALUE1,VALUE2...,VALUEN);
  • Oracle批量插入数据SQL
INSERT ALL
INTO TABLE_NAME(COLUMN1,COLUMN2...,COLUMNN)VALUES(VALUE1,VALUE2...,VALUEN)
INTO TABLE_NAME(COLUMN1,COLUMN2...,COLUMNN)VALUES(VALUE1,VALUE2...,VALUEN)
INTO TABLE_NAME(COLUMN1,COLUMN2...,COLUMNN)VALUES(VALUE1,VALUE2...,VALUEN)
SELECT * FROM DUAL

二、MybatisPlus批量插入实现方式

2.1 通过实现MybatisPlus IService接口，获取saveBatch，底层其实是单条插入

    @Transactional(
        rollbackFor = {Exception.class}
    )
    public boolean saveBatch(Collection<T> entityList, int batchSize) {
        String sqlStatement = this.getSqlStatement(SqlMethod.INSERT_ONE);
        return this.executeBatch(entityList, batchSize, (sqlSession, entity) -> {
            sqlSession.insert(sqlStatement, entity);
        });
    }

2.2 通过XML手动拼接SQL实现批量插入

缺点是每个表都要手动编写xml，优点是效率较高

  • MySQL
<insert id="batchInsert" parameterType="java.util.List">
	insert into user (id, name, age)values
	<foreach collection="list" item="user" separator=",">
		 (#{user.id}, #{user.name}, #{user.age})
	</foreach>
</insert>
  • Oracle
// mapper.xml
<insert id="batchInsert" parameterType="java.util.List">
	insert all
	<foreach collection="list" item="user" separator=",">
		into user (id, name, age) values(#{user.id}, #{user.name}, #{user.age})
	</foreach>
	select * from dual
</insert>

2.3 通过使用InsertBatchSomeColumn方法批量插入

底层也是拼接sql，但无需手动编写sql语句，效率同第二种，本文重点介绍这种方式，使用步骤：

2.3.1. 自定义SQL注入器实现DefaultSqlInjector，添加InsertBatchSomeColumn方法

MySQL版

public class MySqlInjector extends DefaultSqlInjector {

    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
        methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));
        return methodList;
    }
}

Oracle版

import com.baomidou.mybatisplus.annotation.FieldFill;  
import com.baomidou.mybatisplus.core.injector.AbstractMethod;  
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;  
  
import java.util.List;  
  
public class OracleInjector extends DefaultSqlInjector {  
  
	@Override  
	public List<AbstractMethod> getMethodList(Class<?> mapperClass) {  
		List<AbstractMethod> methodList = super.getMethodList(mapperClass);  
		//这里改成我们自己的实现MyInsertBatchSomeColumn  
		methodList.add(new OracleInsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));  
		return methodList;  
	}  
}

2.3.2 编写配置类，把自定义注入器放入spring容器

@Configuration  
public class MyBatisConfig {  
	@Bean  
	public OracleInjector sqlInjector() {  
		return new OracleInjector();  
	}
}

2.3.2 编写自定义BaseMapper,加入InsertBatchSomeColumn方法

public interface MyBaseMapper<T> extends BaseMapper<T> {
    /**
     * 以下定义的 4个 method 其中 3 个是内置的选装件
     */
    int insertBatchSomeColumn(List<T> entityList);
}

2.3.4 需要批量插入的Mapper继承自定义BaseMapper

@Mapper
public interface UserMapper extends MyBaseMapper<Student> {
	
}

2.3.5 修改适配Oracle

先了解下，Oracle批量插入数据SQL

INSERT ALL
INTO TABLE_NAME(COLUMN1,COLUMN2...,COLUMNN)VALUES(VALUE1,VALUE2...,VALUEN)
INTO TABLE_NAME(COLUMN1,COLUMN2...,COLUMNN)VALUES(VALUE1,VALUE2...,VALUEN)
INTO TABLE_NAME(COLUMN1,COLUMN2...,COLUMNN)VALUES(VALUE1,VALUE2...,VALUEN)
SELECT * FROM DUAL

因此我们需要把SQL组装成这种结构,查看InsertBatchSomeColumn类，可以发现SQL组装逻辑在injectMappedStatement方法，因此我们模仿InsertBatchSomeColumn类，编写SQL组装逻辑

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.core.enums.SqlMethod;
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.extension.injector.methods.InsertBatchSomeColumn;
import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.experimental.Accessors;
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.Map;
import java.util.function.Predicate;

@NoArgsConstructor
@AllArgsConstructor
@SuppressWarnings("serial")
public class OracleInsertBatchSomeColumn extends InsertBatchSomeColumn {

    @Setter
    @Accessors(chain = true)
    private Predicate<TableFieldInfo> predicate;

    private final String INSERT_BATCH_SQL="<script>\nINSERT ALL \n  %s\n</script>";

    @SuppressWarnings("Duplicates")
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        //pojo类型为Map时禁用
        if (tableInfo.getEntityType().equals(Map.class)) {
            return null;
        }
        KeyGenerator keyGenerator = new NoKeyGenerator();
        SqlMethod sqlMethod = SqlMethod.INSERT_ONE;
        List<TableFieldInfo> fieldList = tableInfo.getFieldList();
        String insertSqlColumn = tableInfo.getKeyInsertSqlColumn(false) +
                this.filterTableFieldInfo(fieldList, predicate, TableFieldInfo::getInsertSqlColumn, EMPTY);
        String columns = insertSqlColumn.substring(0, insertSqlColumn.length() - 1) ;
        String insertSqlProperty = tableInfo.getKeyInsertSqlProperty(ENTITY_DOT, false) +
                this.filterTableFieldInfo(fieldList, predicate, i -> i.getInsertSqlProperty(ENTITY_DOT), EMPTY);
        insertSqlProperty = LEFT_BRACKET + insertSqlProperty.substring(0, insertSqlProperty.length() - 1) + RIGHT_BRACKET;
        String valuesScript = convertForeach(insertSqlProperty, "list", tableInfo.getTableName(),columns, ENTITY, NEWLINE);
        String keyProperty = null;
        String keyColumn = null;
        // 表包含主键处理逻辑,如果不包含主键当普通字段处理
        if (tableInfo.havePK()) {
            if (tableInfo.getIdType() == IdType.AUTO) {
                /* 自增主键 */
                keyGenerator = new Jdbc3KeyGenerator();
                keyProperty = tableInfo.getKeyProperty();
                keyColumn = tableInfo.getKeyColumn();
            } else {
                if (null != tableInfo.getKeySequence()) {
                    keyGenerator = TableInfoHelper.genKeyGenerator(getMethod(sqlMethod), tableInfo, builderAssistant);
                    keyProperty = tableInfo.getKeyProperty();
                    keyColumn = tableInfo.getKeyColumn();
                }
            }
        }
        String sql = String.format(INSERT_BATCH_SQL, valuesScript);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
        return this.addInsertMappedStatement(mapperClass, modelClass, getMethod(sqlMethod), sqlSource, keyGenerator, keyProperty, keyColumn);
    }
    public static String convertForeach(final String sqlScript, final String collection, final String tableName,final String columns, final String item, final String separator) {
        StringBuilder sb = new StringBuilder("<foreach");

        if (StringUtils.isNotBlank(collection)) {
            sb.append(" collection=\"").append(collection).append("\"");
        }

        if (StringUtils.isNotBlank(item)) {
            sb.append(" item=\"").append(item).append("\"");
        }

        if (StringUtils.isNotBlank(separator)) {
            sb.append(" separator=\"").append(separator).append("\"");
        }

        sb.append(">").append("\n");

        if (StringUtils.isNotBlank(tableName)) {
            sb.append(" INTO ").append(tableName).append(" ");
        }

        if (StringUtils.isNotBlank(columns)) {
            sb.append(LEFT_BRACKET).append(columns).append(RIGHT_BRACKET).append(" VALUES ");
        }

        return sb.append(sqlScript).append("\n").append("</foreach>\n").append(" SELECT ").append("*").append(" FROM dual").toString();
    }
}

执行批量插入，会发现报错

 Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='__frch_et_0.serialno', mode=IN, javaType=class java.lang.String, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #2 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 无效的列类型: 1111] with root cause

这是因为字段值为NULL时无法确定jdbcType是什么类型，导致插入失败，有两种解决方法，第一种是指定实体所有属性的jdbcType类型，如

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.Data;
import org.apache.ibatis.type.JdbcType;

import java.io.Serializable;
import java.util.Date;


@SuppressWarnings("serial")
@Data
public class TzVerifyLog extends Model<TzVerifyLog> {
    private String id;
    @TableField(value = "serialno",jdbcType = JdbcType.VARCHAR)
    private String serialno;
    @TableField(value = "verify_msg",jdbcType = JdbcType.VARCHAR)
    private String verifyMsg;
    @TableField(value = "type",jdbcType = JdbcType.VARCHAR)
    private String type;
    @TableField(value = "row_num",jdbcType = JdbcType.INTEGER)
    private Integer rowNum;

    @TableField(value = "createtime",jdbcType = JdbcType.DATE)
    private Date createtime;

    /**
     * 获取主键值
     *
     * @return 主键值
     */
    @Override
    protected Serializable pkVal() {
        return this.id;
    }
}

第二种是设置mybatisplus的jdbc-type-for-null属性值

mybatis-plus:
  configuration:
    jdbc-type-for-null: varchar #空值时设置为varchar类型

2.4 service封装InsertBatchSomeColumn方法

service封装insertBatchSomeColumn方法，方便后面调用

  • 新建一个IMyService接口继承IServic

import com.baomidou.mybatisplus.extension.service.IService;

import java.util.List;

public interface IMyService <T> extends IService<T> {
    int insertBatchSomeColumn(List<T> entityList);
    int insertBatchSomeColumn(List<T> entityList,int batchSize);
}
  • 新建一个MyServiceImpl类继承ServiceImpl
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;


import java.util.ArrayList;
import java.util.List;

public class MyServiceImpl<M extends MyBaseMapper<T>, T>extends ServiceImpl<M,T> implements IMyService<T> {
    @Override
    public int insertBatchSomeColumn(List<T> entityList) {
        return this.baseMapper.insertBatchSomeColumn(entityList);
    }

    @Override
    public int insertBatchSomeColumn(List<T> entityList, int batchSize) {
        int size=entityList.size();
        if(size<batchSize){
            return this.baseMapper.insertBatchSomeColumn(entityList);
        }
        int page=1;
        if(size % batchSize ==0){
            page=size/batchSize;
        }else {
            page=size/batchSize+1;
        }
        for (int i = 0; i < page; i++) {
            List<T> sub = new ArrayList<>();
            if(i==page-1){
                sub=entityList.subList(i*batchSize, entityList.size());
            }else {
                sub.subList(i*batchSize,(i+1)*batchSize-1);
            }
            if(sub.size()>0){
                baseMapper.insertBatchSomeColumn(sub);
            }

        }
        return size;
    }
}
  • 实体Service接口和接口实现类都分别继承IMyService和MyServiceImpl
public interface ITzVerifyLogService extends IMyService<TzVerifyLog> {  
  
}


import org.springframework.stereotype.Service;  

@Service 
public class TzVerifyLogServiceImpl extends MyServiceImpl<TzVerifyLogMapper, TzVerifyLog> implements ITzVerifyLogService {  
  
}