ORM-mybatis plus之批量新增扩展

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

最近发现公司的微服务项目中没有统一的批量新增方法，公司用的是MP插件，遇到批量新增都是单独去去编写xml实现，费时费力，而MP自带的批插方法只是实现了分批条sql，跟真正意义上一条sql实现批插还是有很大的性能差异，所以决定实现一个统一的批插方法。

---

一、MP如何扩展批量新增方法？

MP给我们预留了一个可以真正实现批插的插件InsertBatchSomeColumn，但是只是针对mysql数据库的，我们可以参照它稍加改造扩展一个针对oracle或者其他数据库的批插插件类，然后通过sql注入器的方式注入并初始化，就可以实现真正的批插了。

二、实现步骤

1.扩展批插类

代码如下（示例）：

public class InsertBatchSomeColumnOracle extends AbstractMethod {

    /**
     * 字段筛选条件
     */
    @Setter
    @Accessors(chain = true)
    private Predicate<TableFieldInfo> predicate;

    @SuppressWarnings("Duplicates")
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        KeyGenerator keyGenerator = new NoKeyGenerator();
        SqlMethod sqlMethod = SqlMethod.INSERT_ALL;
        List<TableFieldInfo> fieldList = tableInfo.getFieldList();
        String insertSqlColumn = tableInfo.getKeyInsertSqlColumn(false) +
                this.filterTableFieldInfo(fieldList, predicate, TableFieldInfo::getInsertSqlColumn, EMPTY);
        String tableName = tableInfo.getTableName();
        String columnScript = LEFT_BRACKET + insertSqlColumn.substring(0, insertSqlColumn.length() - 1) + RIGHT_BRACKET;
        String insertSqlProperty = tableInfo.getKeyInsertSqlProperty(ENTITY_DOT, false) +
                this.filterTableFieldInfo(fieldList, predicate, i -> i.getInsertSqlProperty(ENTITY_DOT), EMPTY);
        insertSqlProperty = " into " + tableName + columnScript + " values " + LEFT_BRACKET + insertSqlProperty.substring(0, insertSqlProperty.length() - 1) + RIGHT_BRACKET;
        String batchScript = SqlScriptUtils.convertForeach(insertSqlProperty, "list", null, ENTITY, EMPTY);
        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(), tableInfo, builderAssistant);
                    keyProperty = tableInfo.getKeyProperty();
                    keyColumn = tableInfo.getKeyColumn();
                }
            }
        }
        String sql = String.format(sqlMethod.getSql(), batchScript);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
        return this.addInsertMappedStatement(mapperClass, modelClass, getMethod(), sqlSource, keyGenerator, keyProperty, keyColumn);
    }

    public String getMethod() {
        // 自定义 mapper 方法名
        return "insertBatchSomeColumnOracle";
    }
}

2.编写sql注入器

代码如下（示例）：

public class MpSqlInjector extends DefaultSqlInjector {

    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
        // 防止父类的方法不可使用
        List<AbstractMethod> methodList = super.getMethodList(mapperClass);
        // 添加批量插入的方法
        methodList.add(new InsertBatchSomeColumn());
        methodList.add(new InsertBatchSomeColumnOracle());
        return methodList;
    }
}

3.spring容器中实例化sql注入器

代码如下（示例）：

@Bean
public MpSqlInjector mpSqlInjector() {
    return new MpSqlInjector();
}

3.业务代码实现

代码如下（示例）：

public enum SqlMethod {
    /**
     * 插入
     */
    INSERT_ONE("insert", "插入一条数据（选择字段插入）", "<script>\nINSERT INTO %s %s VALUES %s\n</script>"),
    UPSERT_ONE("upsert", "Phoenix插入一条数据（选择字段插入）", "<script>\nUPSERT INTO %s %s VALUES %s\n</script>"),
    INSERT_ALL("insertAll", "批量插入（选择字段插入 oracle）", "<script>\nINSERT ALL %s SELECT 1 FROM DUAL\n</script>"),

    /**
     * 删除
     */
    DELETE_BY_ID("deleteById", "根据ID 删除一条数据", "<script>\nDELETE FROM %s WHERE %s=#{%s}\n</script>"),
    DELETE_BY_MAP("deleteByMap", "根据columnMap 条件删除记录", "<script>\nDELETE FROM %s %s\n</script>"),
    DELETE("delete", "根据 entity 条件删除记录", "<script>\nDELETE FROM %s %s %s\n</script>"),
    DELETE_BATCH_BY_IDS("deleteBatchIds", "根据ID集合，批量删除数据", "<script>\nDELETE FROM %s WHERE %s IN (%s)\n</script>"),

    /**
     * 逻辑删除
     */
    LOGIC_DELETE_BY_ID("deleteById", "根据ID 逻辑删除一条数据", "<script>\nUPDATE %s %s WHERE %s=#{%s} %s\n</script>"),
    LOGIC_DELETE_BY_MAP("deleteByMap", "根据columnMap 条件逻辑删除记录", "<script>\nUPDATE %s %s %s\n</script>"),
    LOGIC_DELETE("delete", "根据 entity 条件逻辑删除记录", "<script>\nUPDATE %s %s %s %s\n</script>"),
    LOGIC_DELETE_BATCH_BY_IDS("deleteBatchIds", "根据ID集合，批量逻辑删除数据", "<script>\nUPDATE %s %s WHERE %s IN (%s) %s\n</script>"),

