JPA对接Oracle DB时distinct分页查询报ORA-00909错误如何解决

根因分析 #

报错核心是Oracle与其他数据库的语法差异：

• Oracle的COUNT()函数不支持传入多个列作为参数，而H2、MySQL兼容这种用法
• 你的Content实体使用id+version作为联合主键，开启distinct+分页后，Hibernate自动生成的count查询会把所有主键字段传入COUNT(DISTINCT)，生成count(distinct content0_.id, content0_.version)的非法语法，触发ORA-00909参数数量错误
• 单独用distinct或者单独用分页都不会触发count多列的问题，所以这两种场景下运行正常

解决方案 #

方案1：Specification区分查询类型（最推荐，无侵入，全数据库兼容） #

仅修改toPredicate方法，判断当前是count查询还是数据查询，count查询不需要设置distinct：

@Override
public Predicate toPredicate(
    @NonNull final Root<Content> root,
    @NonNull final CriteriaQuery<?> query,
    @NonNull final CriteriaBuilder builder) {
  final List<Predicate> predicates = new ArrayList<>();

  if (!isEmpty(filter.getTerm())) {
    // 原有逻辑保持不变
    predicates.add(builder.or(title, subtitle, body, keywords));
  }

  // 新增逻辑：只有返回业务实体的查询才需要去重，count查询不需要设置distinct
  if (query.getResultType() != Long.class && query.getResultType() != long.class) {
    query.distinct(true);
  }

  return builder.and(predicates.toArray(new Predicate[0]));
}

这个方案不需要修改其他配置、不需要自定义SQL，对业务逻辑无任何影响。

方案2：自定义count查询语法 #

如果业务确实需要count阶段也做去重，可以手动指定count查询的逻辑，把多列distinct改为Oracle支持的拼接写法：

-- 用concat拼接联合主键字段，适配Oracle语法
COUNT(DISTINCT CONCAT(content0_.id, '-', content0_.version))

使用Specification的场景下可以调用JpaSpecificationExecutor的重载方法，单独传入count查询对应的Specification实现。

方案3：升级Hibernate版本 #

当前使用的Hibernate 5.4.12版本存在Oracle联合主键count distinct的语法生成bug，升级到Hibernate 5.6及以上版本，官方已经修复了该兼容问题，会自动生成适配Oracle的合法count语句。

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