java HQL或SQL查询从多对多相关实体获取数据

Question

我使用spring和hibernate在MySql数据库中存储数据。我试图根据用户请求的过滤器检索行。 我有以下表格/实体：产品和宝石

关系：

• 产品有很多宝石

我正在尝试编写一个查询，以获取包含宝石a、宝石B和宝石C的产品。。等等

用例：

如果用户要求购买带有宝石51和46的产品。查询应仅返回产品id 4

查询：

filterGemstones（）方法返回gemstone用户想要筛选产品的目标。使用下面的查询，我得到零条记录，但如果我删除HAVING Count(DISTINCT p.product_id) = 2，我会得到产品id 4，5

• HQL：

  createQuery("select p.productId from Product p JOIN p.gemstones g where g in :gemstones group by p having count (distinct p) =" +  filterGemstones().size() ).setParameter("gemstones",filterGemstones());
  

• 通过hibernate生成SQL：

  SELECT p.product_id 
  FROM   product p 
  INNER JOIN gemstone_product gp 
         ON p.product_id = gp.product_id 
  INNER JOIN gemstone g 
         ON gp.gemstone_id = g.gemstone_id 
  WHERE  g.gemstone_id IN ( 51, 46 ) 
  GROUP  BY p.product_id 
  HAVING Count(DISTINCT p.product_id) = 2 
  

产品类别：

@Entity
@Table(name = "product")
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "product_id")
    private long productId;

    @ManyToMany()
    @JoinTable(
            name = "gemstone_product",
            joinColumns = {@JoinColumn(name = "product_id")},
            inverseJoinColumns = {@JoinColumn(name = "gemstone_id")}
    )
    private Set<Gemstone> gemstones = new HashSet<>(0);

// setters and getters
}

宝石类：

@Entity
@Table(name = "gemstone")
public class Gemstone {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "gemstone_id")
    private long gemstoneId;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(
            name = "gemstone_product",
            joinColumns = {@JoinColumn(name = "gemstone_id")},
            inverseJoinColumns = {@JoinColumn(name = "product_id")}
    )
    private Set<Product> products = new HashSet<>(0);

// setters and getters
}

Answer 1

实际上，我们在这里需要的SQL查询非常简单：

SELECT t1.product_id 
FROM gemstone_product AS t1 
WHERE (t1.gemstone_id IN ?1 )  # (51, 46)
GROUP BY  t1.product_id  
HAVING (COUNT(t1.gemstone_id) = ?2) # 2 - # of items

有点令人沮丧的是，用JPA创建它并不容易，但可以用FluentJPA（生成上面的查询）来完成：

public List<Integer> getProductsContainingAllStones(List<Long> gemstoneIds) {
    int count = gemstoneIds.size();

    FluentQuery query = FluentJPA.SQL((Gemstone gemstone,
                                       JoinTable<Gemstone, Product> gemstoneProduct) -> {

        discardSQL(gemstoneProduct.joinBy(gemstone.getProducts()));

        long productId = gemstoneProduct.getInverseJoined().getProductId();
        long gemstoneId = gemstoneProduct.getJoined().getGemstoneId();

        SELECT(productId);
        FROM(gemstoneProduct);
        WHERE(gemstoneIds.contains(gemstoneId));
        GROUP(BY(productId));
        HAVING(COUNT(gemstoneId) == count);
    });
    return query.createQuery(em).getResultList();
}

有关其工作原理的更多详细信息，请参见here