Order by MAX of 2 dates using hibernate criteria squery

Question

I have a table in my database with both a CREATED_DATE and a MODIFIED_DATE. I'm hoping to do something like


select <all the columns>
, CASE WHEN MODIFIED_DATE IS NULL 
   THEN CREATED_DATE ELSE MODIFIED_DATE END as editDate 
FROM TABLE ORDER BY editDate;

using the criteria API.

How an I do it?

score 0 · Answer 1 · answered Mar 24 '14 at 20:40

According given example it is not needed to order by maximum value of two, but order by date that is modified date when available and otherwise createdDate. That can be done as follows.

Right tool for that is COALESCE expression. It returns first non-null value. When all arguments are null, then null is returned.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> cq = cb.createQuery(Object[].class);
Root<YourEntity> root = cq.from(YourEntity.class);
cq.multiselect(root, cb.coalesce(
   root.get("modifiedDate"), 
   root.get("createdDate")));
cq.orderBy(cb.asc(cb.coalesce(
   root.get("modifiedDate"), 
   root.get("createdDate"))));

List<Object[]> result = em.createQuery(cq).getResultList();

JPQL is bit more readable:

SELECT y, (y.modifiedDate, y.createdDate) 
FROM YourEntity y 
ORDER BY coalesce(y.modifiedDate, y.createdDate)

Order by MAX of 2 dates using hibernate criteria squery

1 Answers1