MybatisPlus如何添加批量插入的方法

本章我们来介绍如何在MybatisPlus添加批量插入方法。

前提条件:需要依赖baomiduo的MybatisPlus（一款基于Mybatis的方便增删改查的拓展）。
实现步骤
1:利用MapperBuilderAssistant#addMappedStatement动态添加一个Mapper.
2:获取对象模型对应的TableInfo(映射对应的表模型信息，包含了表名，字段名等等)。
3:生成用<script>包裹的Insert语句标签。

先来一个来定义<script>模板的枚举:

public enum CustSqlMethod {

    INSERT_BATCH("insertBatch", "插入多条数据", "<script> INSERT INTO %s (%s) %s</script>");


    private final String method;
    private final String desc;
    private final String sql;

    CustSqlMethod(String method, String desc, String sql) {
        this.method = method;
        this.desc = desc;
        this.sql = sql;
    }

    public String getMethod() {
        return method;
    }

    public String getDesc() {
        return desc;
    }

    public String getSql() {
        return sql;
    }
}

继承AbstractMethod添加一个Mapper

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfo;

import oracle.sql.TIMESTAMP;
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.SqlCommandType;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.type.JdbcType;

import java.lang.reflect.Field;
import java.util.Collections;
import java.util.Date;
import java.util.List;

/**
 * @Author:lycol
 * 
 */
public class BatchInsertByList extends AbstractMethod {



    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        KeyGenerator keyGenerator = new NoKeyGenerator();
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, getBatchInsertSql(tableInfo,modelClass), Collections.class);
        return this.addMappedStatement(mapperClass,CustSqlMethod.INSERT_BATCH.getMethod(),sqlSource,
                SqlCommandType.INSERT, Collections.class,null,int.class,keyGenerator,null,null);

    }

    private String getBatchInsertSql(TableInfo tableInfo){
        String batchInsertSql=CustSqlMethod.INSERT_BATCH.getSql();

        StringBuilder insertColumnBuilder=new StringBuilder();
        StringBuilder itemColumnBuilder=new StringBuilder();
        List<TableFieldInfo> fieldList=tableInfo.getFieldList();
        int size=fieldList.size();

        //主键id
        insertColumnBuilder.append(tableInfo.getKeyColumn()).append(",");

        for(int i=0;i<size-1;i++){
            TableFieldInfo tableFieldInfo=fieldList.get(i);
            insertColumnBuilder.append(tableFieldInfo.getColumn()).append(",");
            String jdbcType=getJdbcTypeByClassType(tableFieldInfo.getPropertyType());
            itemColumnBuilder.append("#{item." + tableFieldInfo.getProperty() + ",jdbcType="+jdbcType+"},\n");
        }
        TableFieldInfo tableFieldInfo=fieldList.get(size-1);
        insertColumnBuilder.append(tableFieldInfo.getColumn());

        String jdbcType=getJdbcTypeByClassType(tableFieldInfo.getPropertyType());
        itemColumnBuilder.append("#{item." + tableFieldInfo.getProperty() + ",jdbcType="+jdbcType+"}");

        String foreachSql;
        //如果是oracle数据库
        if(tableInfo.getDbType()== DbType.ORACLE) {
            foreachSql = "SELECT RAWTOHEX(SYS_GUID()), record.* FROM (\n" +
                    " <foreach collection='items' item='item' index='index' separator='union all'>\n" +
                    " select\n" +
                    itemColumnBuilder.toString() +
                    " FROM dual\n" +
                    " </foreach>\n" +
                    " ) record";
        }

        //如果是非oracle数据库
       else   {
            foreachSql = "values" +
                    " <foreach collection='items' item='item'  open='' index='index' separator=','>\n" +
                    "(%s,%s)</foreach>";
            foreachSql=String.format(foreachSql,"#{item."+tableInfo.getKeyProperty()+",jdbcType=VARCHAR}",itemColumnBuilder);
        }

        return  String.format(batchInsertSql,tableInfo.getTableName(),insertColumnBuilder,foreachSql);
    }

    private String getJdbcTypeByClassType(Class clazz){
        if(clazz.getSuperclass()==Number.class){
            return JdbcType.NUMERIC.name();
        }
        if(clazz==String.class){
            return  JdbcType.VARCHAR.name();
        }
        if(clazz== Date.class||clazz== java.sql.Date.class){
            return  JdbcType.DATE.name();
        }

        if(clazz== TIMESTAMP.class){
            return  JdbcType.TIMESTAMP.name();
        }
        //默认返回JavaObject
        return  JdbcType.JAVA_OBJECT.name();

    }

}

第二步:添加自定义注入方法

/**
 * 添加Sql注入方法,支持空字段更新
 */
public class CustomerSqlInjector extends DefaultSqlInjector {
    @Override
    public List<AbstractMethod> getMethodList(){
        List<AbstractMethod> methodList=super.getMethodList();
        methodList.add(new UpdateAllColumnById());
        methodList.add(new BatchInsertByList());

        return methodList;
    }
}

在MybatisMapper Configuration中注入GlobalConfiguration

@Bean(name = "globalConfiguration")
    @Primary
    public GlobalConfig globalConfiguration() {
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setSqlInjector(new CustomerSqlInjector());
        return globalConfig;
    }

 @Bean(name = "SqlSessionFactory")
    @Primary
    public SqlSessionFactory dbSqlSessionFactory(@Qualifier("DataSource") DataSource dataSource,
                                                 @Qualifier("globalConfiguration") GlobalConfig globalConfiguration) throws Exception {
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setPlugins(getPlusInterceptor());
        bean.setGlobalConfig(globalConfiguration);
        return bean.getObject();
    }

如何引用

public interface JianShuMapper extends BaseMapper<LoanRepayment> {
    int insertBatch(@Param(value = "items") Collection idList);

}

源代码地址:https://github.com/Leeyongke/mybatis-plus_batch_insert
完毕!