在 Oracle 11 上使用查询 Dsl 获取两天之间天差的问题

Question

我正在用查询Dsl进行查询。

我用它来创建一个布尔表达式

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<Entity> 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)<?
)

给出以下错误：

java.sql.SQLException： ORA-00904："DIFF_DAYS"：无效标识符

因此，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))<?

给出类似的错误：

导致原因:Java . SQL . sqlexception:ORA-00904:“TRUNC _日”:无效标识符

和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
)

给出另一个类似的错误：

原因:Java . SQL . sqlexception:ORA-00904:" ADD _ DAYS ":无效标识符

我试图按照这个测试来构建我的查询 https://searchcode.com/codesearch/view/17765673/

提前感谢。

编辑:这是完整的错误跟踪。

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)

.....

Answer 1

JPQL不可能，因为缺少日期算术。但是它可以通过将JPASQLQuery与OracleTemplate一起用于本机查询来完成，参见：http://www.querydsl.com/static/querydsl/latest/reference/html_single/#d0e431

final JPASQLQuery query = new JPASQLQuery(em, OracleTemplates.DEFAULT);
//without jpa use: com.querydsl.sql.oracle.OracleQuery
query.select(
        SQLExpressions.datediff(
            DatePart.day,
            SQLExpressions.datetrunc(DatePart.day, qEntity.date1),
            SQLExpressions.datetrunc(DatePart.day, qEntity.date2)))
        .from(qEntity);

Answer 2

根据评论，这在Oracle中是不可能的。然而，由于您已经标记了java：Joda Time非常适合计算两个日期之间的差异。

public int CalculateDifference(DateTime date1, DateTime date2) {
   return Days.daysBetween(date1, date2).getDays();
}

Answer 3

SQLExpresations方法仅在与SQL一起使用时有效，而不是与JPA/JPQL查询一起使用。Querydsl JPA不提供开箱即用的日期编辑，因为JPA不支持它。