ShriPatrudkar ShriPatrudkar - 6 months ago 73
Java Question

How to write 'Case' in order by clause of hibernate criteria query?

This is my hibernate query:

Session session = HibernateUtil.beginTransaction();
Criteria criteria = session.createCriteria(Project.class);
...
criteria.addOrder(Order.desc("rs.reservationStartDate"));


I want to accomodate below sql query in above hibernate query:

select p.PROJECT_ID, p.PROJECT_NAME, p.PLUPS_NUMBER ...
...
where rs.RESERVATION_START_DATE <= to_char(sysdate)
ORDER BY (
CASE WHEN
(rs.RESERVATION_START_DATE <= to_date(sysdate,'dd/mm/yy') and
rs.RESERVATION_END_DATE >= SYSDATE) THEN '1'
ELSE to_char(rs.RESERVATION_START_DATE) END) DESC


The DB is Oracle 11g and hibernate version is 4.1.

So in new query we have
CASE
in
ORDER BY
clause:

ORDER BY (
CASE WHEN
(rs.RESERVATION_START_DATE <= to_date(sysdate,'dd/mm/yy') and
rs.RESERVATION_END_DATE >= SYSDATE) THEN '1'
ELSE to_char(rs.RESERVATION_START_DATE) END) DESC


How I can add it in Java code?

criterai.addOrder(Order.desc("rs.reservationStartDate"));
...

Answer

Hibernate does not support custom SQL in ORDER BY clause out of the box, however, it's trivial to add one. For an example, look here: http://blog.tremend.com/2008/06/10/how-to-order-by-a-custom-sql-formulaexpression-when-using-hibernate-criteria-api/

Using this class, you will then write:

criteria.addOrder(OrderBySqlFormula.sqlFormula(
    "CASE WHEN sysdate between rs.RESERVATION_START_DATE and rs.RESERVATION_END_DATE "+
    "THEN '1' "+
    "ELSE to_char(rs.RESERVATION_START_DATE) END DESC"));

I also simplified your CASE clause: no need for som many parentheses and BETWEEN operator is simpler.