Mybatis Plus批量插入获取序列值回写主键优化(Oracle)

由于MybatisPlus对Oracle批量插入支持很差(不能自动获取序列值返回，SQL语句拼写出错)，同样是不支持主键自增，Pgsql支持就比较好，可能时oracle sql语法特殊，然后作者又没有专门对其优化。因此项目中使用oracle批量插入还是遇到了问题。
上篇文章介绍了通过sql注入器的方式可以拼写成正确的插入语句，但是主键通过序列自增回显的问题还存在。上篇文章之所以能插入成功，是因为框架内获取主键是通过ASSIN_ID的方式，也就是雪花算法获取主键值，长度19位。所以本篇文章介绍下如何解决通过序列自增的方式插入并回写主键。

一.实现方式

1. 不要求回写主键的情况

这种情况下可以插入语句时获取序列值的方式：

<insert id="addByUser" parameterType="java.util.List" useGeneratedKeys="false">
        insert into ts_app_dxdz(ID, USERID,TYPEID, PZID, ZUID,DQID)
        select seq_ts_app_dxdz_id.nextval,cd.* from(
        <foreach collection="list" item="item" index="index" separator="union all">
            select
            #{item.USERID} USERID,
            #{item.TYPEID} TYPEID,
            #{item.PZ} PZ,
            #{item.ZU} ZU,
            #{item.DQ} DQ
            from dual
        </foreach>
        ) cd
    </insert>

2.回写主键的情况

这种情况我的做法是先批量获取主键值，再手动赋值，进行插入

2.1 批量获取序列值

    <select id="getSeqList" parameterType="int" resultType="java.lang.Long">
        SELECT SEQ_ZL_ZLFLB_ID.nextval FROM (select level from dual connect by level &lt;= #{size})
    </select>

2.2 插入

INSERT ALL
INTO emp (id, empno, ename) VALUES (1, 1001, '张三')
INTO emp (id, empno, ename) VALUES (2, 1002, '李四')
INTO emp (id, empno, ename) VALUES (3, 1003, '王五')
SELECT * FROM dual;

把下面的插入语句编写成xml格式即可

二.具体实现

我这里是采用第二种方式，因为很多情况都需要获取插入后的主键值。我采用注入器方式，全局注入了两个方法：1.批量获取序列，2.插入操作

1.批量获取序列

public class MySelectSeqBatch  extends AbstractMethod {

    @Override
    public String getMethod(SqlMethod sqlMethod) {
        // 自定义 mapper 方法名
        return "selectSeqBatch";
    }

    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        if (tableInfo.getEntityType().equals(Map.class)||null==tableInfo.getKeySequence()) {
            return null;
        }
        final String sql = "<script>SELECT %s.nextval FROM (select level from dual connect by level &lt;= #{size})</script>";
        String sequence = tableInfo.getKeySequence().value();
        final String sqlResult = String.format(sql, sequence);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);

        return this.addSelectMappedStatementForOther(mapperClass, "selectSeqBatch", sqlSource, Long.class);
    }
}

2.插入操作

这里我对批量插入进行了一个优化成和单条插入一样可以过滤掉null值(适合数据库字段有默认值的情况)

public class MyInsertBatchSomeColumn 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;
        }
        //return super.injectMappedStatement(mapperClass,modelClass,tableInfo);
        KeyGenerator keyGenerator = new NoKeyGenerator();
        SqlMethod sqlMethod = SqlMethod.INSERT_ONE;
        List<TableFieldInfo> fieldList = tableInfo.getFieldList();

        String insertSqlColumn = tableInfo.getKeyColumn() +
                dynamicField(fieldList, predicate, TableFieldInfo::getInsertSqlColumn);
        String columnScript = LEFT_BRACKET + insertSqlColumn + RIGHT_BRACKET;

        String keyInsertSqlProperty = tableInfo.getKeyInsertSqlProperty(ENTITY_DOT, false);
        if(keyInsertSqlProperty.length()>0){
            keyInsertSqlProperty=keyInsertSqlProperty.substring(0,keyInsertSqlProperty.length()-1);
        }
        String insertSqlProperty = keyInsertSqlProperty+
                dynamicField(fieldList, predicate, i -> i.getInsertSqlProperty(ENTITY_DOT));

        insertSqlProperty=LEFT_BRACKET + insertSqlProperty + RIGHT_BRACKET;
        String valuesScript = convertForeach(insertSqlProperty, "list", tableInfo.getTableName(),columnScript, 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(columns).append(" VALUES ");
        }

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

    private String dynamicField(List<TableFieldInfo> fieldList, Predicate<TableFieldInfo> predicate,
                                Function<TableFieldInfo, String> function){


        StringBuilder sb=new StringBuilder();
        for (TableFieldInfo fieldInfo : fieldList) {
            if(predicate.test(fieldInfo)&&fieldInfo.getInsertStrategy()== FieldStrategy.NOT_NULL){
                String columnProperty = fieldInfo.getProperty();
                String apply = function.apply(fieldInfo);
                sb.append(" <if test=\"").append("et.").append(columnProperty).append(" != null\">");
                sb.append(",").append(apply,0,apply.length()-1).append("</if>");
            }

        }
        return sb.toString();
    }
}

3.插入工具类编写

这里我把获取序列值和插入操作聚合成一个操作，方便使用，使用时只需传入数据集合和对应mapper

public class DBUtils {

    /**
     * 批量插入
     * @param list
     * @param consumer
     * @param <T>
     */
    public static <T> void insertBatchGroup(List<T> list, int batchSize, Consumer<List<T>> consumer){
        int listSize = list.size();
        for (int i = 0; i < listSize; i += batchSize) {
            int endIndex = Math.min(i + batchSize, listSize);
            List<T> subList = list.subList(i, endIndex);
            // 执行操作
            consumer.accept(subList);
        }
    }

    public static <T> void insertBatch(List<T> list, MyBaseMapper<T> baseMapper)  {
        Assert.notEmpty(list,"A collection of bulk inserted data cannot be empty");
        TableInfo tableInfo = TableInfoHelper.getTableInfo(list.get(0).getClass());

        //获取并设置主键
        if(null!=tableInfo.getKeySequence()){
            List<Long> seqList = baseMapper.selectSeqBatch(list.size());
            for (T t : list) {
                Class<?> cclass = list.get(0).getClass();
                try {
                    Field keyField= cclass.getDeclaredField(tableInfo.getKeyColumn());
                    keyField.setAccessible(true);
                    keyField.set(t,seqList.get(list.indexOf(t)));
                }catch (Exception e){
                    throw new RuntimeException("key sequence set error");
                }
            }
        }
        insertBatchGroup(list,100, baseMapper::insertBatchSomeColumn);;
    }
}

总结

写工具类或者全局的方法就需要考虑的更多，对开发技术和思想要求更高，同时逼迫自己阅读更多的阅读源码。