CODEWITHSUNDEEP
javasqldatabasehibernatejpa
Mark
Mark

Reputation: 4716

Hibernate or SQL Query M-N member of with collections?

Given a class which has an @ElementCollection of Strings, and given an input collection of Strings:

public class FooBar {
   @ElementCollection
   private Set<String> tags
}

Set<String> queryTags;

How can I query the two following scenarios:

  1. Return all FooBar fb where queryTags has at least one match in fb.tags
  2. Return all FooBar fb where all Strings in queryTag match an element in fb.tags (queryTag is a subcollection of fb.tags)

Note: I'm not asking if the two collections are equal.

Obviously, I can query all and perform the collection comparison in code, or I can do:

for(String tag : queryTags){
   createQuery("from FooBar fb where :t member of fb.tags").setString("t", tag)
}

Both ways seem incredibly inefficient, but I don't know if vanilla SQL or HQL support any query support for collections

Upvotes: 1

Views: 517

Answers (1)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 154070

  1. select fb 
from FooBar fb 
left join fb.tags t
where t in ( :queryTags )

  2. select fb 
from FooBar fb 
where fb.id not in (
    select id
    from FooBar
    left join tags t
    where t not in ( :queryTags )
)

Where the queryTags is set with:

session.createQuery(queryString).setParameterList( "queryTags ", queryTags ).list();

Upvotes: 1

Related Questions