mybatisplus 批量插入与修改

保留原mybatisplus自有的方法

1：重写injectMappedStatement，生成拼接批量新增/修改sql的脚本

/**
 * @Description 重写injectMappedStatement，生成拼接批量新增sql的脚本
 * @Author WangKun
 * @Date 2024/2/26 16:55
 * @Version
 */
public class InsertBatchMethod extends AbstractMethod {

    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        final String sql = "<script>insert into %s %s values %s</script>";
        final String fieldSql = prepareFieldSql(tableInfo);
        final String valueSql = prepareValuesSql(tableInfo);
        final String sqlResult = String.format(sql, tableInfo.getTableName(), fieldSql, valueSql);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
        return this.addInsertMappedStatement(mapperClass, modelClass, "insertBatch", sqlSource, new NoKeyGenerator(), null, null);
    }

    private String prepareFieldSql(TableInfo tableInfo) {
        StringBuilder fieldSql = new StringBuilder();
        fieldSql.append(tableInfo.getKeyColumn()).append(",");
        tableInfo.getFieldList().forEach(x -> {
            fieldSql.append(x.getColumn()).append(",");
        });
        fieldSql.delete(fieldSql.length() - 1, fieldSql.length());
        fieldSql.insert(0, "(");
        fieldSql.append(")");
        return fieldSql.toString();
    }

    private String prepareValuesSql(TableInfo tableInfo) {
        final StringBuilder valueSql = new StringBuilder();
        valueSql.append("<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"(\" separator=\"),(\" close=\")\">");
        valueSql.append("#{item.").append(tableInfo.getKeyProperty()).append("},");
        tableInfo.getFieldList().forEach(x -> valueSql.append("#{item.").append(x.getProperty()).append("},"));
        valueSql.delete(valueSql.length() - 1, valueSql.length());
        valueSql.append("</foreach>");
        return valueSql.toString();
    }


}

/**
 * @Description 重写injectMappedStatement，生成拼接批量更新sql的脚本
 * @Author WangKun
 * @Date 2024/2/26 16:51
 * @Version
 */
public class UpdateBatchMethod extends AbstractMethod {

    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        final String sql = "<script>\n update %s %s \n where id in \n <foreach collection=\"list\" item=\"item\" separator=\",\" open=\"(\" close=\")\">\n #{item.id} </foreach> \n </script>";
        final String valueSql = prepareValuesSql(tableInfo);
        final String sqlResult = String.format(sql, tableInfo.getTableName(), valueSql);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
        return this.addUpdateMappedStatement(mapperClass, modelClass, "updateBatch", sqlSource);
    }

    private String prepareValuesSql(TableInfo tableInfo) {
        final StringBuilder valueSql = new StringBuilder();
        valueSql.append("<trim prefix=\"set\" suffixOverrides=\",\">\n");
        tableInfo.getFieldList().forEach(x -> {
            valueSql.append("<trim prefix=\"").append(x.getColumn()).append(" =(case id\" suffix=\"end),\">\n");
            valueSql.append("<foreach collection=\"list\" item=\"item\" >\n");
            valueSql.append("<if test=\"item.").append(x.getProperty()).append("!=null\">\n");
            valueSql.append("when #{item.id} then #{item.").append(x.getProperty()).append("}\n");
            valueSql.append("</if>\n");
            valueSql.append("</foreach>\n");
            valueSql.append("else ").append(x.getColumn());
            valueSql.append("</trim>\n");
        });
        valueSql.append("</trim>\n");
        return valueSql.toString();
    }
}

2：注入器注入

/**
 * @Description 批量插入 SQL 注入器 在Mapper中生成insertBatchSomeColumn（必须是这个方法名）方法
 *              不使用自带的伪批量
 * @Author WangKun
 * @Date 2023/12/8 14:49
 * @Version
 */
public class BatchSqlInjector extends DefaultSqlInjector {
    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
        // super.getMethodList() 保留 Mybatis Plus 自带的方法
        List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
        // 添加自定义方法：批量插入，方法名为 insertBatchSomeColumn
        methodList.add(new InsertBatchSomeColumn());
        methodList.add(new InsertBatchMethod());
        methodList.add(new UpdateBatchMethod());

        return methodList;
    }

}

3：在mybatisplus配置中加入注入器

/**
 * @Description mybatis-plus配置
 * @Author WangKun
 * @Date 2023/4/4 15:01
 * @Version
 */
@Configuration
public class MybatisPlusConfig {


    /**
     * @param
     * @Description 分页
     * @Throws
     * @Return com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor
     * @Date 2023-04-11 16:46:11
     * @Author WangKun
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        PaginationInnerInterceptor innerInterceptor = new PaginationInnerInterceptor();
        innerInterceptor.setDbType(DbType.MYSQL);
        innerInterceptor.setOverflow(true);
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        interceptor.addInnerInterceptor(innerInterceptor);
        return interceptor;
    }

    /**
     * @param
     * @Description 新增是否以自增列为主键
     * @Throws
     * @Return com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer
     * @Date 2023-04-11 16:45:41
     * @Author WangKun
     */
    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return configuration -> configuration.setUseGeneratedShortKey(false);
    }

    /**
     * @param
     * @Description 开启返回map结果集的下划线转驼峰
     * @Throws
     * @Return com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer
     * @Date 2023-04-11 16:45:06
     * @Author WangKun
     */
    @Bean
    public ConfigurationCustomizer mybatisConfigurationCustomizer() {
        return configuration -> configuration.setObjectWrapperFactory(new MybatisMapWrapperFactory());
    }

    /**
     * @Description 批量插入数据
      * @param
     * @Throws
     * @Return com.harmonywisdom.common.mybatisplus.BatchSqlInjector
     * @Date 2023-12-08 14:51:43
     * @Author WangKun
     */
    @Bean
    public BatchSqlInjector batchSqlInjector() {
        return new BatchSqlInjector();
    }
}

4：定义mapper接口

/**
 * @Description 自定义批量加入
 * @Author WangKun
 * @Date 2023/12/8 15:35
 * @Version
 */
public interface BaseBatchMapper<T> extends BaseMapper<T> {

    /**
     * 真实批量加入
     * insertBatchSomeColumn 必须和注入的 InsertBatchSomeColumn 类的id是一样的
     * 否则容器中找不到对应实体，会报错：org.apache.ibatis.binding.BindingException: Invalid bound statement (not found)
     */
    long insertBatchSomeColumn(List<T> entityList);

    /**
     * 自定义批量新增，适用于mysql,并且数据已确定
     */
    int insertBatch(@Param("list") List<T> list);

    /**
     * 自定义批量更新，适用于mysql,并且数据已确定
     */
    int updateBatch(@Param("list") List<T> list);

}

5：业务层使用