从多个相关实体获取数据的HQL或SQL查询

Question

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

关系：

• 产品many2many宝石

我正在尝试编写一个查询，以获得具有Gemstone A、Gemstone B和Gemstone C的产品。诸若此类。

用例：

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

查询：

filterGemstones()方法将希望筛选产品的宝石用户返回给。使用下面的查询可以获得零记录，但如果删除HAVING Count(DISTINCT p.product_id) = 2，则会得到产品id 4、5

​

​

• 总部： createQuery(从产品p中选择p.productId加入p.gemstones g，其中g在:宝石组按p有计数(distinct p) =“+ filterGemstones().size() ).setParameter”(“宝石”，filterGemstones())；
• hibernate生成的SQL： 从产品p内接p.product_id中选择gemstone_product gp ON p.product_id = gp.product_id内连接宝石g ON gp.gemstone_id = g.gemstone_id，其中g.gemstone_id IN ( 51，46 )组按p.product_id有计数(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.join(gemstone, 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();
}

有关其工作方式的更多细节可以找到这里。