JPA用Oracle12cDialect时复合键加distinct分页查询报ORA-00909

Oracle不支持count(distinct col1, col2)这类多列去重计数的语法，而Hibernate在处理带复合键实体的distinct分页查询时，会自动生成这种不兼容的count语句，进而抛出ORA-00909错误。以下是两种可行的解决方案：

方案一：自定义Oracle方言（全局解决） #

通过继承官方Oracle方言，重写SQL生成逻辑，将多列distinct count转换为Oracle支持的单列拼接去重计数。

针对Hibernate 6.x版本 #

创建自定义方言类，重写SQL AST翻译器的多列distinct count渲染逻辑：

import org.hibernate.dialect.Oracle12cDialect;
import org.hibernate.sql.ast.spi.SqlAstTranslatorFactory;
import org.hibernate.sql.ast.spi.StandardSqlAstTranslatorFactory;
import org.hibernate.sql.ast.tree.select.CompositeSelectItem;
import org.hibernate.sql.ast.tree.select.SelectItem;
import org.hibernate.sql.ast.tree.select.SelectStatement;
import org.hibernate.sql.ast.spi.SessionFactoryImplementor;
import org.hibernate.sql.ast.spi.Statement;

public class CustomOracle12cDialect extends Oracle12cDialect {

    @Override
    protected SqlAstTranslatorFactory buildSqlAstTranslatorFactory() {
        return new StandardSqlAstTranslatorFactory() {
            @Override
            protected <T> org.hibernate.sql.ast.spi.SqlAstTranslator<T> buildTranslator(
                    SessionFactoryImplementor sessionFactory,
                    Statement statement) {
                if (statement instanceof SelectStatement) {
                    return new org.hibernate.dialect.OracleSqlAstTranslator<>(sessionFactory, (SelectStatement) statement) {
                        @Override
                        protected void renderCountDistinctSelectItem(SelectItem selectItem) {
                            // 处理复合键多列distinct，转为拼接后的单列distinct
                            if (selectItem instanceof CompositeSelectItem) {
                                getSqlAppender().append("count(distinct (");
                                boolean first = true;
                                for (SelectItem item : ((CompositeSelectItem) selectItem).getSelectItems()) {
                                    if (!first) {
                                        getSqlAppender().append(" || '-' || ");
                                    }
                                    renderSelectItem(item);
                                    first = false;
                                }
                                getSqlAppender().append("))");
                            } else {
                                super.renderCountDistinctSelectItem(selectItem);
                            }
                        }
                    };
                }
                return super.buildTranslator(sessionFactory, statement);
            }
        };
    }
}

在配置文件中指定使用该自定义方言：

spring.jpa.properties.hibernate.dialect=com.yourpackage.CustomOracle12cDialect

针对Hibernate 5.x版本 #

通过字符串替换修改生成的count查询：

import org.hibernate.dialect.Oracle12cDialect;
import org.hibernate.sql.CountQuerySqlGenerator;

public class CustomOracle12cDialect extends Oracle12cDialect {

    @Override
    public CountQuerySqlGenerator getCountQuerySqlGenerator() {
        return new CountQuerySqlGenerator() {
            @Override
            public String generateCountQuery(String querySelect) {
                String countQuery = super.generateCountQuery(querySelect);
                // 替换多列distinct语法为Oracle支持的拼接形式
                return countQuery.replaceAll(
                    "count\\(distinct\\s+([^,]+),\\s+([^)]+)\\)", 
                    "count(distinct ($1 || '-' || $2))"
                );
            }
        };
    }
}

方案二：手动处理分页count查询（局部解决） #

如果不想全局修改方言，可以在业务代码中手动构建count查询，绕过Hibernate自动生成的错误语句：

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.PageableExecutionUtils;
import org.springframework.data.jpa.domain.Specification;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;

// 在你的Repository实现类或Service中
public Page<YourEntity> findWithDistinctCompositeKey(Specification<YourEntity> spec, Pageable pageable) {
    // 查询分页列表数据
    List<YourEntity> content = yourEntityRepository.findAll(spec, pageable);
    
    // 手动计算符合条件的去重总数
    long total = yourEntityRepository.count((Root<YourEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
        Predicate predicate = spec.toPredicate(root, query, cb);
        // 拼接复合键字段，避免去重冲突
        Expression<String> compositeKey = cb.concat(
            cb.concat(root.get("businessRulesId"), "-"), 
            root.get("notificationConfigId")
        );
        query.select(cb.countDistinct(compositeKey)).distinct(true);
        return predicate;
    });
    
    return PageableExecutionUtils.getPage(content, pageable, () -> total);
}

注意事项 #

• 拼接复合键时建议加入分隔符（如-），避免不同键组合拼接后出现重复值（例如id1=12, id2=3和id1=1, id2=23，不加分隔符会拼接成相同的123）。
• 自定义方言的方式更适合全局统一解决问题，无需修改多处业务代码；手动处理count的方式则适合局部特殊场景。

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