Search code examples
jpahibernate-criteriadatediffcriteria-api

DATEDIFF function in hibernate criteria query

I have to convert the below query into hibernate criteria query:

select * from tbl1 t1,tbl2 t2 where t1.id=t2.id 
and ( To_char(t1.modified_date, 'YYYYMMDD') - To_char(get_sys_date, 'YYYYMMDD') )>10;

where get_sys_date is a sql function.

When I use datediff function in my java code like:

 Expression<String> day = cb.literal("day");
 Expression<Integer> diff = cb.function("DATEDIFF", Integer.class, day, currentDate, 
 root.get(myVO_.modified_date));

it gives me a compile error:

method function in interface CriteriaBuilder cannot be applied to given types

'currentDate' is passed as a param to the method.

How to write the creteria expression then?

Solution

  • I figured it out.

    By using ParameterExpression<Date> param = cb.parameter(Date.class);

    we can achieve this. And then :

    TypedQuery<myVO> tq = this.entityManager.createQuery(cq);
tq.setParameter(param, currentDate);
return tq.getResultList();