Problems to get the difference of days between two days with queryDsl on Oracle 11

Question

I´m making a query with queryDsl.

I used this to create a 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;
}

And in another procedure I call and execute this as:

BooleanBuilder pBuilderBusquePerso = new BooleanBuilder();

Predicate filter =getPredicate(pBuilderBusquePerso);

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

So the sql query result is:

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

Giving the following error:

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

So the querysql translation for the ddbb oracle is wrong. Is there a way to translate from queryDsl to oracle functions? What should I need?

I also tried with 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))<?

Giving a similar error:

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

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

Giving another similar error:

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

I was trying to follow this test to build my query https://searchcode.com/codesearch/view/17765673/

Thanks in advance.

Edit: this is the full error trace.

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)

.....

Well I Know that because It tried the query in the oracle sql Developer. But is there a way to translate that queryDsl expressions to oracle functions? — Vic, Apr 17 '15 at 11:37
you might say something like DATE1-DATE2 that gives a numerical difference in days — Randy, Apr 17 '15 at 11:43
With the second try I just used SQLExpressions.datetrunc(DatePart.day, qEntity.date1).castToNum(Integer.class).subtract(SQLExpressions.datetrunc(DatePart.day, qEntity.date2).castToNum(Integer.class))).lt(dias) :null So I cast the result to number. — Vic, Apr 17 '15 at 13:00
I also tried this : DateTimeExpression fechaFactura = SQLExpressions.datetrunc(DatePart.day, qInvoices.invExpDate); DateTimeExpression fechaVencimientos = SQLExpressions.datetrunc(DatePart.day, qInvoices.paymentData.any().expDate); But this needs a cast to, I didnt find a way to get a numberExpresion. — Vic, Apr 17 '15 at 13:01

score 2 · Answer 1 · answered Apr 18 '15 at 10:32

2

The SQLExpressions methods work only when used with SQL, not with JPA/JPQL queries. Querydsl JPA doesn't provide datediff out of the box, since JPA doesn't support it.

answered Apr 18 '15 at 10:32

Timo Westkämper

21,824
5
78
111

3

So with Querydsl what would be a solution to se the diff between two dates? Or to add days to a date and compare it with other date in queryDsl? – Vic Apr 21 '15 at 08:13
You can try creating a date_diff function in your OracleDialect and call that. – Philip YW Oct 20 '18 at 12:10

score 0 · Answer 2 · answered Apr 17 '15 at 11:47

0

According to the comments, it's not possible in Oracle. However, since you've tagged java: Joda-Time is excellent to calculate difference between 2 dates.

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

answered Apr 17 '15 at 11:47

Edwin Lambregts

408
6
22

I know but, is there a way to use Joda-Time with QueryDsl?, my problem is to try to use queryDsl predicate with the SQLExpresions functionalities into Oracle. – Vic Apr 17 '15 at 11:58

Vic · Answer 3 · 2021-03-23T12:31:24.790

Finally in my team, we just change the focus to solve the problem after realizing this is not possible to do by queryDsl, so we create a custom repository and then we execute a query to obtain the days, so we obtain the id`s from a table and then we use them to filter the results of other query made with querydsl.

I hope this helps.

@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;
        }


    

}

score 0 · Answer 4 · answered Mar 06 '16 at 06:31

not possible with JPQL, because the lack of date arithmetic. But it can be done by the use of JPASQLQuery with OracleTemplates for native Queries, see: 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);

score 0 · Answer 5 · answered Aug 04 '17 at 08:31

I decided this so ...

    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 Functions

score 0 · Answer 6 · answered Oct 20 '18 at 12:15

0

You can try creating a date_diff function in your ExtendedOracleDialect and call that e.g.

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

and call it like this

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

How to use a custom function in a jpa query?

answered Oct 20 '18 at 12:15

Philip YW

150
1
2
15

Oracle does not support `DATE_DIFF` – Jasper de Vries Dec 05 '18 at 16:31

Problems to get the difference of days between two days with queryDsl on Oracle 11

6 Answers6