    /**
     * 修改
     */
    UPDATE_BY_ID("updateById", "根据ID 选择修改数据", "<script>\nUPDATE %s %s WHERE %s=#{%s} %s\n</script>"),
    UPDATE("update", "根据 whereEntity 条件，更新记录", "<script>\nUPDATE %s %s %s %s\n</script>"),

    /**
     * 逻辑删除 -> 修改
     */
    LOGIC_UPDATE_BY_ID("updateById", "根据ID 修改数据", "<script>\nUPDATE %s %s WHERE %s=#{%s} %s\n</script>"),

    /**
     * 查询
     */
    SELECT_BY_ID("selectById", "根据ID 查询一条数据", "SELECT %s FROM %s WHERE %s=#{%s} %s"),
    SELECT_BY_MAP("selectByMap", "根据columnMap 查询一条数据", "<script>SELECT %s FROM %s %s\n</script>"),
    SELECT_BATCH_BY_IDS("selectBatchIds", "根据ID集合，批量查询数据", "<script>SELECT %s FROM %s WHERE %s IN (%s) %s </script>"),
    SELECT_ONE("selectOne", "查询满足条件一条数据", "<script>%s SELECT %s FROM %s %s %s\n</script>"),
    SELECT_COUNT("selectCount", "查询满足条件总记录数", "<script>%s SELECT COUNT(%s) FROM %s %s %s\n</script>"),
    SELECT_LIST("selectList", "查询满足条件所有数据", "<script>%s SELECT %s FROM %s %s %s %s\n</script>"),
    SELECT_PAGE("selectPage", "查询满足条件所有数据（并翻页）", "<script>%s SELECT %s FROM %s %s %s %s\n</script>"),
    SELECT_MAPS("selectMaps", "查询满足条件所有数据", "<script>%s SELECT %s FROM %s %s %s %s\n</script>"),
    SELECT_MAPS_PAGE("selectMapsPage", "查询满足条件所有数据（并翻页）", "<script>\n %s SELECT %s FROM %s %s %s %s\n</script>"),
    SELECT_OBJS("selectObjs", "查询满足条件所有数据", "<script>%s SELECT %s FROM %s %s %s %s\n</script>");

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

    SqlMethod(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;
    }

}

public interface MyBaseMapper<T> extends BaseMapper<T> {

    /**
     * 批量插入 (mysql)
     * @param entityList 实体列表
     * @return 影响行数
     */
    int insertBatchSomeColumn(Collection<T> entityList);

    /**
     * 批量插入 (oracle)
     * @param entityList 实体列表
     * @return 影响行数
     */
    int insertBatchSomeColumnOracle(Collection<T> entityList);
}

/**
     * 批量插入记录（选择字段，策略插入）
     *
     * @param entityList 实体对象集合
     */
    public void saveBatch(Collection<T> entityList) {
        if (DEFAULT_MUST_SAVE_BATCH) {
            ServiceAssert.isTrue(CollectionUtil.isNotEmpty(entityList), "批量插入传入空集合， 插入失败");
        }
        if (CollectionUtil.isEmpty(entityList)) {
            return;
        }
        int res;
        if (DATABASE_TYPE_ORACLE.equalsIgnoreCase(dbType)) {
            res = getBaseMapper().insertBatchSomeColumnOracle(entityList);
        } else {
            res = getBaseMapper().insertBatchSomeColumn(entityList);
        }
        ServiceAssert.isTrue(res == entityList.size(), "插入失败，请联系管理员");
    }

    /**
     * 批量插入记录（选择字段，策略插入）
     *
     * @param entityList 实体对象集合
     * @param batchSize 每批次插入数
     */
    public void saveBatch(Collection<T> entityList, int batchSize) {
        if (DEFAULT_MUST_SAVE_BATCH) {
            ServiceAssert.isTrue(CollectionUtil.isNotEmpty(entityList), "批量插入传入空集合， 插入失败");
        }
        if (CollectionUtil.isEmpty(entityList)) {
            return;
        }
        int size = entityList.size();
        int idxLimit = Math.min(batchSize, size);
        int i = 1;
        //保存单批提交的数据集合
        List<T> batchList = new ArrayList<>();
        for(Iterator<T> it = entityList.iterator(); it.hasNext(); ++i) {
            T element = it.next();
            batchList.add(element);
            int res;
            if (i == idxLimit) {
                if (DATABASE_TYPE_ORACLE.equalsIgnoreCase(dbType)) {
                    res = getBaseMapper().insertBatchSomeColumnOracle(batchList);
                } else {
                    res = getBaseMapper().insertBatchSomeColumn(batchList);
                }
                //每次提交后需要清空集合数据
                batchList.clear();
                idxLimit = Math.min(idxLimit + batchSize, size);
                ServiceAssert.isTrue(res == entityList.size(), "插入失败，请联系管理员");
            }
        }
    }

---

总结

遇到问题可以看看源码，说不定作者已经给你留好路了。