JPA和Oracle DB：不同的分页查询导致错误：ORA-00909：无效的参数数量

Question

我正在运行一个带有oracle数据库的Spring后端。我使用规范API来执行查询：

@Slf4j
public class ContentSpecification implements Specification<Content> {

  private final transient ContentFilter filter;

  public ContentSpecification(final ContentFilter filter) {
    this.filter = filter;
  }

  @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())) {
      // ... not relevant

      predicates.add(builder.or(title, subtitle, body, keywords));
    }
    
    query.distinct(true);

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

  public PagedModel<ContentModel> getContent(
      final String term,
      final Integer page,
      final Integer size) {

    final int pageSize = size == 0 ? MAX_VALUE : size;

    return pagedAssembler
        .toModel(
            repository.findAll(
                new ContentSpecification(
                    new ContentFilter(term)),
                PageRequest.of(page, pageSize)),
            contentAssembler);
  }

这在H2和MySQL数据库中运行良好。但一旦我使用Oracle数据库，它就无法工作：

spring:
  datasource:
    driver-class-name: oracle.jdbc.OracleDriver
    initialization-mode: never
    url: <url>
    username: <user>
    password: <pw>
  jpa:
    database-platform: org.hibernate.dialect.Oracle12cDialect
    hibernate:
      ddl-auto: validate
    properties:
      hibernate:
        format_sql: true
        current_session_context_class: org.springframework.orm.hibernate5.SpringSessionContext
    show-sql: true

im获得的控制台输出如下：

2021-12-07 15:24:22.487  INFO [my-service,4d641a9f03674994,4d641a9f03674994,false] 14244 --- [nio-8082-exec-7] d.b.b.p.p.content.ContentController      : get content: null, null, null, 0, 5
Hibernate: 
    select
        * 
    from
        ( select
            distinct content0_.id as id2_3_,
            content0_.version as version3_3_,
            content0_.status as status9_3_,
            content0_.status_date as status_date10_3_,
            content0_.subtitle as subtitle11_3_,
            content0_.title as title12_3_,
        from
            content content0_ 
        where
            content0_.status=? ) 
    where
        rownum <= ?
Hibernate: 
    select
        distinct count(distinct content0_.id,
        content0_.version) as col_0_0_ 
    from
        content content0_ 
    where
        content0_.status=?
2021-12-07 15:24:22.508  WARN [my-service,4d641a9f03674994,4d641a9f03674994,false] 14244 --- [nio-8082-exec-7] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 909, SQLState: 42000
2021-12-07 15:24:22.508 ERROR [my-service,4d641a9f03674994,4d641a9f03674994,false] 14244 --- [nio-8082-exec-7] o.h.engine.jdbc.spi.SqlExceptionHelper   : ORA-00909: Invalid amount of arguments
2021-12-07 15:24:22.514 ERROR [my-service,4d641a9f03674994,4d641a9f03674994,false] 14244 --- [nio-8082-exec-7] d.b.b.p.p.ApplicationExceptionHandler    : runtime exception: 

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    ...
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    ... 111 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: ORA-00909: Ungültige Anzahl von Argumenten
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.4.2.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.2.jar:na]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
    ... 148 common frames omitted
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00909: Ungültige Anzahl von Argumenten

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    ... 165 common frames omitted

只要我删除独特的或分页，它也将与oracle db一起工作。

甲骨文有没有一个解决方案，让不同的运行与分页一起？

编辑

因此，oracle似乎无法执行计数查询。为了进行比较，将使用H2生成以下sql：

Hibernate: 
    select
        distinct content0_.id as id2_3_,
        content0_.version as version3_3_,
        content0_.status as status9_3_,
        content0_.status_date as status_10_3_,
        content0_.subtitle as subtitl11_3_,
        content0_.title as title12_3_,
    from
        content content0_ 
    where
        content0_.status=? limit ?
Hibernate: 
    select
        distinct count(distinct (content0_.id,
        content0_.version)) as col_0_0_ 
    from
        content content0_ 
    where
        content0_.status=?

Answer 1

实际上，失败的查询：

    select
        distinct count(distinct (content0_.id,
        content0_.version)) as col_0_0_ 
    from
        content content0_ 
    where
        content0_.status=? ```

是一个查询，其中Spring数据问题，以获得可用的记录总量（请检查：org.springframework.data.jpa.repository.support.SimpleJpaRepository#执行计数查询-实际上，它值得提供完整的堆栈跟踪）。你的内容实体似乎有复合PK，即（id，版本）（同样，没有提供实体定义），但是HBN在这样的查询中遇到了一些困难：https://hibernate.atlassian.net/browse/HHH-11042

Answer 2

导致错误的原因是提供行计数的语句。ORA-00909:参数数无效。

select
        distinct count(distinct content0_.id,
        content0_.version) as col_0_0_ 
    from
        content content0_
...

这种结构COUNT（DISTINCT expr[expr…]）maight在MySQL中是允许的

但在甲骨文中是非法的。

由于您的方言是Oracle12cDialect，我怀疑在实体中存在多个标识列的情况下，这种方言的实现存在问题。如需正确的SQL解决方案，请参阅此处。

还要注意，此查询中的第一个DISTINCT是可能的，但完全无关，因为具有Count的查询将只产生一行-

已经说过，您应该在您的数据模型中做出一些努力，这将使的使用变得与众不同。我认为这是正确的方法。