如何在JPA Specification中通过多对多关联子查询按标签列表查Feature

需求与对应SQL #

需要根据输入的标签名称列表（如'Test 1', 'Test 2'），获取所有关联这些标签的Feature，对应SQL如下：

SELECT * FROM feature where id in (select feature_id from label_feature where label_id in (SELECT id FROM label where name in ('Test 1', 'Test 2')));

实体模型 #

Label 实体 #

@Entity
public class Label {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(
            name = "label_feature",
            joinColumns = @JoinColumn(name = "label_id"),
            inverseJoinColumns = @JoinColumn(name = "feature_id"))
    private List<Feature> features = new ArrayList<>();
}

Feature 实体 #

@Entity
public class Feature {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    @ManyToMany(mappedBy = "features", cascade = CascadeType.ALL)
    private List<Label> labels = new ArrayList<>();
}

JPA Specification 实现方案 #

方案一：完全匹配SQL的嵌套子查询 #

该方案严格对应给定的SQL逻辑，通过两层子查询实现：

import org.springframework.data.jpa.domain.Specification;
import javax.persistence.criteria.*;
import java.util.List;

public class FeatureSpecifications {

    public static Specification<Feature> findByLabelNames(List<String> labelNames) {
        return (Root<Feature> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
            // 子查询：获取符合名称条件的Label ID集合
            Subquery<Long> labelIdSubquery = query.subquery(Long.class);
            Root<Label> labelRoot = labelIdSubquery.from(Label.class);
            labelIdSubquery.select(labelRoot.get("id"))
                    .where(cb.in(labelRoot.get("name")).value(labelNames));

            // 子查询：通过中间表获取关联的Feature ID集合
            Subquery<Long> featureIdSubquery = query.subquery(Long.class);
            Root<Object> joinTable = featureIdSubquery.from("label_feature");
            featureIdSubquery.select(joinTable.get("feature_id"))
                    .where(cb.in(joinTable.get("label_id")).value(labelIdSubquery));

            // 主查询：匹配Feature的ID在子查询结果中
            return cb.in(root.get("id")).value(featureIdSubquery);
        };
    }
}

方案二：利用实体关联简化实现 #

借助JPA的多对多关联映射，无需直接操作中间表，代码更简洁：

public static Specification<Feature> findByLabelNamesSimplified(List<String> labelNames) {
    return (Root<Feature> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
        // 关联Feature与Label
        Join<Feature, Label> labelJoin = root.join("labels", JoinType.INNER);
        // 去重，避免同一个Feature因关联多个匹配标签而重复返回
        query.distinct(true);
        // 匹配标签名称
        return cb.in(labelJoin.get("name")).value(labelNames);
    };
}

使用方式 #

将上述Specification传入Spring Data JPA的JpaRepository方法即可：

List<Feature> features = featureRepository.findAll(FeatureSpecifications.findByLabelNames(labelNames));

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