Im running a spring backend with an oracle database. I use the specification API to perform queries:
@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);
}
This is working perfectly with H2 and MySQL Database. But as soon as im using an Oracle database it wont work:
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
The console output im getting is the following:
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
As soon as I remove either the distinct or the pagination it will also work with the oracle db.
Is there a solution for oracle to get the distinct running together with the pagination?
EDIT
So it seems like oracle fails with the count query. Just for comparison, with H2 the following sql is getting generated:
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=?
CodePudding user response：
The cause of the error ORA-00909: invalid number of arguments is the statement providing the count of the rows
select
distinct count(distinct content0_.id,
content0_.version) as col_0_0_
from
content content0_
...
This construction COUNT(DISTINCT expr,[expr...]) maight be allowed in MySQL
but is illegal in Oracle.
As your dialect is Oracle12cDialect I'd suspect a problem in the implementation of this dialect in case of more than one identifying columns in the entity. See e.g. here for correct SQL solutions.
Also note that the first DISTINCT in this query is possible, but completely irrelevant as a query with count will produce only one row-
Having said that you should IMO make some effort in your data model that will make the usage of distinct not necessary. IMHO the right approach.
CodePudding user response：
Actually the failing query:
select distinct count(distinct (content0_.id, content0_.version)) as col_0_0_ from content content0_ where content0_.status=? ```
is a query which spring-data issues to get the total amount of records available (please check: org.springframework.data.jpa.repository.support.SimpleJpaRepository#executeCountQuery - actually, it would worth to provide the full stacktrace). And it seems that your Content entity has composite PK, i.e. (id, version) (again, no entity definition provided), but HBN experiences some difficulties with such queries: https://hibernate.atlassian.net/browse/HHH-11042