JPA query filter on concatenated fields

Question

I want to query my "customers" table using "name + surname" string as a key.

Name and surname are stored in different fields.

So my query would be:

SELECT 
   * 
FROM 
   customers 
WHERE 
   CONCAT(name,' ',surname) LIKE '%term%' 
   OR CONCAT(surname,' ',name) LIKE '%term%' 

However, I can't do that, my query is a JPA2 criteria query. Something like:

CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
            CriteriaQuery cq = cb.createQuery();
            Root<Customer> from = cq.from(Customer.class);
            cq.select(from);

            Predicate whereClause = cb.or(
                cb.like(from.<String>get("name"), "%"+ r +"%"),
                cb.like(from.<String>get("surname"), "%"+ r +"%"),
            );

            cq.where(whereClause);      
            TypedQuery<Customer> query = getEntityManager().createQuery(cq);
            list = query.getResultList();

How can I filter my result set by the combination of name and surname, please?

Solution

Use CriteriaBuilder.concat(Expression, Expression):

Expression<String> exp1 = cb.concat(from.<String>get("name"), " ");
exp1 = cb.concat(exp1, from.<String>get("surname"));
Expression<String> exp2 = cb.concat(from.<String>get("surname"), " ");
exp2 = cb.concat(exp2, from.<String>get("name"));
Predicate whereClause = cb.or(cb.like(exp1, "%"+ r +"%"), cb.like(exp2, "%"+ r +"%"));

Comments

[PCalouche]

If your database supports the CONCAT_WS (concat with separator) you can do something like: Expression<String> nameConcat = cb.function( "CONCAT_WS", String.class, cb.literal(" "), from.<String>get("name"), from.<String>get("surname")); I wanted to have a delimiter in my concat, but some of the fields were empty or null and chaining these together would add spaces in my concat that I didn't want when there were blanks.