mysql - Spring Data JPA 在多列上使用 AND 子句并与 OR 子句组合

Question

我的表结构如下

BrandMerchant(id,brand_id,category_id,merchant_id)

我们的请求中有一个列表，我想创建一个查询:

Select * 
from BrandMerchant 
where (brand_id=1 and merchant_id=2 and category_id=3) 
   OR (brand_id=4 and merchant_id=5 and category_id=6) 
   OR (brand_id=5 and merchant_id=4 and category_id=6)` 

...更多取决于列表大小。

我想要类似于findByBrandIdAndMerchantIdAndCategoryId的东西在集合上( List<BrandMerchant> )

如何使用 spring data jpa 或通过在 java 中生成自定义查询来实现上述语句

Answer 1

这可以通过 FluentJPA 来完成。假设:

• 列表包含品牌、商家和类别 ID 的元组
• BrandMerchant 定义相应的访问属性

public List<BrandMerchant> findMerchants(List<Integer[]> params) {

    Function1<BrandMerchant, Boolean> dynamicFilter = buildOr(params);

    FluentQuery query = FluentJPA.SQL((BrandMerchant m) -> {
        SELECT(m);
        FROM(m);
        WHERE(dynamicFilter.apply(m));
    });
    return query.createQuery(em, BrandMerchant.class).getResultList();
}

private Function1<BrandMerchant, Boolean> buildOr(List<Integer[]> params) {
    Function1<BrandMerchant, Boolean> criteria = Function1.FALSE();

    for (Integer[] tuple : params) {
        int brandId = tuple[0];
        int merchantId = tuple[1];
        int categoryId = tuple[2];
        criteria = criteria.or(m -> m.getBrand().getId() == brandId &&
                                    m.getMerchant().getId() == merchantId &&
                                    m.getCategory().getId() == categoryId);
    }

    return criteria;
}

生成以下 SQL:

SELECT t0.* 
FROM t0 
WHERE (((t0.brand = 1) AND ((t0.merchant = 2) AND (t0.category = 3))) OR
 ((t0.brand = 4) AND ((t0.merchant = 5) AND (t0.category = 6))))

有关 Dynamic Queries 的更多详细信息和 JPA Respositories integration .