Spring Data/SQL查询按集合查找所有匹配项

Question

请问如何在Spring Data JPA存储库中编写查询?情况如下:我有两个实体:客户和产品，关系为"OneToMany"-意味着一个客户可能有许多产品。在代码中，它看起来像客户实体有设置产品和产品有引用客户客户，非常简单。如果我从DB中检索Customer, JSON将如下所示:{"id":10, "name":'John Smith',"personalCode":12345678,"products":[ {"id":15,"type":"productType1"}, {"id":20,"type":"productType2"}] }

问题是我如何写查询到DB找到所有客户的产品匹配传递的产品集合?例如，我想找到拥有类型1和类型2产品的所有客户。谢谢!

@Entity
@Table(name = "customer")
public class Customer {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private int age;
private String personalCode;
@Enumerated(EnumType.STRING)
private Country country;
private String internetBankUserId;
@Enumerated(EnumType.STRING)
private CustomerType type;
@JsonManagedReference
@OneToMany(mappedBy = "customer", cascade = CascadeType.ALL)
@SortNatural
private SortedSet<Product> products = new TreeSet<>();

---

@Entity
@Table(name = "product")
public class Product implements Comparable<Product>{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Enumerated(EnumType.STRING)
private ProductType type;
@JsonBackReference
@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "customer_id")
private Customer customer;

Answer 1

这个解决方案对我很有效:

@Query("SELECT c FROM Customer c join  c.products p where p.type in :products 
and SIZE(c.products) >= :count")
Set<Customer> findAllByProductType (@Param("products")Set<ProductType> products,
@Param("count") Integer count );