左联接查询DSL和JPA中不相关的表 [英] Left join on unrelated tables in Query DSL and JPA

问题描述

我有两个不相关的表，每个表都带有外地电子邮件.我需要一个查询，如果电子邮件匹配，该查询将引入第二个表中的列；如果找不到匹配，则将为null.在SQL中，这很容易:

I have two unrelated tables, each one with field email. I need a query which introduces column taken from second table if emails match or will be null if no match is found. In SQL this is easy:

SELECT tableA.id, tableA.email, tableB.name
 FROM tableA
 LEFT JOIN tableB ON tableA.email=tableB.email
 ORDER BY tableB.name

不幸的是，JPA不允许通过不相关实体进行联接，因此我将其转换为:

Unfortunately JPA doesn't allow joins over unrelated entities so I converted it to:

SELECT tableA.id, tableA.email,
       (SELECT tableB.name FROM tableB WHERE tableB.email=tableA.email) AS aname
  FROM tableA
  ORDER BY aname 

现在，它可以用作JPA查询，但是我们使用的是Query DSL，因此我们开始对其进行转换:

Now, it works as JPA query but we are using Query DSL so off we go to converting it:

JPQLQuery query = new JPAQuery(em);
List<Dto> items=query.from(qTableA)
  .list(new QDto(qTableA.id, qTableA.email,
                 new JPASubQuery().from(qTableB)
                  .where(qTableB.email.eq(qTableA.email)).unique(qTableB.name)))

它可以工作，但是现在我不知道如何通过子查询引入的字段来实现排序和过滤.

It works but now I have no idea how to implement sorting and filtering by field introduced by subquery.

Dto是用于收集结果的POJO，QDto是从Dto自动生成的类.

Dto is a POJO used to collect results, QDto is a class autogenerated from Dto.

问题是:如何使用Query DSL和JPA联接两个不相关的表并避免使用本机SQL?是否有可能?必须对tableA和tableB.name中的字段进行排序和过滤.

Question is: how to join two unrelated tables using Query DSL and JPA and avoiding native SQL? Is it possible? Sorting and filtering on fields from tableA and tableB.name is a requirement.

推荐答案

最新JPA规范(2.1)未涵盖不相关实体的加入

Join on unrelated entities is not covered by latest JPA spec (2.1)

但是，Hibernate 5.1.0+和EclipseLink 2.4.0+支持临时连接. http://blog.anthavio.net/2016/03 /join-unrelated-entities-in-jpa.html

However Hibernate 5.1.0+ and EclipseLink 2.4.0+ support ad hoc joins. http://blog.anthavio.net/2016/03/join-unrelated-entities-in-jpa.html

这篇关于左联接查询DSL和JPA中不相关的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！