JPA Criteria API using ISNULL in a order by statement

Question

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

Answer 1

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.