Comparing two collections of enums with Hibernate or SQL

I have an entity 'Parent' which has a Set of enums

private Set<MyEnum> myEnums = EnumSet.noneOf(MyEnum.class);

    @CollectionOfElements(targetElement=MyEnum.class)
    @JoinTable
        (name="PARENT_MY_ENUM",
        joinColumns=@JoinColumn(name="PARENT_ID"))
    @Enumerated(EnumType.STRING)
    @Column (name="MY_ENUM", nullable=false)
    public Set<MyEnum> getMyEnums(){
        return myEnums;
    }


public MyEnum {
ENUM_A,
ENUM_B, 
ENUM_C,
ENUM_D;
}

Now I want to search for this entity with a collection of MyEnums. Only entities where all enums are 开发者_JAVA百科set as in the search collection should be returned. So if entity A has ENUM_A, ENUM_B and ENUM_C and entity B has ENUM_B, ENUM_C, ENUM_D a search with the search collectoin ENUM_A, ENUM_B, ENUM_C should only return entity A. A search for ENUM_B and ENUM_C should return nothing.

How would I do that in Hibernate? if I do

select p from Parent p where p.myEnums IN (:searchCollection) and size(p.myEnums) = size(:searchCollection)

then this would return both entities for the first search.

Any ideas?

Update: I got a step further by figuring out how to do it in MySQL but applying this to Hibernate generates invalid SQL. You would use a subquery with EXISTS for it like:

WHERE EXISTS(
    SELECT pa.PARENT_ID, count(pme.MY_ENUM) FROM PARENT pa, PARENT_MY_ENUM pme
    where pa.PARENT_ID = pme.PARENT_ID 
    AND pme.MY_ENUM IN ('ENUM_A','ENUM_B')
    GROUP BY pa.PARENT_ID HAVING count(pme.MY_ENUM) = 2
)

But when I try to do the same in Hibernate:

select pa.ParentId, count(pa.myEnums) from Parent pa 
WHERE pa.myEnums IN ('ENUM_A','ENUM_B')
GROUP BY pa.ParentId HAVING count(pa.myEnums) = 2

Hiberante creates this SQL statement:

select pa.CONTAINER_RELEASE_REFERENCE_ID as col_0_0_, count(.) as col_1_0_ from PARENT pa, PARENT_MY_ENUM enum1, PARENT_MY_ENUM enum2, PARENT_MY_ENUM enum3 
where pa.PARENT_ID=enum1.PARENT_ID and pa.PARENT_ID=enum2.PARENT_ID and pa.PARENT_ID=enum3.PARENT_ID 
and (. in ('ENUM_A' , 'ENUM_B')) 
group by pa.PARENT_ID having count(.)=2

MySQL complains about the '.', Where is that coming from and why is Hibernate using 3 joins to PARENT_MY_ENUM?

Is this a Hibernate bug or what am I doing wrong?

Give the following a try for you exists subselect

select pa.ParentId, count(en) from Parent pa join pa.myEnums as en
WHERE en IN ('ENUM_A','ENUM_B')
GROUP BY pa.ParentId HAVING count(en) = 2

Otherwise, I wonder if something like that might not do the job

select p from Parent p join p.myEnums em
where (:results) = elements(em)

or

select p from Parent p join p.myEnums em
where (:results) in elements(em)

I think you can do that in java. Execute your initially proposed query, iterate the result and exclude (iterator.remove()) the false positives. It should be O(n), and I believe MySQL will require the same time to filter your result.