JPA：左连接不使用“is null”在where子句中 [英] JPA: Left join not working with "is null" in where clause

问题描述

我在一个非常简单的项目中使用EclipseLink（2.4,2.5和2.6），我有一个 Department 实体和每个 Department 指向员工实体的链接，该实体是部门的经理 。

I am using EclipseLink (2.4, 2.5 and 2.6) on a very simple project where I have a Department entity and each Department links to an Employee entity which is the manager of the Department.

我目前无法使这个简单的查询工作：

I am currently unable to make this simple query work:

select d from Department d where d.manager is null

返回1行

select d from Department d left join fetch d.manager where d.manager is null

返回0行

我在H2数据库上使用Eclipselink。生成的SQL查询似乎不会创建左连接，而是内连接，显然会失败。

I am using Eclipselink over an H2 database. The SQL query generated does not seem to create a left join but rather an inner join which obviously will fail.

SELECT t1.ID, t1.MANAGER_ID, t0.ID, t0.NAME FROM EMPLOYEE t0, DEPARTMENT t1
WHERE ((t1.MANAGER_ID IS NULL) AND (t0.ID = t1.MANAGER_ID))

这是一个错误还是需要的东西？或者有人可以帮我解决这个问题吗？

Is this a bug or is it something wanted? Or could someone help me fixing this?

很高兴提供代码和示例，如果有人想要它或更多信息。

Happy to provide the code and example if anyone wants it, or more information.

推荐答案

使用 FETCH JOIN 引用查询中返回的实体作为副作用似乎是不安全的。 JPA规范尝试至少对多值关联禁止此类查询（JSR 338，第4.4.5.3节）：

It seems unsafe to reference an entity that was returned in a query as a side effect by using a FETCH JOIN. The JPA specification tries to prohibit such queries at least for multi-valued associations (JSR 338, section 4.4.5.3):

它不是允许为 FETCH JOIN 子句右侧引用的对象指定标识变量，因此对隐式获取的实体或元素的引用不能出现查询中的其他地方。

It is not permitted to specify an identification variable for the objects referenced by the right side of the FETCH JOIN clause, and hence references to the implicitly fetched entities or elements cannot appear elsewhere in the query.

您在EclipseLink和Hibernate上执行的查询会产生不同的结果（EclipseLink 2.6.3：没有结果，Hibernate 4.3.11：所有部门都有没有经理）：

Your query executed on EclipseLink and Hibernate yields different results (EclipseLink 2.6.3: no results, Hibernate 4.3.11: all departments with no manager):

select d from Department d left join fetch d.manager where d.manager is null

要解决您的问题，可以使用子查询。可移植的JPQL查询看起来像这样（在EclipseLink和Hibernate中也是如此）：

To solve your problem a subquery could be used. A portable JPQL query could look like this (same results in EclipseLink and Hibernate):

select d1 from Department d1 left join fetch d1.manager where exists 
(select d2 from Department d2 where d2.manager is null and d1 = d2)

这篇关于JPA：左连接不使用“is null”在where子句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！