Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

COALESCE function with hibernate criteria

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.

like image 614
nimi Avatar asked Feb 06 '17 09:02

nimi


People also ask

What is coalesce in HQL?

The equivalent to the nvl command in HQL is the coalesce command. coalesce(a,b) will return a if a is not null, otherwise b . So you would want something on the lines of: from Table where col1 = coalesce(:par1, 'asdf') Copy link CC BY-SA 2.5. Follow this answer to receive notifications.

Is hibernate criteria deprecated?

Since Hibernate 5.2, the Hibernate Criteria API is deprecated, and new development is focused on the JPA Criteria API. We'll explore how to use Hibernate and JPA to build Criteria Queries.

How hibernate handle null values?

The best way to avoid Hibernate's attempts at setting null values to primitives is to use Wrapper classes (Integer, Long, Double...); and especially, if you need to tack on a column or 2 to an existing table. Auto-boxing is your friend.

What is CriteriaBuilder in Java?

Interface CriteriaBuilder. public interface CriteriaBuilder. Used to construct criteria queries, compound selections, expressions, predicates, orderings. Note that Predicate is used instead of Expression<Boolean> in this API in order to work around the fact that Java generics are not compatible with varags.


1 Answers

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

like image 190
Maciej Kowalski Avatar answered Oct 25 '22 16:10

Maciej Kowalski