Java EE+Oracle下用GTT规避Hibernate Criteria IN子句1000参数上限咨询

优化方案选型建议 #

两种方案中，拆分IN子句的简易方案适合临时救急，但IN列表过长会导致SQL硬解析频率升高、执行计划不稳定，无法解决你当前4万条数据查询耗时25秒的性能问题，GTT方案是最优选择。

GTT实现疑问解答 #

• 关于创建GTT是否需要索引：
  GTT属于全局定义、数据事务/会话隔离的表结构，只需要在应用首次启动时执行一次建表语句，不需要每次查询都动态创建。如果你的参数列表长度普遍超过100，建议在GTT的org_id列创建主键索引，和Company表关联查询时能大幅提升join性能。你之前写的if not exist建表逻辑可以放在项目启动的SQL脚本或者初始化代码中执行一次即可。
• 关于是否可以直接绑定列表参数批量插入：
  不能直接通过单条insert语句绑定整个列表参数，需要开启Hibernate的JDBC批处理能力，配置hibernate.jdbc.batch_size=50~100、hibernate.order_inserts=true后循环绑定参数批量插入，插入性能和单次插入上千条参数的IN子句相比开销可以忽略不计。
• 关于没有GTT实体类如何适配CriteriaQuery：
  GTT的表结构是固定的，你可以直接创建对应的实体类和元模型，和普通业务实体的用法完全一致，不需要额外做特殊适配。如果不想新增实体，也可以在CriteriaQuery中嵌入原生SQL片段关联GTT，但新增实体的方式维护成本更低、类型安全。
• 关于参数小于1000是否需要用GTT：
  建议设置阈值做分支切换，比如参数长度大于500走GTT逻辑，小于500走原有IN逻辑即可。GTT有数据插入的固定开销，小列表用IN的性能反而更优，你可以根据实际压测结果调整阈值，700~800参数的场景如果测试下来GTT性能和IN持平，也可以统一走GTT逻辑减少代码分支。

可运行实现示例 #

步骤1：预先创建GTT（执行一次即可） #

create global temporary table GTT_ORG_IDS (
    org_id varchar2(32) not null,
    constraint pk_gtt_org_ids primary key (org_id)
) ON COMMIT DELETE ROWS;

步骤2：创建GTT对应实体类 #

@Entity
@Table(name = "GTT_ORG_IDS")
public class GttOrgId {
    @Id
    @Column(name = "org_id", length = 32)
    private String orgId;

    public GttOrgId() {}
    public GttOrgId(String orgId) {
        this.orgId = orgId;
    }
    // 省略getter、setter
}

同时用Hibernate的元模型生成工具生成对应的GttOrgId_元模型类即可。

步骤3：改造现有业务方法 #

private static final int GTT_THRESHOLD = 500;

public List<ItemHistory> findByIdTypePermissionAndOrganizationIds(final Query<String> query, final ItemIdType idType) throws DataLookupException {
    String id = query.getObjectId();
    String type = idType.name();
    Set<String> companyIds = query.getCompanyIds();
    Set<String> allowedOrgIds = query.getAllowedOrganizationIds();
    Set<String> excludedOrgIds = query.getExcludedOrganizationIds();
    if (CollectionUtils.isEmpty(allowedOrgIds)) {
        return Collections.emptyList();
    }
    try {
        CriteriaBuilder builder = entityManager.getCriteriaBuilder();
        CriteriaQuery<ItemHistory> criteriaQuery = builder.createQuery(ItemHistory.class);
        Subquery<String> subQueryCompanyIds = criteriaQuery.subquery(String.class);
        Root<Company> companies = subQueryCompanyIds.from(Company.class);
        Path<String> orgIdColumn = companies.get(Company_.organizationId);

        // 构造组织ID过滤条件
        Predicate orgIdPredicate;
        if (companyIds.size() >= GTT_THRESHOLD) {
            // 批量插入参数到GTT
            entityManager.unwrap(Session.class).setJdbcBatchSize(100);
            for (String orgId : companyIds) {
                entityManager.persist(new GttOrgId(orgId));
            }
            entityManager.flush();
            // 构造GTT关联查询条件
            Subquery<String> gttSubquery = criteriaQuery.subquery(String.class);
            Root<GttOrgId> gttRoot = gttSubquery.from(GttOrgId.class);
            gttSubquery.select(gttRoot.get(GttOrgId_.orgId));
            orgIdPredicate = builder.in(orgIdColumn).value(gttSubquery);
        } else {
            // 小参数走原有IN逻辑
            orgIdPredicate = groupByWildcardsAndCombine(builder, query.getCompanyIds(), orgIdColumn, false);
        }

        // 原有权限过滤逻辑保留
        Predicate permissionPredicate = getCompanyIdRangeByPermission(
                builder, allowedOrgIds, excludedOrgIds, orgIdColumn
        );
        Predicate subqueryWhere = CriteriaQueryUtils.joinWith(builder, true, permissionPredicate, orgIdPredicate);
        subQueryCompanyIds.select(companies.get(Company_.id));
        if (subqueryWhere != null) {
            subQueryCompanyIds.where(subqueryWhere);
        }

        // 主查询逻辑保留
        Root<ItemHistory> itemHistory = criteriaQuery.from(ItemHistory.class);
        criteriaQuery.select(itemHistory)
                .where(builder.and(
                        builder.equal(getColumnByIdType(itemHistory, idType), id),
                        builder.in(itemHistory.get(ItemHistory_.companyId)).value(subQueryCompanyIds)
                ))
                .orderBy(builder.desc(itemHistory.get(ItemHistory_.eventDate)));
        TypedQuery<ItemHistory> finalQuery = entityManager.createQuery(criteriaQuery);
        return finalQuery.setMaxResults(MAX_LIST_FETCH_SIZE).getResultList();
    } catch (NoResultException e) {
        LOGGER.info("No item history events found by permission and org ids with {}={}", type, id);
        throw new DataLookupException(ErrorCode.DATA_LOOKUP_NO_RESULT);
    } catch (Exception e) {
        LOGGER.error("Error when fetching item history events by permission and org ids with {}={}", type, id, e);
        throw new DataLookupException(ErrorCode.DATA_LOOKUP_ERROR,
                "Error when fetching item history events by permission and org ids with " + type + "=" + id);
    }
}

临时救急方案：IN子句拆分工具 #

如果暂时不想引入GTT，可以用以下工具方法拆分IN子句规避1000参数限制：

public static <T> Predicate buildSplitInPredicate(CriteriaBuilder builder, Path<T> path, Collection<T> values) {
    int chunkSize = 999;
    List<Predicate> inPredicates = new ArrayList<>();
    List<T> valueList = new ArrayList<>(values);
    for (int i = 0; i < valueList.size(); i += chunkSize) {
        int endIdx = Math.min(valueList.size(), i + chunkSize);
        inPredicates.add(path.in(valueList.subList(i, endIdx)));
    }
    return builder.or(inPredicates.toArray(new Predicate[0]));
}

直接替换原有IN逻辑即可，无需改动其他代码。

额外注意事项 #

• 整个查询逻辑必须在同一个事务内执行，因为你选择的是ON COMMIT DELETE ROWS，事务提交后GTT的数据会自动清空，不会出现数据残留。
• GTT实体不要加任何缓存注解，避免不同事务的数据互相干扰。

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