@ManyToMany JPA 2 complex query

Question

I have got the following ManyToMany mapping.

@Entity
public class Class1 {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "class1_class2", joinColumns = @JoinColumn(name = "class1Id"),  inverseJoinColumns = @JoinColumn(name = "class2Id"))
private List<Class2> class2;
}

@Entity
public class Class2 {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
}

I want to retrieve all the Class1 entities, that have relation with Classe2 entities, with class2Id = 1 and class2Id =2 and class2Id =3. {1,2,3}

Or to filter the Classe1 entities that have on their class2 List, the Class2 entities with the values: class2Id = 1 and class2Id =2 and class2Id =3

For example:

If on the join table, i have got the follwoing values.

class1Id   class2Id
   1          1
   1          2
   1          3
   1          4
   6          1
   6          2
   6          3
   4          1
   5          2

The result would be for this example, Class1 with class1Id: 1 and 6. Because Class1 entity, with class1Id=1 has relaion with classe2Id: 1,2,3, 4 And Class1 entity, with class1Id=2 has relaion with classe2Id: 1,2,3

Is it possible to get the right entities returned with JPA2 (Predicate)?

And is there any better mappings to handle this case?

For the moment, i have come up with the following SQL query:

select v1.class1Id from class1_class2 v1
inner join class1_class2 v2 on v1.class1Id=v2.class1Id
inner join class1_class2 v3 on v2.class1Id=v3.class1Id
where v1.classe2Id=1 and v2.classe2Id=2 and v3.classe2Id=3;

Answer 1

Here is the query that can help:

select c1 
from Class1 c1 
join c1.class2 c2 
where c2.id in (1,2,3)
group by c1
having count(c1)=3 ;

Answer 2

At first in your class Class2 you'll need add the following:

@ManyToMany(fetch = FetchType.LAZY, mappedBy="class2")
private List<Class1> class1;

After this your task should be done with this query:

select c1 from Class1 c1 join c1.class2 c2 where c2.id in ?1 group by c1

where ?1 - object of type List<Long>, containing ids {1,2,3}.