CODEWITHSUNDEEP
javasqlhibernatehibernate-criteria
Yassine Badache
Yassine Badache

Reputation: 1851

Hibernate Criteria - How to apply functions to ORDER BY?

It sounded like a simple problem, but I found no easy solution online.

I am trying to replicate the current ORDER BY in Hibernate, without succes:

SELECT * FROM IPEM.DEMANDE
WHERE INIT_DATE >= TO_TIMESTAMP('23/04/2021', 'dd/MM/yyyy') AND INIT_DATE <= 
TO_TIMESTAMP('29/04/2021', 'dd/MM/yyyy')
ORDER BY TO_TIMESTAMP(LIMIT_DATE, 'dd/MM/yyyy'), TO_TIMESTAMP(INIT_DATE , 'dd/MM/yyyy') ASC <<< this line

Why ? Because I have this kind of data in my database:

05/05/2021 00:00:00 - 23/04/2021 00:00:00
05/05/2021 00:00:00 - 28/04/2021 00:00:00 << this should be 3rd
05/05/2021 02:00:00 - 24/04/2021 00:00:00 << this should be 2nd

The hours mess up the sorting. I'm trying to ignore them/format the date before loading my entries. A way I found to do so is applying TO_TIMESTAMP to ORDER_BY. It works well in SQL, but when going to Hibernate, it is not that simple.

Actually, my code looks like this:

criteria.addOrder(Order.asc(fieldName));

I tried some trivial solution, which obviously did not work:

criteria.addOrder(Order.asc("TO_TIMESTAMP(" + fieldName + ", 'dd/MM/yyyy')");

For which I had the following error (limitDate is the Java name, LIMIT_DATE the corresponding column):

org.hibernate.QueryException: could not resolve property: TO_TIMESTAMP(limitDate, 'dd/MM/yyyy')

How can I apply TO_TIMESTAMP to the members of my ORDER BY ? (No worries, we assume here all the members are dates, both in Java and in the SQL Table).

Upvotes: 0

Views: 859

Answers (1)

Christian Beikov
Christian Beikov

Reputation: 16400

That's not possible, but you can subclass Order and override the org.hibernate.criterion.Order#toSqlString method to implement whatever logic you need. Anyway, you should move away from the legacy Criteria API to the JPA Criteria API. There you could use criteriaQuery.orderBy(criteriaBuilder.function("TO_TIMESTAMP", root.get(fieldName), criteriaBuilder.literal("dd/MM/yyyy")))

Upvotes: 1

Related Questions