datediff oracle11,使用Oracle 11上的queryDsl获取两天之间差异的问题

我正在使用queryDsl进行查询.

我用它来创建一个BooleanExpresion

public BooleanBuilder getPredicate(BooleanBuilder pBuilderBusquePerso){

int dias = 30;

QEntity1 qEntity = QEntity1.entity;

pBuilderBusquePerso = pBuilderBusquePerso.and(qEntity.date1 != null

? (SQLExpressions.datediff(DatePart.day, qEntity.date2, qEntity.date1).lt(dias) ) :null );

return pBuilderBusquePerso;

}

在另一个过程中,我调用并执行以下操作：

BooleanBuilder pBuilderBusquePerso = new BooleanBuilder();

Predicate filter =getPredicate(pBuilderBusquePerso);

Page iLista = myRepository.findAll(getMyPredicate(usr, filter, tipoListado, null, estados), paginacion);

所以sql查询结果是：

select table1 ta1

......

exists (

select 1

from

table2 ta2

where

ta1.inv_id=ta2.inv_id

and diff_days(ta1.inv_exp_date, ta2.exp_date)

)

给出以下错误：

Caused by: java.sql.SQLException: ORA-00904: “DIFF_DAYS”: invalid identifier

所以ddbb oracle的querysql转换是错误的.有没有办法从queryDsl转换为oracle函数？我需要什么？

我也试过DATETRUNC

pBuilderBusquePerso = pBuilderBusquePerso.and(qEntity.date1 != null

? (SQLExpressions.datetrunc(DatePart.day, qEntity.date1).castToNum(Integer.class).subtract(SQLExpressions.datetrunc(DatePart.day, qEntity.date2).castToNum(Integer.class))).lt(dias) :null );

exists (

select 1

from

table2 ta2

where

ta1.inv_id=ta2.inv_id

and cast(trunc_day(ta2.exp_date) as number(10,0))-cast(trunc_day(ta1.inv_exp_date) as number(10,0))

给出类似的错误：

Caused by: java.sql.SQLException: ORA-00904: “TRUNC_DAY”: invalid identifier

和addDays

pBuilderBusquePerso = pBuilderBusquePerso.and(qEntity.date1 != null

? ((SQLExpressions.addDays(qEntity.date2, dias)).after(qEntity.date1)):null );

exists (

select 1

from

table2 ta2

where

ta1.inv_id=ta2.inv_id

and add_days(ta1.inv_exp_date, ?)>ta1.exp_date

)

给出另一个类似的错误：

Caused by: java.sql.SQLException: ORA-00904: “ADD_DAYS”: invalid identifier

提前致谢.

编辑：这是完整的错误跟踪.

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:231)

at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:214)

at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)

at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)

at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)

.....................

......................

Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)

......................

......................

......................

(And here comes the ORA error)

Caused by: java.sql.SQLException: ORA-00904: "TRUNC_DAY": invalid identifier

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)

.....