Oracle ORA-01795错误：JPA动态IN列表超限的动态解决方案需求

针对Oracle数据库IN列表最多支持1000个元素的限制，以下是几种无需手动拆分、可适配任意大小列表的解决方案：

方案1：使用会话级临时表关联查询 #

会话级临时表的数据仅在当前会话有效，会话结束后自动清理，适合处理超大列表的查询场景。

步骤： #

1. 在Oracle中创建会话级临时表：

CREATE GLOBAL TEMPORARY TABLE TEMP_MSISDN (
    MSISDN VARCHAR2(20) PRIMARY KEY
) ON COMMIT DELETE ROWS;

2. 在JPA Repository中添加批量插入和查询方法：

// 批量插入msisdn到临时表
@Modifying
@Query(value = "INSERT INTO TEMP_MSISDN (MSISDN) VALUES (?1)", nativeQuery = true)
void batchInsertMsisdn(String msisdn);

// 关联临时表查询
@Query(value = "SELECT b.* FROM BILL_INFO_DETAILS b " +
               "JOIN TEMP_MSISDN tm ON b.MSISDN = tm.MSISDN " +
               "WHERE b.MASTER_ACCT_CODE IN :masterAccountList", nativeQuery = true)
List<BillInfoDetails> findByMasterAcctAndTempMsisdn(@Param("masterAccountList") List<String> masterAccountList);

3. 业务代码中调用（需开启事务）：

@Transactional
public List<BillInfoDetails> queryBillInfo(List<String> masterAccountList, List<String> msisdnList) {
    // 批量插入临时表
    msisdnList.forEach(msisdn -> billInfoDetailsRepository.batchInsertMsisdn(msisdn));
    // 执行关联查询
    return billInfoDetailsRepository.findByMasterAcctAndTempMsisdn(masterAccountList);
}

方案2：利用Oracle TABLE函数+自定义数组类型 #

通过Oracle的数组类型和TABLE函数，将Java列表转换为数据库可识别的集合，避免IN列表长度限制。

步骤： #

1. 在Oracle中定义字符串数组类型：

CREATE OR REPLACE TYPE STRING_ARRAY AS TABLE OF VARCHAR2(20);

2. 在JPA中注册该数组类型（以Hibernate为例）：

public class StringArrayType implements UserType {
    @Override
    public int[] sqlTypes() {
        return new int[]{Types.ARRAY};
    }

    @Override
    public Class<?> returnedClass() {
        return List.class;
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws SQLException {
        Array array = rs.getArray(names[0]);
        return array == null ? Collections.emptyList() : Arrays.asList((String[]) array.getArray());
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws SQLException {
        if (value == null) {
            st.setNull(index, Types.ARRAY, "STRING_ARRAY");
            return;
        }
        List<String> list = (List<String>) value;
        Array array = session.connection().createArrayOf("STRING_ARRAY", list.toArray());
        st.setArray(index, array);
    }

    // 实现equals、hashCode、deepCopy等剩余UserType方法
}

3. 编写JPA查询：

@Query(value = "SELECT b FROM BillInfoDetails b " +
               "WHERE b.masterAcctCode IN :masterAccountList " +
               "AND b.msisdn IN (SELECT COLUMN_VALUE FROM TABLE(:msisdnArray))", nativeQuery = false)
List<BillInfoDetails> findAllByMsisdnAndMasterAcctList(
    @Param("masterAccountList") List<String> masterAccountList,
    @Param("msisdnArray") List<String> msisdnList);

方案3：JPA Criteria API自动拆分IN列表 #

通过代码自动将大列表拆分为多个不超过1000元素的子列表，用OR连接多个IN条件，纯JPA层面处理，无需修改数据库。

实现代码： #

public List<BillInfoDetails> queryWithSplitIn(List<String> masterAccountList, List<String> msisdnList) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<BillInfoDetails> cq = cb.createQuery(BillInfoDetails.class);
    Root<BillInfoDetails> root = cq.from(BillInfoDetails.class);

    // 处理masterAcctCode的IN条件
    Predicate masterAcctPredicate = root.get("masterAcctCode").in(masterAccountList);

    // 拆分msisdnList为多个子列表，每个最多1000元素
    List<List<String>> splitMsisdnLists = splitList(msisdnList, 1000);
    List<Predicate> msisdnPredicates = new ArrayList<>();
    for (List<String> subList : splitMsisdnLists) {
        msisdnPredicates.add(root.get("msisdn").in(subList));
    }
    Predicate msisdnPredicate = cb.or(msisdnPredicates.toArray(new Predicate[0]));

    // 组合条件并查询
    cq.where(cb.and(masterAcctPredicate, msisdnPredicate));
    return entityManager.createQuery(cq).getResultList();
}

// 自定义列表拆分工具方法
private <T> List<List<T>> splitList(List<T> list, int batchSize) {
    List<List<T>> result = new ArrayList<>();
    for (int i = 0; i < list.size(); i += batchSize) {
        int end = Math.min(i + batchSize, list.size());
        result.add(list.subList(i, end));
    }
    return result;
}

内容的提问来源于stack exchange，提问作者Sadi