我正在運行一個帶有 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
我得到的控制臺輸出如下：
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
一旦我洗掉了distinct或 ，pagination它也將與 oracle db 一起使用。
oracle有沒有辦法讓分頁和分頁一起運行？
編輯
因此，似乎 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=?
uj5u.com熱心網友回復：
錯誤的原因ORA-00909: invalid number of arguments是提供行數的陳述句
select
distinct count(distinct content0_.id,
content0_.version) as col_0_0_
from
content content0_
...
COUNT(DISTINCT expr,[expr...])在MySQL 中可能允許這種構造
但在 Oracle 中是非法的。
由于您的方言是Oracle12cDialect我懷疑在物體中存在多個識別列的情況下在實施此方言時存在問題。例如，請參見此處以獲取正確的 SQL 解決方案。
另請注意，此查詢中的第一個 DISTINCT是可能的，但與查詢完全無關，count只會產生一行-
說了這么多，你應該讓IMO在資料模型中一些努力，這將使的使用distinct 沒有必要。恕我直言，正確的方法。
uj5u.com熱心網友回復：
實際上失敗的查詢：
select distinct count(distinct (content0_.id, content0_.version)) as col_0_0_ from content content0_ where content0_.status=? ```
是 spring-data 問題以獲取可用記錄總數的查詢（請檢查：org.springframework.data.jpa.repository.support.SimpleJpaRepository#executeCountQuery - 實際上，提供完整的堆疊跟蹤是值得的）。并且您的內容物體似乎具有復合 PK，即 (id, version)（同樣，未提供物體定義），但 HBN 在此類查詢中遇到一些困難：https : //hibernate.atlassian.net/browse/HHH-11042
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/375482.html