java - 条件左外连接 SQL 查询到 JPQL

Question

SQL FIDDLE LINK FOR THIS DATA

什么是 jpql 等同于以下 SQL 查询:

select * from App left outer join App_Child on (App.id=App_Child.id and App_Child.status = 'active') where App.status='active' and App.id=1;

示例数据:

ij> select * from App;
ID         |STATUS
----------------------
1          |active
2          |active1
3          |active3
5          |active

4 rows selected
ij> select * from App_Child;
ID         |STATUS    |D
----------------------------------
1          |active    |1
2          |active11  |2
1          |active111 |3
1          |active    |4

4 rows selected
ij> select * from App left outer join App_Child on (App.id=App_Child.id and App_Child.status = 'active') where App.status='active' and App.id=1;
ID         |STATUS    |ID         |STATUS    |D
---------------------------------------------------------
1          |active    |1          |active    |1
1          |active    |1          |active    |4

2 rows selected
ij> select * from App left outer join App_Child on (App.id=App_Child.id and App_Child.status = 'active') where App.status='active' and App.id=5;
ID         |STATUS    |ID         |STATUS    |D
---------------------------------------------------------
5          |active    |NULL       |NULL      |NULL

1 row selected

编辑:我们正在使用 jpa 2.0

Answer 1

由于 @ManyToOne 关系，条件 App.id=App_Child.id 被自动添加。在 JPA 2.1 中，您可以使用显式 on 子句添加其他条件:

select a 
from App a left outer join 
     a.children c on (c.status = 'active') 
where a.status='active' and a.id=1;

参见示例 EclipseLink Documentation .