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

10.4k views Asked by At

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.

2

There are 2 answers

1
user489041 On BEST ANSWER

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();
3
Bart Weber On
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.)