JPA2 Specification多列去重查询问题：Oracle报ORA-00909错误

嘿，我明白你遇到的问题了——Oracle不支持COUNT(DISTINCT column1, column2)这种多列参数的写法，而Hibernate在处理分页的count查询时自动生成了这条错误的SQL，才导致抛出ORA-00909。你之前试的query.distinct(true)没生效，大概率是没正确处理查询的返回字段，或者分页场景下的count查询需要单独定制。

下面分两种常见场景给你解决方案：

场景1：不需要分页，直接拿去重后的实体列表 #

如果你的查询不需要分页，只是想基于column1和column2去重获取实体数据，直接在Specification里开启distinct(true)就行，不用手动改select为拼接列（除非你只需要这两个列的结果）：

修改你的toPredicate方法，加上query.distinct(true)：

return new Specification<ProfilingInstructionAccessEntity>() {
    @Override
    public Predicate toPredicate(Root<ProfilingInstructionAccessEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        final List<Predicate> predicates = new ArrayList<>();
        List<FilterDTO> activeFilters = filtersService.findAllActive();
        
        filters.forEach((k, v) -> {
            if (!StringUtils.isBlank(v)) {
                SpecificationHelperEnum helperEnum = SpecificationHelperEnum.getByKey(k);
                SpecFilter filter = (SpecFilter) appContext.getBean(helperEnum.getFilter());
                // 开启行级去重，等价于SQL里的SELECT DISTINCT *
                query.distinct(true);
                
                Predicate predicate = filter.createSmartPredicate(root, cb, v, helperEnum.getFilterId(), activeFilters);
                predicates.add(predicate);
            }
        });

        // 如果你只需要基于column1和column2去重，也可以明确指定返回这两个列（返回的是Object[]或自定义DTO）
        // query.select(cb.construct(YourCustomDTO.class, root.get("column1"), root.get("column2")))
        //      .distinct(true);

        return cb.and(predicates.toArray(new Predicate[predicates.size()]));
    }
};

这样Hibernate会生成SELECT DISTINCT column1, column2, ...（实体其他列） FROM ...的SQL，Oracle完全能正常执行。

场景2：需要分页（解决count查询的坑） #

如果你的查询用了分页（比如传了Pageable参数），Spring Data JPA会自动生成count查询来算总条数，这时候Hibernate会傻愣愣地生成COUNT(DISTINCT column1, column2)，而Oracle不买账。

这时候得自定义count查询，手动实现去重后的计数逻辑：

步骤1：在Repository里加自定义count方法 #

public interface ProfilingInstructionAccessRepository extends JpaRepository<ProfilingInstructionAccessEntity, Long>, JpaSpecificationExecutor<ProfilingInstructionAccessEntity> {
    // 用拼接列的方式实现去重计数，避免Oracle不支持多列COUNT(DISTINCT)的问题
    @Query("SELECT COUNT(DISTINCT CONCAT(p.column1, '|', p.column2)) FROM ProfilingInstructionAccessEntity p WHERE 1=1 #{#spec}")
    Long countDistinctByColumns(Specification<ProfilingInstructionAccessEntity> spec);

    // 或者用子查询的方式，更直观：
    // @Query("SELECT COUNT(*) FROM (SELECT DISTINCT p.column1, p.column2 FROM ProfilingInstructionAccessEntity p WHERE 1=1 #{#spec}) t")
    // Long countDistinctByColumns(Specification<ProfilingInstructionAccessEntity> spec);
}

步骤2：在Service里手动处理分页逻辑 #

别直接用findAll(spec, pageable)，分开查数据和总条数：

public Page<ProfilingInstructionAccessEntity> getDistinctPage(Specification<ProfilingInstructionAccessEntity> spec, Pageable pageable) {
    // 先查去重后的数据列表，开启distinct
    List<ProfilingInstructionAccessEntity> content = profilingInstructionAccessRepository.findAll(Specification.where(spec).distinct(true), pageable);
    // 再查去重后的总条数，用自定义的count方法
    Long total = profilingInstructionAccessRepository.countDistinctByColumns(spec);
    // 手动封装成Page对象
    return new PageImpl<>(content, pageable, total);
}

另一种思路：在Specification里区分count查询 #

你也可以在Specification里判断当前是否是count查询，动态调整查询逻辑：

return new Specification<ProfilingInstructionAccessEntity>() {
    @Override
    public Predicate toPredicate(Root<ProfilingInstructionAccessEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        final List<Predicate> predicates = new ArrayList<>();
        // ... 你的过滤器逻辑，和原来一样 ...

        // 判断当前是否是count查询（返回类型是Long）
        if (Long.class.equals(query.getResultType())) {
            // 用子查询实现去重计数，绕开Oracle的限制
            Subquery<ProfilingInstructionAccessEntity> subquery = query.subquery(ProfilingInstructionAccessEntity.class);
            Root<ProfilingInstructionAccessEntity> subRoot = subquery.from(ProfilingInstructionAccessEntity.class);
            subquery.select(subRoot)
                    .distinct(true)
                    .where(predicates.toArray(new Predicate[0]));
            query.select(cb.count(subquery));
        } else {
            // 普通查询直接开启distinct
            query.distinct(true);
        }

        return cb.and(predicates.toArray(new Predicate[predicates.size()]));
    }
};

为什么你之前的拼接列尝试没生效？ #

你之前写的query.select(root.get("concated")).distinct(true)，如果concated不是实体类里定义的字段，Hibernate肯定会报错。正确的拼接方式得用CriteriaBuilder的concat方法：

// 拼接column1和column2，作为去重依据
Expression<String> concated = cb.concat(root.get("column1"), cb.literal("|"), root.get("column2"));
query.select(concated).distinct(true);

但这种方式只能返回拼接后的字符串，不是完整的实体对象。如果要返回实体，还是用query.distinct(true)让Hibernate生成包含所有列的DISTINCT查询更靠谱。

内容的提问来源于stack exchange，提问作者Nahúm López Cuellas