如何为多对多关系实体的SQL查询构建JPA Specification

现有以下JPA实体：

@Entity
@Table(name = "advertisement")
public class AdEntity {
   @Id
   @GeneratedValue(strategy = GenerationType.UUID)
   private String id;
   
   @ManyToMany(fetch = FetchType.LAZY, cascade = { CascadeType.PERSIST, CascadeType.MERGE })
   @JoinTable(name = "ad_tags", joinColumns = {@JoinColumn(name = "ad", foreignKey = @ForeignKey(name = "fk_at_ad")) }, inverseJoinColumns = {@JoinColumn(name = "tag", foreignKey = @ForeignKey(name = "fk_at_tag")) })
   private Set<TagEntity> tags;
   // 其他字段和方法省略
}

@Entity
@Table(name = "tag")
public class TagEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private String id;

    @ManyToMany(mappedBy = "tags")
    private Set<AdEntity> ads;
    // 其他字段和方法省略
}

需求是筛选出包含所有指定标签的广告，普通的IN子句只能返回至少包含一个指定标签的广告，无法满足需求。已经写出符合要求的SQL查询：

select ad.id from advertisement ad
where ad.id in (select at.ad x
from ad_tags at
join tag t on t.id = at.tag
where t.name in ('tag1', 'tag2', 'tag3')
group by x
having count(tag) = 3)

现在需要将该SQL转换为JPA Specification，尝试编写的代码无法正常运行，现有代码如下：

public static Specification<AdEntity> filterTags(List<TagEntity> tags) {
    
  return (root, query, criteriaBuilder) -> {
            
    query.distinct(true);

            
    Subquery<TagEntity> tagSubquery = query.subquery(TagEntity.class);
    Root<TagEntity> tagRoot = tagSubquery.from(TagEntity.class);
            
    Expression<Collection<AdEntity>> adTags = tagRoot.get("ads");
            
    Expression<Long> countExpression = criteriaBuilder.count(root);
            
    query.multiselect(root.get("tags"), countExpression);
            
    query.groupBy(root.get("tags"));
            
    Predicate havingPredicate = criteriaBuilder.equal(countExpression, tags.size());
            
    query.having(havingPredicate);      
    tagSubquery.select(tagRoot);        
    tagSubquery.where(tagRoot.in(tags), criteriaBuilder.isMember(root, adTags));
    
            
    return criteriaBuilder.exists(tagSubquery);
  };
            
}

请问正确的实现方式是什么？

要实现和目标SQL等价的JPA Specification，需要按照子查询的逻辑构建：先筛选出关联了所有指定标签的广告ID，再用主查询匹配这些ID。以下是正确代码：

public static Specification<AdEntity> filterTags(List<TagEntity> tags) {
    return (root, query, criteriaBuilder) -> {
        // 子查询：获取关联了所有指定标签的广告ID
        Subquery<String> subquery = query.subquery(String.class);
        Root<AdEntity> subRoot = subquery.from(AdEntity.class);
        Join<AdEntity, TagEntity> tagJoin = subRoot.join("tags");

        // 筛选指定标签
        subquery.where(tagJoin.in(tags));
        // 按广告ID分组
        subquery.groupBy(subRoot.get("id"));
        // 分组后标签数量等于指定标签的数量，确保广告包含所有标签
        subquery.having(criteriaBuilder.equal(criteriaBuilder.count(tagJoin), tags.size()));
        // 子查询选择广告ID
        subquery.select(subRoot.get("id"));

        // 主查询：匹配子查询返回的广告ID
        return criteriaBuilder.in(root.get("id")).value(subquery);
    };
}

代码说明： #

• 子查询从AdEntity出发，关联tags集合，筛选出匹配指定标签的记录
• 按广告ID分组后，通过having子句校验分组内的标签数量与传入的标签列表长度一致，确保该广告包含所有指定标签
• 主查询通过in条件匹配子查询结果，最终得到符合要求的广告

如果需要通过标签名称而非TagEntity对象筛选，可以修改子查询条件：

public static Specification<AdEntity> filterTagsByNames(List<String> tagNames) {
    return (root, query, criteriaBuilder) -> {
        Subquery<String> subquery = query.subquery(String.class);
        Root<AdEntity> subRoot = subquery.from(AdEntity.class);
        Join<AdEntity, TagEntity> tagJoin = subRoot.join("tags");

        subquery.where(tagJoin.get("name").in(tagNames));
        subquery.groupBy(subRoot.get("id"));
        subquery.having(criteriaBuilder.equal(criteriaBuilder.count(tagJoin), tagNames.size()));
        subquery.select(subRoot.get("id"));

        return criteriaBuilder.in(root.get("id")).value(subquery);
    };
}

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