JPA Criteria with ElementCollection

Question

I have something like that:

@Entity
public class Person {

    @ElementCollection
    private List<String> emails;
    ...
}

how can I convert the following JPQL into a Criteria Query:

select p from Person p
where exists (
    select 1 
    from p.emails e
    where e like :email
)

Answer 1

If you don't really need the power of LIKE and an exact match is enough you can check if emails contains email.

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Person> criteria = builder.createQuery(Person.class);
Root<Person> p = criteria.from(Person.class);
criteria.select(p);


Expression<List<String>> emails = p.get(Person_.emails);
criteria.where(builder.isMember("[email address]", emails));


TypedQuery<Person> tq = entityManager.createQuery(criteria);
List<Person> persons = tq.getResultList();

Note that p.get(Person_.emails) requires a static meta model of the Person class. If you do not have that, you can replace that part by p.get("emails") at the cost of type-safety.

If you do need to perform a LIKE you will have to join on the collection.

Join<Person, String> emailJoin = p.join(Person_.emails);
criteria.where(builder.like(emailJoin, "[email address]"));
criteria.distinct(true);