Hibernate Criteria order by multiple dates columns

Question

I'm trying to create an hibernate criteria query which would use an order on a specific date column "lastAnswerDate" and if this date is null, fallback on a date which is always present : "createDate".

I don't know how to express it through a criteria query. I guess I need to use expression and case statement, but I don't know how to start.

Look for "Coalesce", a function to fall back to another value (or column) if the first is null. — Stefan Steinegger

jpprade jpprade · Accepted Answer · 2016-03-14T14:18:38

You can do it like this fist create your custom Order and use native sql:

public class CustomizedOrderBy extends Order {
    private String sqlExpression;

    protected CustomizedOrderBy(String sqlExpression) {
        super(sqlExpression, true);
        this.sqlExpression = sqlExpression;
    }

    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        return sqlExpression;
    }

    public static Order sqlFormula(String sqlFormula) {
        return new CustomizedOrderBy(sqlFormula);
    }

    public String toString() {
        return sqlExpression;
    }

}

Then use it like this :

Criteria criteria = session.createCriteria(MyEntity.class);     
criteria.addOrder(CustomizedOrderBy.sqlFormula("case when lastAnswerDate is null then createDate else lastAnswerDate end  desc  "));                    
List res = criteria.list();

It is raw sql so be sure to user the real colmumn name of lastAnswerDate and createDate .

Hibernate Criteria order by multiple dates columns

1 Answers