COALESCE function with hibernate criteria

Question

I have a requirement where I need to use COALESCE with hibernate criteria? Is there are way to implement this? I am using hibernate 5.0.

Answer 1

There is an API for this in the JPA specification: CriteriaBuilder.Coalesce

Interface used to build coalesce expressions. A coalesce expression is equivalent to a case expression that returns null if all its arguments evaluate to null, and the value of its first non-null argument otherwise.

As of latest hibernate versions, it is advised to use the JPQL criteria instead of the hibernate specific ones.. you would end up with something like this:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<String> query = builder.createQuery(String.class);
Root<User> user= query.from(User.class);

CriteriaBuilder.Coalesce<String> coalesceExp = builder.coalesce();
coalesceExp.value(user.get("name"));
coalesceExp.value(user.get("surname"));
coalesceExp.value(user.get("middlename"));
query.select(coalesceExp);

Query q =  em.createQuery(query);

The bottom line is that you use the method

CriteriaBuilder.Coalesce<T> value(T value)

or

CriteriaBuilder.Coalesce<T> value(Expression<? extends T> value)

to fill your coalesc expression per your needs