关于java:springdatajpa使用oracle作为数据源时in查询在1000条以上-报ORA01795的错误

目标

解决我的项目中应用 spring-data-jpa，采纳oracle 11g 作为数据源时，当 in 查问前面的条件超过 1000 条后，oracle报 ORA_01795 的异样。

思路

问题在于以后版本的 oracle 不反对单个 in 查问超过 1000 的情景，思路是通过 jpa 提供给咱们的有实体类生成 SQL 后、未执行前的拦截器，对生成的 SQL 进行判断，如果存在上述的超过 1000 的情景，将 SQL 拆分成多个 in 的组合、通过 or 连贯。
例如：

# jpa 转换的 SQL 通常是如下模式，如果 in (?,?,?....)有超过 1000 个？select id,name,gender from user where id in(? ,? ,? ,? ,? ,? ,? ,? ,? ,?, ?);
# 将其拆分
select id,name,gender from user where (id in(? ,? ,? ,? ,? ,?) or id in (,? ,? ,?)  or id in(?, ?));

代码

package com.orient.collab.provider.common.jpa.interceptor;

import org.hibernate.resource.jdbc.spi.StatementInspector;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.autoconfigure.orm.jpa.HibernatePropertiesCustomizer;
import org.springframework.context.annotation.Configuration;

import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * description:
 * when using oracle as data source, custom a sql statement interceptor to solve "select or delete with'in' " limit 1000 cause !!!
 * !!! 不反对 not in
 * @author MorningSun
 * @version 1.0
 * @since JDK1.8
 * date 2022/4/27 19:16
 */
@Configuration
@ConditionalOnProperty(name = "spring.datasource.driver-class-name", havingValue = "oracle.jdbc.driver.OracleDriver")
public class JPAConfigurationOnUsingOracle implements HibernatePropertiesCustomizer {

    /**
     *         StringBuilder sql = new StringBuilder("select *.* from . . where *.* in(");
     *         for (int i = 0; i < 999; i++) {*             sql.append("? ,");
     *         }
     *         sql.append("?)");
     *         System.out.println(sql.length());//4033
     */
    private static final int SQL_THRESHOLD_LENGTH = 4033;// 超过这个才去匹配
    private final static int IN_CAUSE_LIMIT = 1000;
    private final static Pattern pattern = Pattern.compile("[^(^\\()\\s]+\\s+in\\s*\\(+(\\?\\s*,\\s*){"+(IN_CAUSE_LIMIT - 2) +",}+\\?\\)", Pattern.CASE_INSENSITIVE);

    @Override
    public void customize(Map<String, Object> hibernateProperties) {hibernateProperties.put("hibernate.session_factory.statement_inspector", new StatementInspector(){

            @Override
            public String inspect(String sql) {if (sql.length() <= SQL_THRESHOLD_LENGTH){return sql;}

                return this.rewriteSqlToAvoidORA_01795(sql);
            }

            private String rewriteSqlToAvoidORA_01795(String sql) {//                long startTime = System.currentTimeMillis();
                Matcher matcher = pattern.matcher(sql);
                while (matcher.find()) {String inExpression = matcher.group();
                    long countOfParameters = inExpression.chars().filter(ch -> ch == '?').count();
                    String fieldName = inExpression.substring(0, inExpression.indexOf(' '));
                    StringBuilder transformedInExpression = new StringBuilder("(").append(fieldName).append("in (");
                    for (int i = 0; i < countOfParameters; i++) {if (i != 0 && i % IN_CAUSE_LIMIT == 0) {
                            transformedInExpression
                                    .deleteCharAt(transformedInExpression.length() - 1)
                                    .append(") or").append(fieldName).append("in (");
                        }
                        transformedInExpression.append("?,");
                    }
                    transformedInExpression.deleteCharAt(transformedInExpression.length() - 1).append("))");
                    sql = sql.replaceFirst(Pattern.quote(inExpression), transformedInExpression.toString());
                }
//                long endTime = System.currentTimeMillis();
//                System.out.println("拆分 sql 耗时："+(endTime - startTime)+"ms");//2ms
                return sql;
            }
        });
    }

}

问题

• 不反对 not in, 思路其实一样的，not in 须要通过and 连贯，该场景更少，故不作思考；

• 对于性能：

  • 但凡应用了 jpa 的dao层操作，均会走到该拦截器，所有基于 jpa 生成的 sql 的特色定义一个 sql 长度的阈值，生成的 sql 的length如果小于该阈值，间接返回（理论这个根本曾经排除了绝大部分的场景）；
  • 对于正则匹配，可能有多种写法，因为不足对底层的相干机制如：回溯 、 贪婪算法 等理解，尚有优化的中央（本人试着写了几种不同的模式测了一下，耗时都在 1、2ms，所以可能呈现的性能瓶颈可能不是 sql 的转化上，更多的是 sql 的查问执行上）