CODEWITHSUNDEEP
hibernatejpajpql
Indigenuity
Indigenuity

Reputation: 9740

Why does this enum parameter work for a single field and not for a collection?

Consider the following JPA Entity (implementation is Hibernate 4.3.0):

@Entity
public class MyEntity {

    @Id
    protected long myEntityId;

    @Enumerated(EnumType.STRING)
    public Condition myCondition = Condition.BAD;

    @Enumerated(EnumType.STRING)
    @ElementCollection
    public Set<Condition> conditions = new HashSet<Condition>();

    public enum Condition {
        GOOD, FAIR, BAD, SOL
    }
}

When I run the following query with an enum parameter, I get 0 results (no error):

//Produces no results
String queryString = "FROM MyEntity me WHERE :condition MEMBER OF me.conditions";
List<MyEntity> things = JPA.em().createQuery(queryString, MyEntity.class).setParameter("condition", Condition.BAD).getResultList();

This query produces the following SQL:

select myentity0_.myentityid as myentity1_38_, myentity0_.mycondition as mycondit2_38_ 
from MyEntity myentity0_ 
where ? in (
    select conditions1_.conditions 
    from MyEntity_conditions conditions1_ 
    where myentity0_.myentityid=conditions1_.MyEntity_myEntityId)

If I use a string literal instead, I get results. I also get results if I use the same parameter strategy for non-collection fields:

// Produces results
queryString = "FROM MyEntity me WHERE 'BAD' MEMBER OF me.conditions";
things = JPA.em().createQuery(queryString, MyEntity.class).getResultList();

// Also produces results
queryString = "FROM MyEntity me WHERE me.myCondition = :condition";
things = JPA.em().createQuery(queryString, MyEntity.class).setParameter("condition", Condition.BAD).getResultList();

//So does this
String queryString = "from MyEntity me join me.conditions c where c = :condition";
List<MyEntity> things = JPA.em().createQuery(queryString, MyEntity.class).setParameter("condition", Condition.BAD).getResultList();

These queries produce the following SQL:

select myentity0_.myentityid as myentity1_38_, myentity0_.mycondition as mycondit2_38_ 
from MyEntity myentity0_ 
where 'BAD' in (
    select conditions1_.conditions 
    from MyEntity_conditions conditions1_ 
    where myentity0_.myentityid=conditions1_.MyEntity_myEntityId)

select myentity0_.myentityid as myentity1_38_, myentity0_.mycondition as mycondit2_38_ 
from MyEntity myentity0_ 
where myentity0_.mycondition=?

select myentity0_.myentityid as myentity1_38_, myentity0_.mycondition as mycondit2_38_ 
from MyEntity myentity0_ 
    inner join MyEntity_conditions conditions1_ on myentity0_.myentityid=conditions1_.MyEntity_myEntityId 
where conditions1_.conditions=?

Am I wrong in my assumption that Hibernate should take care of EnumType.STRING and EnumType.ORDINAL when you use parameters? At the least, it's inconsistent. Filling in parameters works when querying enum fields but not enum collections. What am I missing?

Upvotes: 2

Views: 1256

Answers (1)

Michele Mariotti
Michele Mariotti

Reputation: 7459

You have a wrong mapping, so the results are unpredictable.

You missed the @ElementCollection, using only @Enumerated on a Collection it's not a valid mapping.

update

It seems the problem is somewhere else.

You should add the generated SQL, so we can have more info.

I've tried on an equivalent schema, and the query runs fine:

return em.createQuery("from DocumentType x where :family member of x.families", DocumentType.class)
    .setParameter("family", DocumentFamily.GENERIC)
    .setMaxResults(5)
    .getResultList();

and also

from DocumentType x where 'GENERIC' member of x.families
from DocumentType x join x.families f where f = :family
from DocumentType x join x.families f where f = 'GENERIC'
from DocumentType x join x.families f where f = it.shape.edea2.jpa.enums.DocumentFamily.GENERIC

there are two different generated SQL queries:

select ...
from ELEMENT_TYPE documentty0_ 
where documentty0_.DTYPE='DocumentType' 
    and ('GENERIC' in (
        select families1_.FAMILY 
        from DOCUMENT_TYPE_FAMILY families1_ 
        where documentty0_.ID=families1_.DOCUMENT_TYPE_ID)) 
limit 5

select ...
from ELEMENT_TYPE documentty0_ 
    inner join DOCUMENT_TYPE_FAMILY families1_ on documentty0_.ID=families1_.DOCUMENT_TYPE_ID 
where documentty0_.DTYPE='DocumentType' 
    and families1_.FAMILY='GENERIC' 
limit 5

but there's something strange:

from DocumentType x where it.shape.edea2.jpa.enums.DocumentFamily.GENERIC member of x.families

throws

org.hibernate.QueryException: Unrecognized Hibernate Type for handling query constant (it.shape.edea2.jpa.enums.DocumentFamily.GENERIC); expecting LiteralType implementation or AttributeConverter

wich version of Hibernate are you using?

I'm on 5.2.2

Upvotes: 2

Related Questions