JPA Specification中如何将复杂多属性匹配SQL转换为CriteriaBuilder实现

• 目标是实现多属性组过滤：筛选同时匹配所有指定属性规则的商品，对应原生SQL为多次关联product_attribute中间表，通过内连接特性要求所有属性组条件同时满足。
• 原有实现生成的SQL逻辑错误，核心原因是最后添加了完全不符合业务逻辑的root.in(a1Join,a2Join,a3Join,a4Join)条件，同时代码存在变量名不一致的编译错误。

class Product{
  private Long id;
  private String name;

  @JsonIgnore
  @ManyToMany(cascade = { CascadeType.PERSIST, CascadeType.MERGE })
  @JoinTable(name = "product_attribute", joinColumns = @JoinColumn(name = "product_id"), 
  inverseJoinColumns = @JoinColumn(name = "attribute_id"))
  List<Attribute> attributes = new ArrayList<>();
}

class Attribute{
    private Long id;
    private String name;
    private String value;

    @JsonIgnore
    @ManyToMany(mappedBy = "attributes")
    List<Product> products = new ArrayList<>();
}

SELECT pa1.product_id 
FROM  product_attribute pa1,  
product_attribute pa2, 
product_attribute pa3,
product_attribute pa4
where pa1.attribute_id IN ('A','B')
and pa2.attribute_id IN ('C','D')
and pa3.attribute_id IN ('E','F')
and pa4.attribute_id IN ('G','H')
AND pa2.product_id = pa3.product_id
AND pa1.product_id = pa3.product_id
AND pa1.product_id = pa4.product_id
AND pa2.product_id = pa4.product_id
AND pa3.product_id = pa4.product_id

public class ProductSpecification implements Specification<Product>  {
   @Override
    public Predicate toPredicate(Root<Product> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        // 第一组过滤：品牌为Acer或Dell
        Join<Product, Attribute> a1Join = root.join("attributes", JoinType.INNER);
        Predicate a1Predicate = builder.or(
                builder.and(
                        builder.equal(a1Join.get("name"), "branch"),
                        builder.equal(a1Join.get("value"), "Acer")
                ),
                builder.and(
                        builder.equal(a1Join.get("name"), "branch"),
                        builder.equal(a1Join.get("value"), "Dell")
                )
        );
        a1Join.on(a1Predicate);

        // 第二组过滤：屏幕尺寸为13英寸或15.6英寸
        Join<Product, Attribute> a2Join = root.join("attributes", JoinType.INNER);
        Predicate a2Predicate = builder.or(
                builder.and(
                        builder.equal(a2Join.get("name"), "screen"),
                        builder.equal(a2Join.get("value"), "13 Inch")
                ),
                builder.and(
                        builder.equal(a2Join.get("name"), "screen"),
                        builder.equal(a2Join.get("value"), "15.6 Inch")
                )
       );
        a2Join.on(a2Predicate);

        // 第三组过滤：使用需求为游戏
        Join<Product, Attribute> a3Join = root.join("attributes", JoinType.INNER);
        Predicate a3Predicate = builder.and(
                builder.equal(a3Join.get("name"), "need"),
                builder.equal(a3Join.get("value"), "gaming")
        );
        a3Join.on(a3Predicate);

        // 第四组过滤：内存为32G
        Join<Product, Attribute> a4Join = root.join("attributes", JoinType.INNER);
        Predicate a4Predicate = builder.and(
                builder.equal(a4Join.get("name"), "memory"),
                builder.equal(a4Join.get("value"), "32 G")
        );
        a4Join.on(a4Predicate);

        // 多次join会产生重复商品行，加distinct去重
        query.distinct(true);

        // 所有过滤逻辑已经通过inner join的on子句实现，返回空的永真条件即可
        return builder.conjunction();
    }
}

1. 修正原代码变量名错误：原代码on子句中调用了未定义的criteriaBuilder，实际入参名为builder，会直接触发编译错误。
2. 删除错误的in条件：原代码最后添加的root.in(...)会生成product.id in (属性表主键)的错误逻辑，完全偏离业务需求。
3. 显式指定JoinType.INNER：内连接的特性是只有关联表存在匹配记录时主表数据才会返回，多次对属性集合做内连接、每个连接绑定对应属性组的过滤规则，天然等价于目标SQL里多次关联中间表+product_id等值匹配的逻辑，JPA会根据实体的多对多映射自动生成中间表关联和product_id等值条件，不需要手动编写关联条件。
4. 返回builder.conjunction()生成无额外过滤的永真条件，所有过滤规则已经下沉到join的on子句中执行。

内容的提问来源于stack exchange，提问作者Boycpu