Mybatis-plus解决兼容oracle批量插入

本博客借鉴网上很多大佬的答案，东拼西凑，最终在项目中完成批量插入，仅供参考~~~

1. 自定义SQL注入器

新建一个名为EasySqlInjector的类，继承DefaultSqlInjector。

public class EasySqlInjector extends DefaultSqlInjector {@Overridepublic List<AbstractMethod> getMethodList(Class<?> mapperClass) {// 注意：此SQL注入器继承了DefaultSqlInjector(默认注入器)，调用了DefaultSqlInjector的getMethodList方法，保留了mybatis-plus的自带方法List<AbstractMethod> methodList = super.getMethodList(mapperClass);methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));return methodList;}}

2. 将SQL注入器交给Spring容器

在MybatisPlusConfig类中，将刚才创建的SQL注入器EasySqlInjector,注册为一个bean。

@EnableTransactionManagement(proxyTargetClass = true)
@Configuration
public class MybatisPlusConfig
{@Beanpublic PaginationInterceptor paginationInterceptor() {PaginationInterceptor paginationInterceptor = new PaginationInterceptor();// 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求 默认false// paginationInterceptor.setOverflow(false);// 设置最大单页限制数量，默认 500 条，-1 不受限制// paginationInterceptor.setLimit(500);// 开启 count 的 join 优化,只针对部分 left joinpaginationInterceptor.setLimit(-1);
//        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));return paginationInterceptor;}/*** 分页插件，自动识别数据库类型 https://baomidou.com/guide/interceptor-pagination.html*/public PaginationInnerInterceptor paginationInnerInterceptor(){PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();// 设置数据库类型为mysqlpaginationInnerInterceptor.setDbType(DbType.ORACLE);// 设置最大单页限制数量，默认 500 条，-1 不受限制paginationInnerInterceptor.setMaxLimit(-1L);return paginationInnerInterceptor;}/*** 乐观锁插件 https://baomidou.com/guide/interceptor-optimistic-locker.html*/public OptimisticLockerInnerInterceptor optimisticLockerInnerInterceptor(){return new OptimisticLockerInnerInterceptor();}/*** 如果是对全表的删除或更新操作，就会终止该操作 https://baomidou.com/guide/interceptor-block-attack.html*/public BlockAttackInnerInterceptor blockAttackInnerInterceptor(){return new BlockAttackInnerInterceptor();}@Beanpublic EasySqlInjector sqlInjector() {return new EasySqlInjector();}}

3. 配置EasyBaseMapper继承BaseMapper

新建EasyBaseMapper类，继承BaseMapper，并在此类中配置insertBatchSomeColumn()方法。

/

public interface  EasyBaseMapper<T> extends BaseMapper<T> {/*** @param entityList 实体列表*/void insertBatchSomeColumn(Collection<T> entityList);
}

4.自定义Mybatis拦截器OracleSqlInterceptor

   这个地方要注意,表的主键我用触发器已经自动填入，所以keyGenerator设置为NoKeyGenerator.INSTANCE,这个地方有个坑，不那么设置，SQL一直报错，折腾了两个小时，实际上拼接的SQL没问题

@Component
@Slf4j
@Order(1)
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class OracleSqlInterceptor implements Interceptor {@Overridepublic Object intercept(Invocation invocation) throws Throwable {//当前业务，兼容pg 和 oracle，需要兼容oracle的批量插入语句StatementHandler statementHandler = (StatementHandler) invocation.getTarget();BoundSql boundSql = statementHandler.getBoundSql();String sql = boundSql.getSql();StatementHandler delegate = (StatementHandler) ReflectUtil.getFieldValue(statementHandler, "delegate");MappedStatement mappedStatement = (MappedStatement) ReflectUtil.getFieldValue(delegate, "mappedStatement");ReflectUtil.setFieldValue(mappedStatement,"keyGenerator", NoKeyGenerator.INSTANCE);String mName  = mappedStatement.getId().substring(mappedStatement.getId().lastIndexOf(".") + 1);if("insertBatchSomeColumn".equals(mName)){//开始兼容批量插入语句,并设置boundSqlField declaredField = boundSql.getClass().getDeclaredField("sql");declaredField.setAccessible(true);declaredField.set(boundSql, convertOracleInsertSql(sql));log.info("---转换后的sql为：{}", boundSql.getSql());}return invocation.proceed();}/*** Oracle Insert语句转化** @param sql 传入的pg的sql* @return 转化后的sql*/public String convertOracleInsertSql(String sql) {//用oracle中的批量语句代替//查找values的位置，将后面全部括号里的东西取出，然后再用对应的数据进行封装//获取前面的sql，这段sql与Oracle的相同String prefix = sql.substring(0, getKeywordValueIndex(sql));//排除table中的括号，取后面的括号String subSql = sql.substring(getKeywordValueIndex(sql));String valueSql = subSql.substring(subSql.indexOf("("));List<String> valueList = getValues(valueSql);//拼接sqlStringBuilder sqlBuilder = new StringBuilder().append(prefix);//sqlBuilder.append("SELECT A.* FROM (");String selectValue = "SELECT ";String endValue = " FROM DUAL ";String unionValue = "UNION ALL ";boolean start = true;for (String value : valueList) {if (!start) {sqlBuilder.append(unionValue);}else {start = false;}sqlBuilder.append(selectValue).append(value).append(endValue);}//sqlBuilder.append(") A");return sqlBuilder.toString();}/*** 使用栈实现获取value中括号的值**/public List<String> getValues(String sql) {List<String> values = new ArrayList<>();Stack<Character> brackets = new Stack<>();StringBuilder splitValue = new StringBuilder();for (Character c : sql.toCharArray()) {if ('(' == c) {//左括号进栈brackets.push(c);}else if (')' == c) {//右括号则将左括号出栈,清空builderbrackets.pop();values.add(splitValue.toString());splitValue.delete(0, splitValue.length());}else if (!brackets.empty()) {//只有进入括号中才将值放入，排除括号外的逗号splitValue.append(c);}}return values;}/*** 查找关键字value的位置*/public  int getKeywordValueIndex(String sql) {//先找values，再找valueif (sql.contains("values")) {return sql.indexOf("values");}else if (sql.contains("VALUES")) {return sql.indexOf("VALUES");}else if (sql.contains("value")) {return sql.indexOf("value");}else {return sql.indexOf("VALUE");}}}

然后，用业务Mapper继承EasyBaseMapper就可以调用insertBatchSomeColumn()方法了。