How to create query using JPA specification on non primary column

Question

I have the below query, where two tables are joining on non primary column. The table is joining on one common column.

In entity, I have not provided any joining between User and UserDetails, and I don't want to provide either

SELECT * from User user, UserDetails ud
WHERE user.user_key = :pUserKey // passed in parameter
      user.common_key = ud.common_key

User

@Entity
@Table(name = "USER")
@Data
public class User implements java.io.Serializable {

    private static final long serialVersionUID = 1L;
    
    @Id
    @Column(name = "user_key", nullable = false)
    @JsonIgnore
    private Long userKey;
    
    @Column(name = "common_key ", nullable = false)
    @JsonIgnore
    private Long commonKey;
}

UserDetails

@Entity
@Table(name = "USER_DETAILS")
@Data
public class UserDetails implements java.io.Serializable {

    private static final long serialVersionUID = 1L;
    
    @Id
    @Column(name = "user_details_key", nullable = false)
    @JsonIgnore
    private Long userDetailsKey;
    
    @Column(name = "common_key ", nullable = false)
    @JsonIgnore
    private Long commonKey;
}

How to achieve the same query in JPA specification

Why don't you have a relationship between User and UserDetails? — Simon Martinelli, Aug 19 '20 at 11:58
@Simon Martinelli This is the primary requirement of my problem, having no relationship, the only common column is there — SSK, Aug 19 '20 at 12:00
You can use join().on() Checkout https://stackoverflow.com/questions/31120461/how-to-do-join-on-query-using-criteria-api — Simon Martinelli, Aug 19 '20 at 12:10
@Simon Martinelli I have developed the solution as answered below. Thanks for your time :) — SSK, Aug 25 '20 at 04:17

score 1 · Accepted Answer · answered Aug 25 '20 at 04:16

I have got the solution as expected. I have created new root using query.from()

Below is the complete solution

public static Specification<User> userAndDetails(Long userKey) {
        return (Root<User> root, CriteriaQuery<?> query, CriteriaBuilder builder) -> {
        
            Root<UserDetails> userDetailsRoot = query.from(UserDetails.class);

            Predicate userAndDetailsJoin = builder.equal(
                    root.get(User_.commonKey),
                    userDetailsRoot.get(UserDetails_.commonKey));

            Predicate userKeyPredicate = builder.equal(root.get(User_.userKey),
                    userKey);

            return builder.and(userAndDetailsJoin, userKeyPredicate);
        };
    }

How to create query using JPA specification on non primary column

1 Answers1