如何用CriteriaBuilder实现Post与TranslationValue的非关联Left Join？

数据库结构 #

• title和content列与translations_keys表为一对一关系
• 每个translations_keys对应多个translations_values

需求与可用JPQL查询 #

需要读取按请求语言翻译相关字段的帖子，以下是可正常运行的JPQL查询：

@Query(value = """
    SELECT i AS template,
           tv_n.value AS title,
           tv_d.value AS content
      FROM Post i
 LEFT JOIN TranslationValue tv_n
        ON tv_n.key = i.title
       AND tv_n.languageIdentifier = ?1
 LEFT JOIN TranslationValue tv_d
        ON tv_d.key = i.content
       AND tv_d.languageIdentifier = ?1
""")
public Page<PostView> findAll(int languageIdentifier);

问题场景 #

尝试用CriteriaBuilder重写该查询但无法正常工作，核心问题是Post实体与TranslationValue实体无直接关联映射，不知道如何正确执行Left Join操作。以下是错误的尝试代码：

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<PostView> query = criteriaBuilder.createQuery(PostView.class);
Root<Post> root = query.from(Post.class);

Root<TranslationValue> translation = query.from(TranslationValue.class);
root.join("title", JoinType.LEFT).on(
    criteriaBuilder.equal(translation.get("key"), root.get("title")),
    criteriaBuilder.equal(translation.get("languageIdentifier"), 3)
);
root.join("title", JoinType.LEFT).on(
    criteriaBuilder.equal(translation.get("key"), root.get("content")),
    criteriaBuilder.equal(translation.get("languageIdentifier"), 3)
);

Post实体映射 #

@Entity
@Data
@Table(name = "posts")
@NamedQuery(name = "Post.findAll", query = "SELECT i FROM Post i")
public class Post implements Serializable {

    private static final long serialVersionUID = 1L;

    @Column(name = "identifier")
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer identifier;

    @Column(name = "title", insertable = false, updatable = false)
    private Integer titleIdentifier;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "title")
    public TranslationKey title;

    @Column(name = "content", insertable = false, updatable = false)
    private Integer contentIdentifier;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "content")
    private TranslationKey content;

    // Getter & Setter

}

正确的CriteriaBuilder实现 #

方案1：通过TranslationKey关联（推荐） #

前提是TranslationKey实体中定义了与TranslationValue的一对多关联：

@OneToMany(mappedBy = "key")
private List<TranslationValue> translationValues;

同时TranslationValue实体包含：

@ManyToOne
@JoinColumn(name = "key_id")
private TranslationKey key;

重写后的CriteriaBuilder代码：

public Page<PostView> findAll(int languageIdentifier, Pageable pageable) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<PostView> cq = cb.createQuery(PostView.class);
    Root<Post> postRoot = cq.from(Post.class);

    // 左连接title对应的TranslationValue（通过TranslationKey关联）
    Join<TranslationKey, TranslationValue> titleTranslation = postRoot.join("title", JoinType.LEFT)
            .join("translationValues", JoinType.LEFT);
    titleTranslation.on(cb.equal(titleTranslation.get("languageIdentifier"), languageIdentifier));

    // 左连接content对应的TranslationValue（通过TranslationKey关联）
    Join<TranslationKey, TranslationValue> contentTranslation = postRoot.join("content", JoinType.LEFT)
            .join("translationValues", JoinType.LEFT);
    contentTranslation.on(cb.equal(contentTranslation.get("languageIdentifier"), languageIdentifier));

    // 构造PostView结果对象
    cq.select(cb.construct(
            PostView.class,
            postRoot,
            titleTranslation.get("value"),
            contentTranslation.get("value")
    ));

    // 分页处理
    TypedQuery<PostView> typedQuery = entityManager.createQuery(cq);
    typedQuery.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
    typedQuery.setMaxResults(pageable.getPageSize());

    List<PostView> content = typedQuery.getResultList();
    // 统计总数
    CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    countQuery.select(cb.count(countQuery.from(Post.class)));
    long total = entityManager.createQuery(countQuery).getSingleResult();

    return new PageImpl<>(content, pageable, total);
}

方案2：直接关联Post与TranslationValue（JPA 2.1+） #

如果不想通过TranslationKey中转，可直接基于JPQL的逻辑用CriteriaBuilder实现：

public Page<PostView> findAll(int languageIdentifier, Pageable pageable) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<PostView> cq = cb.createQuery(PostView.class);
    Root<Post> postRoot = cq.from(Post.class);

    // 左连接title对应的TranslationValue
    Join<Post, TranslationValue> titleTv = cb.leftJoin(TranslationValue.class, tv ->
            cb.and(
                    cb.equal(tv.get("key"), postRoot.get("title")),
                    cb.equal(tv.get("languageIdentifier"), languageIdentifier)
            )
    );

    // 左连接content对应的TranslationValue
    Join<Post, TranslationValue> contentTv = cb.leftJoin(TranslationValue.class, tv ->
            cb.and(
                    cb.equal(tv.get("key"), postRoot.get("content")),
                    cb.equal(tv.get("languageIdentifier"), languageIdentifier)
            )
    );

    // 映射结果到PostView
    cq.select(cb.construct(
            PostView.class,
            postRoot,
            titleTv.get("value"),
            contentTv.get("value")
    ));

    // 分页与总数统计
    TypedQuery<PostView> typedQuery = entityManager.createQuery(cq);
    typedQuery.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
    typedQuery.setMaxResults(pageable.getPageSize());

    List<PostView> content = typedQuery.getResultList();
    long total = entityManager.createQuery(cq.select(cb.count(postRoot))).getSingleResult();

    return new PageImpl<>(content, pageable, total);
}

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