JPA - How to query with a LIKE operator in combination with an AttributeConverter

Question

For example Customer has a field of type PhoneNumber (value object). In the persistence.xml a PhoneNumberConverter is registered which implements javax.persistence.AttributeConverter. This converter converts a PhoneNumber to string and visa versa, so the JPA provider is able to store PhoneNumbers into the database.

How to query a Customer with a LIKE operator on PhoneNumber with the Criteria API? PhoneNumber can only be a valid phone number. A PhoneNumber with a value like '+31%' is not possible.

@Safwan Hijazi The use of `Predicates` within the Criteria API comes in handy implementing a large form for searching 'Customers'. When a field is left empty, it shouldn't be taken in account when querying `Customers`. — Bart Weber, Jun 09 '15 at 08:16

user489041 · Accepted Answer · 2015-06-08T21:00:49.717

4

The simple answer is to use a NamedQuery, but you could also use a CriteriaBuilder. Note that you will have to provide the correct types and search terms.

Something like this:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
Root root = criteriaQuery.from(/*The class youre searching*/);
Predicate predicate = criteriaBuilder.like(root.<String>get(/*field name*/), /*search values*/);
criteriaQuery.where(predicate);
criteriaQuery.select(root);
TypedQuery query = entityManager.createQuery(criteriaQuery);
List<T> result = query.getResultList();

edited Jun 08 '15 at 21:00

answered Jun 08 '15 at 20:55

user489041

27,916
55
135
204

All credits to user489041. I added an [answer](http://stackoverflow.com/a/30726266/868879) which continues the example with Customer for completeness. – Bart Weber Jun 09 '15 at 08:08

score 3 · Answer 2 · edited May 23 '17 at 12:14

3

public List<Customer> findCustomerByPhoneNumber(String phoneNumber) {
    EntityManager em = getEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Customer> cq = criteriaBuilder.createQuery();
    Root<Customer> customer = criteriaQuery.from(Customer.class);
    Predicate predicate = cb.like(customer.get(Customer_.phoneNumber).as(String.class), phoneNumber);
    cq.where(predicate);
    TypedQuery<Customer> query = entityManager.createQuery(criteriaQuery);

    return query.getResultList();
    }

Where phoneNumber may include the character %.
The solution of the problem is in the casting to String of PhoneNumber: .as(String.class). PhoneNumber has to override the methode toString () and return the phone number.

(Customer_ provides the meta model of Customer and may be generated by a metamodel generator.)

edited May 23 '17 at 12:14

Community

1
1

answered Jun 09 '15 at 08:02

Bart Weber

1,136
4
15
32

1

Beware of _.as_, it will generate _cast()_ in your query, possibly preventing it from using index. – chimmi Feb 22 '19 at 11:33
@chimmi Would it still do this is the db datatype of the column is text? My issue is similar to the OP's except the db datatype was specifically chosen to be text to permit efficient 'like' queries. Works fine in native queries but now I need to use the Criteria API for ad-hoc form-based filtering. – Dave May 11 '21 at 02:46
Most likely. But you should really check for yourself, just try it with logs enabled and see what sql is being executed. – chimmi May 11 '21 at 06:53

JPA - How to query with a LIKE operator in combination with an AttributeConverter

2 Answers2

Linked