CODEWITHSUNDEEP
javasqlhibernatejpa
DevNG
DevNG

Reputation: 6065

JPA Criteria API using ISNULL in a order by statement

I use MySQL 5.5 with Hibernate 3.6 and JPA 2.0. I have a User table with a firstName which could also be null or an empty string. I want to have those empty firstName results last in my search results. For that I wrote the following SQL query which works just fine:

SELECT * FROM User ORDER BY ISNULL(firstName), firstName = "", firstName ASC LIMIT 100

Now want to translate this to JPA using the criteria API and I am not quite so sure about the order by. Here is what I have:

criteriaQuery = criteriaQuery.orderBy(cb.asc(cb.isNull(users.get(User_.firstName))), cb.asc(cb.equal(users.get(User_.firstName), "")), cb.asc(users.get(User_.firstName)));

However, the code snippet above does not work, because the CriteriaBuilder.isNull() method is traslated to IS NULL and not to the ISNULL() function of MySQL. I get the following exception:

org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: is null

Any ideas on how to check for null in the Order by statement with JPA 2.0

Upvotes: 3

Views: 3040

Answers (3)

Stephane BEAUFORT
Stephane BEAUFORT

Reputation: 448

JPA can only order field name, so you must declare a new column with your sort expression as below.

SELECT u, (firstName is null) as firstName_isNull 
FROM User u 
ORDER BY firstName_isNull , firstName = "", firstName ASC

Upvotes: 0

user7994918
user7994918

Reputation: 1

I got the exact same problem as you do, finally I solve it using this way, maybe you can try:

  CriteriaQuery<> query;
  query.orderBy(cb.desc(cb.selectCase().
when(cb.isNull("field name"),0).otherwise(1)),
cb.asc("field name");

Upvotes: 0

V G
V G

Reputation: 19002

That is not possible. In JPA you can ORDER BY fields only that you select (that are in the SELECT part of your query). The problem is that there is no IS_NULL function, that can be used in the SELECT part.

Upvotes: 1

Related Questions