Hibernate Tips: How to use @ElementCollection entries in a query

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question for a future Hibernate Tip, please leave a comment below.

Question:

We are using an @ElementCollection to model a list of values on one of our entities. Now we need to implement a new use case for which we need to select entities which’s element collection contains a specific value. How can we do that?

Solution:

You can use the @ElementCollection annotation to store a list of values as an entity attribute without needing to model an additional entity. That might look like a great feature, but it has a few downsides, as I explained in a previous Hibernate Tip. I, therefore, don’t recommend to use an element collection but to instead model an association with an additional entity.

If you decide to use an @ElementCollection anyways or if just can’t change the existing code, you can reference its elements similarly as you would do it with a modeled association. Let’s take a look at a simple example.

The following code snippet models an Author entity and uses an @ElementCollection to map a List of phone numbers to the phoneNumbers attribute.

@Entity
public class Author {
 
    @ElementCollection
    private List<String> phoneNumbers = new ArrayList<String>();
 
    ...
}

If you want to select the Author entity that belongs to a given phoneNumber, you need to join the entity with the element collection before you can reference the elements in the WHERE clause.

TypedQuery<Author> q = em.createQuery("SELECT a FROM Author a JOIN a.phoneNumbers p WHERE p = :phoneNumber", Author.class);
q.setParameter("phoneNumber", "123456");
Author a = q.getSingleResult();

It requires a little more code, but you can do the same with a CriteriaQuery. The Author_ class is part of the JPA metamodel and enables you to reference the attributes of the Author entity in a typesafe way. I explained it in more details in Create type-safe queries with the JPA static metamodel.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Author> cq = cb.createQuery(Author.class);
Root<Author> root = cq.from(Author.class);
ListJoin<Author, String> phoneNumbers = root.join(Author_.phoneNumbers);

ParameterExpression<String> paramPhoneNumber = cb.parameter(String.class);
cq.where(cb.equal(phoneNumbers, paramPhoneNumber));

TypedQuery<Author> q = em.createQuery(cq);
q.setParameter(paramPhoneNumber, "123456");
Author a = q.getSingleResult();

Hibernate generates the following SQL statement for both queries. Even so, the phone numbers are not modeled as a separate entity; Hibernate maps them to the Author_phoneNumbers table. That’s why you need to join them in your query before you can reference the elements in the WHERE clause.

12:11:29,019 DEBUG [org.hibernate.SQL] - 
    select
        author0_.id as id1_0_,
        author0_.firstName as firstNam2_0_,
        author0_.lastName as lastName3_0_,
        author0_.version as version4_0_ 
    from
        Author author0_ 
    inner join
        Author_phoneNumbers phonenumbe1_ 
            on author0_.id=phonenumbe1_.Author_id 
    where
        phonenumbe1_.phoneNumbers=?

Learn more:

If you’re using element collections, please be aware of its downsides. I explain them in this Hibernate Tip: How to persist a List of Strings as an ElementCollection.

And you can learn more about JPQL in my Ultimate Guide to JPQL Queries with JPA and Hibernate.