@ManyToMany JPA 2复杂的查询 [英] @ManyToMany JPA 2 complex query

问题描述

  @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））
私人列表< Class2>等级2; 
} 
 
 @Entity 
公共类Class2 {
 
 @Id 
 @GeneratedValue（strategy = GenerationType.AUTO）
私人长ID; 
} 
  

我想检索所有与Classe2实体有关的Class1实体， class2Id = 1，class2Id = 2，class2Id = 3。 {1,2,3}

或者为了过滤class2 List上的Classe1实体，Class2实体的值为：class2Id = 1和class2Id = 2， class2Id = 3

例如：

如果在连接表上，我已经得到了以下值。 / p>

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

结果将用于本例，Class1和class1Id：1和6.
因为Class1实体，class1Id = 1与classe2Id有关：1,2,3,4
Class1实体，class1Id = 2与classe2Id有关联：1,2,3

是否可以使用JPA2（Predicate）返回正确的实体？

有没有更好的映射来处理这种情况？

目前，我已经想出了以下ng SQL查询：

 从class1_class2中选择v1.class1Id v1 
在v1.class1Id = v2中加入class1_class2 v2。 class1Id 
内连接class1_class2 v3在v2.class1Id = v3.class1Id 
其中v1.classe2Id = 1且v2.classe2Id = 2且v3.classe2Id = 3; 
  

解决方案

以下是可以提供帮助的查询：

 从Class1中选择c1 
 c1 
加入c1.class2 c2 
其中c2.id in（1 ，2,3）
 group by c1 
 count（c1）= 3; 
  

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;

解决方案

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 ;

这篇关于@ManyToMany JPA 2复杂的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！