Spring数据/SQL查询，按集合查找所有匹配项

Question

您能建议如何在Spring Data JPA存储库中编写查询吗？情况是这样的：我有两个实体：客户和产品，关系为“OneToMany”——意味着一个客户可能有多个产品。在代码中，看起来客户实体已经设置了产品，产品引用了客户，非常简单。如果我从DB JSON中检索Customer，将如下所示：{“id”：10，“name”：'John Smith'，“personalCode”：12345678，“products”：[{“id”：15，“type”：“productType1”}，{“id”：20，“type”：“productType2”}]}

问题是，我如何向DB写入查询，以查找其产品与通过产品集合的产品匹配的所有客户？例如，我想找到所有拥有type1和type2产品的客户。谢谢！

@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 );