CODEWITHSUNDEEP
javahibernatejpa
Aurasphere
Aurasphere

Reputation: 4011

JPA match subset of elements in a ManyToMany relationship

in my database I have two tables: Person and Language. The two tables are mapped in Hibernate in a many-to-many relationship with each person speaking multiple languages.

Now I want to write a method which takes a list of languages and returns a list of people speaking ALL (logical AND) of those languages. I tried to build a criteria like:

Restrictions.in("languages", languagesList);

but this resulted back in a list of people speaking ANY (logical OR) of those languages.

I looked online and I found that there are some tricks using conjunctions and NOT EXISTS but they looked more like hacks so I wanted to check if there's a cleaner approach to achieve this.

Any suggestions? Thanks.

Upvotes: 1

Views: 236

Answers (2)

K.Nicholas
K.Nicholas

Reputation: 11551

Try a JPQL query that first selects all the persons that speak one language and then selects from that list all the people that also speak the other language.

List<Person> ps = em.createQuery("select distinct p from Person p join p.languages l where l.languageName = 'english' "
+ " and p.id in (select distinct p.id from Person p join p.languages l where l.languageName = 'spanish' )", Person.class).getResultList();

Should work.

Upvotes: 0

Raffaele
Raffaele

Reputation: 20885

Your problem is with the understanding of the underlying relational model and not with Hibernate. The Restrictions.in builder maps to the SQL IN operator so your result is not unexpected at all.

In SQL this problem is interesting, too. A possible approach is this (full SQL source):

select u.user_name, count(l.language_name)
  from user u
  join user_language ul on u.user_name = ul.user_name
  join language l on l.language_name = ul.language_name
 where l.language_name in ('spanish', 'italian')
 group by u.user_name
 having count(l.language_name) = 2

The parameters of the query are:

  • the array of languages ('spanish', 'italian')
  • the length of the array (2)

Unfortunately I don't have Hibernate at hand right now, but I think you'd be better off using HQL rather than the Criteria API for this.

Upvotes: 1

Related Questions