如何在非主列上使用JPA规范创建查询

Question

我有下面的查询，其中两个表连接在非主列上。该表连接到一个公用列上。

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

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

用户详细信息

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

如何在JPA规范中实现相同的查询

Answer 1

我已经得到了预期的解决办法。我已经使用query.from()创建了新的根

以下是完整的解决方案

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