CODEWITHSUNDEEP
javasqlpostgresqljpa
wojtek
wojtek

Reputation: 503

JPA CriteriaBuilder date operations

I'm trying to translate my sql query to JPA Criteria Builder which is used in project which I'm working on and I stuck on date operation (PostgreSQL 9.3), I have a bit in where clause:

...AND cl.due_date + '3 YEARS' <= '2016-01-01 10:27:16.473' and cl.due_date + '3 YEARS' >= '2008-01-01 10:27:16.473'....

The problem is that I'm not able to create a Predicate object for "due_date" + "3 YEARS". Does anyone know how to manage to do that?

Thanks in advance

Upvotes: 2

Views: 10247

Answers (1)

perissf
perissf

Reputation: 16273

You need to add (or subtract) the 3 years in Java, not in the query:

Calendar dueDateCalPlus3Years = Calendar.getInstance();
dueDateCalPlus3Years.set(set(year, month, date, hourOfDay, minute, second);
dueDateCalPlus3Years.add(Calendar.YEAR, -3);
Date dueDatePlus3Years = dueDateCalPlus3Years.getTime();

Expression<Date> cl;      // initialize it!
Predicate predicate = criteriaBuilder.greaterThanOrEqualTo(cl, dueDatePlus3Years);

To be more concise, you can use the interval mapping CriteriaBuilder#between(), and the query you have posted results in:

Date date1;
Date date2;

Predicate predicate = criteriaBuilder.not(criteriaBuilder.between(cl, date1, date2));

Upvotes: 2

Related Questions