Compare difference between two dates using JPA Criteria Query

Question

I am using MySQL as a backend for my application.

I want to execute query below using JPA Criteria Query.

SELECT * 
FROM table1 t1
WHERE datediff(now(), t1.created) >= 20;

Note: Type of created is TIMESTAMP

I want Criteria Query like:

CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();

// select
CriteriaQuery<Table1> query = cb.createQuery(Table1.class);

// from
Root<Table1> root = query.from(Table1.class);

// where
List<Predicate> predicates = new ArrayList<Predicate>();

// TODO: add expression for datediff(now(), t1.created) >= 20

query.where(cb.and(predicates.toArray(new Predicate[0])));

Can any one help?

Thanks in advance.

Solution

Supposing that created is a java.util.Date, you have to perform the DATEDIFF in java and compare the property with the resulting date:

Calendar c = Calendar.getInstance();
c.add(Calendar.DAY_OF_YEAR, -20);
Date myDate = c.getTime();

predicates.add(cb.lessThanOrEqualTo(root.<Date>get("created"), myDate));

Comments

[Ashvin Kanani]

Thank you very much for your reply. But, there small mistake you made. I think it should not be cb.greaterThanOrEqualTo() it should be cb.lessThanOrEqualTo(). Because we are finding records older than 20 days.