如何使用JPA Specifications关联两张表并实现指定条件查询？

现有两个实体类对应的数据库表：

Subscription实体

public class Subscription {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(updatable = false, nullable = false)
    private Long id;
    private Integer subscriptionTypeId;
    private Boolean active;
    private Long subscriberId;
}

SpecialSubscription实体

public class SpecialSubscription {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(updatable = false, nullable = false)
    private Long id;

    @ManyToOne(targetEntity = Subscription.class, fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "subscriptionId", referencedColumnName = "id")
    private Subscription subscription;

    private String searchType;
    private Long refId; // 注：原代码中为refIfd，推测是笔误，按需求修正为refId
}

需求：编写一个Specification，关联两张表，查询出满足SpecialSubscription.searchType等于指定输入、SpecialSubscription.refId等于指定输入的Subscription列表，等效SQL如下：

select s.* from subscriptions s, special_subscriptions ss where s.id = ss.subscription_id and ss.search_type = ? and ss.ref_id = ?;

尝试的代码未生效：

public static Specification<Subscription> bySearchTypeAndRefId(String searchType, Long refId) {
    return (root, query, builder) -> {
        Join<Subscription, SpecialSubscription> join =
            root.join(SpecialSubscription_.subscription.toString());
        return builder.and(builder.equal(join.get(SpecialSubscription_.searchType), searchType),
                builder.equal(join.get(SpecialSubscription_.normalizedRefId), refId)
        );
    };
}

错误分析 #

1. Join方式错误：root是Subscription类型，它并没有名为SpecialSubscription_.subscription的属性，这种写法会导致关联失败。正确的关联逻辑应该是通过SpecialSubscription中的subscription字段与Subscription的id关联。
2. 字段名不匹配：代码中使用SpecialSubscription_.normalizedRefId，但实体类中对应的字段是refId（或原代码的refIfd），字段名不匹配会导致查询条件失效。
3. 缺少关联条件：未显式指定两张表的关联关系（s.id = ss.subscription_id），导致关联逻辑不完整。

正确的Specification实现 #

方法一：使用Join关联查询 #

public static Specification<Subscription> bySearchTypeAndRefId(String searchType, Long refId) {
    return (root, query, builder) -> {
        // 关联SpecialSubscription表，指定INNER JOIN
        Join<Subscription, SpecialSubscription> join = root.join(SpecialSubscription.class, JoinType.INNER);
        // 关联条件：Subscription.id = SpecialSubscription.subscription.id
        Predicate joinCondition = builder.equal(root.get("id"), join.get("subscription").get("id"));
        // 查询条件：searchType和refId匹配
        Predicate searchTypeCondition = builder.equal(join.get("searchType"), searchType);
        Predicate refIdCondition = builder.equal(join.get("refId"), refId);
        // 组合所有条件
        return builder.and(joinCondition, searchTypeCondition, refIdCondition);
    };
}

方法二：使用Exists子查询（更适合无反向关联的场景） #

如果Subscription实体没有维护与SpecialSubscription的反向关联，使用子查询的方式更清晰：

public static Specification<Subscription> bySearchTypeAndRefId(String searchType, Long refId) {
    return (root, query, builder) -> {
        // 创建子查询，查询符合条件的SpecialSubscription对应的subscriptionId
        Subquery<Long> subquery = query.subquery(Long.class);
        Root<SpecialSubscription> subRoot = subquery.from(SpecialSubscription.class);
        subquery.select(subRoot.get("subscription").get("id"));
        // 子查询条件
        Predicate subSearchType = builder.equal(subRoot.get("searchType"), searchType);
        Predicate subRefId = builder.equal(subRoot.get("refId"), refId);
        subquery.where(builder.and(subSearchType, subRefId));
        // 主查询条件：Subscription.id存在于子查询结果中
        return builder.in(root.get("id")).value(subquery);
    };
}

额外说明 #

• 如果使用JPA元模型（SpecialSubscription_），确保元模型生成正确，字段名与实体类完全一致，比如SpecialSubscription_.refId而非normalizedRefId。
• 若原实体类中确实是refIfd，则将代码中的refId替换为refIfd即可。

内容的提问来源于stack exchange，提问作者accursed medal 36