java – Searching in a collection with JPA/Hibernate – Education Career Blog

I would like to make a query from an object I have defined that contains a collection. Object looks like this:

public class ValidationLog {
    public static class ValidationLogPK implements Serializable {
        private String dataKey;
        private DataType dataType;

    private ValidationLogPK id;

    private ValidationResult validationResult;

    @CollectionOfElements(fetch = FetchType.EAGER)
    private Set<ValidationRule> validationRules;

And query looks something like this:

"select from ValidationLog v " + 
    " where v.validationResult = :result" +
    " and = :type" +
    " and :rule in indices(v.validationRules)"

However this does not work. The “indices”-function I am unsure of though. The thing is I would like to get all “dataKeys” that is of a specified type, result and rule. The problem is that each “dataKey” can have many rules as you can see… So how do I do this?


Are you looking for MEMBER OF? Something like this in JPQL:

FROM ValidationLog v
WHERE v.validationResult = :result
  AND = :type
  AND :rule MEMBER OF v.validationRules

If you face HHH-5209 (not sure you will, I reported this issue against Hibernate 3.5), try the HQL variant:

FROM ValidationLog v
WHERE v.validationResult = :result
  AND = :type
  AND :rule IN elements(v.validationRules)

Update: There is another issue when using in elements on a collection of enums, namely HHH-5159. The problem is not with the query itself, it’s with the parameter binding. When using:

query.setParameter("rule", ValidationRule.FOO);

Hibernate binds a serialized version of the enum (in my case, the query just returns nothing). However, using the following worked for me:


Update #2: I am sorry but I don’t think I’ll be able to help further. What I posted about the in element on a collection of enum works for me when used as suggested with Hibernate 3.4 and HSQLDB. Here is the test:

public void testQueryWithInElementOfCollectionOfElementsOfEnums() {
    Person person = new Person("Bruce", "Wayne");
    Set<SomeEnum> someEnums = new HashSet<SomeEnum>();



    String queryString = "SELECT p FROM Person p WHERE :someEnum in elements(p.someEnums)";

    Query query = session.createQuery(queryString);
    // query.setParameter("someEnum", SomeEnum.FIVE); // doesn't work, see HHH-5159
    List actual = query.list();
    assertEquals(1, actual.size());

And the logs:

12:40:06.353 main DEBUG org.hibernate.SQL - 
    select as id11_,
        person0_.dept as dept11_,
        person0_.firstName as firstName11_,
        person0_.gender as gender11_,
        person0_.lastName as lastName11_ 
        Person person0_ 
        ? in (
                Person_someEnums someenums1_ 
12:40:06.357 main TRACE org.hibernate.type.StringType - binding 'FIVE' to parameter: 1
12:40:06.359 main DEBUG org.hibernate.jdbc.AbstractBatcher - about to open ResultSet (open ResultSets: 0, globally: 0)
12:40:06.361 main TRACE org.hibernate.type.IntegerType - returning '1' as column: id11_
12:40:06.363 main DEBUG org.hibernate.loader.Loader - result row: EntityKeycom.acme.domain.Person#1

Maybe try to simplify the query to narrow down the problem. I’m not sure it’s related to the in element part.


  • JPA 1.0 Specification
    • Section 4.6.12 “Collection Member Expressions”
  • Hibernate Core Reference Guide

Leave a Comment