Searching in a collection with JPA/Hibernate

Question

Hi,

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

@Entity
public class ValidationLog {
    @Embeddable
    public static class ValidationLogPK implements Serializable {
        private String dataKey;
        @Enumerated(EnumType.STRING)
        private DataType dataType;
    }

    @EmbeddedId
    private ValidationLogPK id;

    @Enumerated(EnumType.STRING)
    private ValidationResult validationResult;

    @CollectionOfElements(fetch = FetchType.EAGER)
    @JoinTable
    @Enumerated(EnumType.STRING)
    private Set<ValidationRule> validationRules;
}

And query looks something like this:

"select v.id.dataKey from ValidationLog v " + 
    " where v.validationResult = :result" +
    " and v.id.dataType = :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?

Answer 1

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

SELECT v.id.dataKey
FROM ValidationLog v
WHERE v.validationResult = :result
  AND v.id.dataType = :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:

SELECT v.id.dataKey
FROM ValidationLog v
WHERE v.validationResult = :result
  AND v.id.dataType = :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:

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

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:

@Test
// http://opensource.atlassian.com/projects/hibernate/browse/HHH-5159
public void testQueryWithInElementOfCollectionOfElementsOfEnums() {
    Person person = new Person("Bruce", "Wayne");
    Set<SomeEnum> someEnums = new HashSet<SomeEnum>();
    someEnums.add(SomeEnum.ONE);
    someEnums.add(SomeEnum.TWO);
    someEnums.add(SomeEnum.FIVE);

    person.setSomeEnums(someEnums);

    session.persist(person);

    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
    query.setParameter("someEnum", SomeEnum.FIVE.name());
    List actual = query.list();
    assertNotNull(actual);
    assertEquals(1, actual.size());
}

And the logs:

...
12:40:06.353 [main] DEBUG org.hibernate.SQL - 
    select
        person0_.id as id11_,
        person0_.dept as dept11_,
        person0_.firstName as firstName11_,
        person0_.gender as gender11_,
        person0_.lastName as lastName11_ 
    from
        Person person0_ 
    where
        ? in (
            select
                someenums1_.element 
            from
                Person_someEnums someenums1_ 
            where
                person0_.id=someenums1_.Person_id
        )
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: EntityKey[com.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.

References

• JPA 1.0 Specification
  • Section 4.6.12 "Collection Member Expressions"
• Hibernate Core Reference Guide
  • 14.10. Expressions