spring-data-jpa使用oracle作为数据源时in查询在1000条以上 ，报ORA_01795的错误

spring-data-jpa使用oracle作为数据源时in查询在1000条以上 ，报ORA_01795的错误
原文连接： https://segmentfault.com/a/1190000041913304?sort=votes

目的

解决项目中使用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的查询执行上）