CODEWITHSUNDEEP
springjpajpa-2.0
accursed medal 36
accursed medal 36

Reputation: 13

Join 2 tables using JPA Specifications

I have 2 Tables

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;
}

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 refIfd;
}

I need to write a Specification that joins the 2 tables and fetches List where SpecialSubscription.searchType = specified input and SpecialSubscription.refId = specified input.

Something like this.

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)
        );
    };
}

This is the equivalent sql I would write if it were sql:

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

Tried the code snippet mentioned in description but did not work.

Upvotes: 0

Views: 54

Answers (1)

Andrey B. Panfilov
Andrey B. Panfilov

Reputation: 6053

my issue is that Subscription entity does not have reference of SpecialSubscription in it.

your actual issue is you assuming that traversing associations is the only way to construct joins in Criteria API.

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

Upvotes: 0

Related Questions