使用QueryDsl在Oracle 11上获取两个日期之间天数差的问题

Question

使用QueryDsl在Oracle 11上获取两个日期之间天数差的问题

5

我正在使用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<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)<?
)

出现以下错误：

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

因此，针对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_DAY": 无效标识符

并且添加天数

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": 无效标识符

我试图按照这个测试构建我的查询： 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)

.....

- Vic

这些函数在Oracle中不存在。 - OldProgrammer

我知道这一点，因为我已经在Oracle SQL Developer中尝试过该查询。但是是否有一种将QueryDsl表达式转换为Oracle函数的方法呢？ - Vic

你可以使用类似于DATE1-DATE2的方式来计算日期之间的天数差异。 - Randy

第二次尝试时，我使用了 SQLExpressions.datetrunc(DatePart.day, qEntity.date1).castToNum(Integer.class).subtract(SQLExpressions.datetrunc(DatePart.day, qEntity.date2).castToNum(Integer.class))).lt(dias) 。因此，我将结果转换为数字。 - Vic

我也尝试了这个：DateTimeExpression<Date> fechaFactura = SQLExpressions.datetrunc(DatePart.day, qInvoices.invExpDate); DateTimeExpression<Date> fechaVencimientos = SQLExpressions.datetrunc(DatePart.day, qInvoices.paymentData.any().expDate); 但是这需要一个转换，我没有找到获取numberExpresion的方法。 - Vic

6个回答

0

使用JPQL不可能实现，因为缺乏日期算术运算。但是可以通过使用JPASQLQuery和OracleTemplates进行本地查询来完成，参见：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);

- Meiko Rachimow

0

根据评论，Oracle 不支持这个功能。不过，既然你标记了 java : Joda-Time 是一个非常好的计算两个日期之间差值的工具。

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

- Edwin Lambregts

我知道，但是有没有一种方法可以将Joda-Time与QueryDsl一起使用？我的问题是尝试在Oracle中将queryDsl谓词与SQLExpresions功能结合使用。 - Vic

0

你可以尝试在你的ExtendedOracleDialect中创建一个date_diff函数，并调用它，例如：

``` registerFunction( "date_diff", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "?1-?2") ); ```

然后像这样调用它：

.and(Expressions.numberTemplate(Integer.class, "function('date_diff', {0}, {1})", datefield1, datefield2).eq(6))

如何在JPA查询中使用自定义函数？

- Philip YW

Oracle不支持DATE_DIFF。 - Jasper de Vries

0

最后在我的团队中，我们意识到无法通过queryDsl解决问题后，改变了重点，开始着手解决问题。我们创建了一个自定义的存储库，然后执行查询以获取天数，从表中获取id，然后使用它们来过滤使用querydsl进行的其他查询的结果。

希望这可以帮助到您。

@Repository
public class MyRepositoryImpl implements MyRepository, InitializingBean  {

    @PersistenceContext
    private EntityManager em;

    private QTable1 qTable1;
    private PathBuilder<Table1> path;
    private Querydsl querydsl;
    private JPASQLQuery sqlquery;
    private SQLTemplates templates;

    @Override
    public void afterPropertiesSet() throws Exception {
        this.qTable1 =  QTable1.table1;
        this.path = new PathBuilder<Table1>(qTable1.getType(), qTable1.getMetadata());
        this.querydsl = new Querydsl(em, path);

        this.templates = new OracleTemplates();
        this.sqlquery = new JPASQLQuery(em, templates);
    }





    @Override
    public List<Long> findDiasFecha(String data1, int id ) {

         String myQuery =null;
         int dias;

         switch (id) {
            case 1:  dias = 30;
                    break;
            case 2:  dias = 60;
                     break;
            case 3:  dias = 90;
                     break;
            case 4:  dias = 120;
                     break;
                         //5 & 6 are for 180 days
            default: dias = 180;
                     break;
        }

         
             myQuery =" SELECT i1.invId  FROM Table1 i1 WHERE i1.invRecCif = :data1 and "+
                     " i1.invId IN "+
                     "   (SELECT i2.invId "+
                     "   FROM Table1 i2, "+
                     "     Table2 pd "+
                     "   WHERE pd.entrada.invId = i2.invId "+
                     "   and i1.invId = i2.invId "+
                     "   AND TRUNC(pd.expDate)-TRUNC(i2.invExpDate)<= :dias "+
                     "   )";
         

         Long diasL = new Long(dias);
         TypedQuery<Long> typedQuery = em.createQuery(myQuery , Long.class);
         typedQuery.setParameter("dias", (double) diasL);
         typedQuery.setParameter("data1", data1);

         List<Long> results = typedQuery.getResultList();

        return results;
        }


    

}

- Vic

0

我决定这样做...

    protected enum TypeCompare {
                                EQ,
                                GT,
                                GOE,
                                LT,
                                LOE
    }
    private final static String ORACLE_DATE_FORMAT = "dd.mm.yyyy";

...

    protected BooleanExpression dateCompare(DateTimePath<java.util.Date> dateTimePath, Date date, TypeCompare typeCompare) {
        StringTemplate dbDate = Expressions.stringTemplate("function('TRUNC', {0})", dateTimePath);
        StringTemplate compareDate = Expressions.stringTemplate("function('TO_DATE', {0}, {1})", DateUtils.formatRuDate(date), ORACLE_DATE_FORMAT);
        switch (typeCompare) {
            case EQ:
                return dbDate.eq(compareDate);
            case GT:
                return dbDate.gt(compareDate);
            case GOE:
                return dbDate.goe(compareDate);
            case LT:
                return dbDate.lt(compareDate);
            case LOE:
                return dbDate.loe(compareDate);
            default:
                return dbDate.eq(compareDate);
        }
    }

QueryDSL JPA 函数

- LeshaRB

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Timo Westkämper · Accepted Answer

< p > 仅当与 SQL 一起使用时，SQLExpressions 方法才能工作，而不适用于 JPA/JPQL 查询。由于 JPA 不支持，因此 Querydsl JPA 并未提供 datediff 方法。 < /p >