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



Get access to all my video courses, 2 monthly Q&A calls, monthly coding challenges, a community of like-minded developers, 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.

 

Hibernate Tips Book







Get more recipes like this one in my new book Hibernate Tips: More than 70 solutions to common Hibernate problems.

It gives you more than 70 ready-to-use recipes for topics like basic and advanced mappings, logging, Java 8 support, caching, and statically and dynamically defined queries.

Get it now!


Related Articles

Responses

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.