java JPA/JPQL JOIN 子选择/子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31393246/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JPA/JPQL JOIN Subselect/Subquery
提问by Joachim Schmidt
I have trouble to translate some easy SQL statement to JPQL, because of a used subquery, which is not supported by JPQL.
由于使用了 JPQL 不支持的子查询，我无法将一些简单的 SQL 语句转换为 JPQL。
Can someone give me a hint, how to achieve the same result with JPQL or JPA2 Criteria API?
有人可以给我一个提示，如何使用 JPQL 或 JPA2 Criteria API 实现相同的结果？
Given (Simplified fake data to demonstrate the problem):
鉴于（简化的假数据以证明问题）：
CREATE TABLE person (id integer, name text);
CREATE TABLE phone (id integer, person_id integer, type text, number text);
INSERT INTO person VALUES (1, "John");
INSERT INTO person VALUES (2, "Mike");
INSERT INTO person VALUES (3, "Paul");
INSERT INTO person VALUES (4, "Walter");
INSERT INTO phone VALUES (1, 1, "MOBILE", "+49-123-11111");
INSERT INTO phone VALUES (2, 1, "HOME" , "+49-123-22222");
INSERT INTO phone VALUES (3, 2, "WORK" , "+49-123-33333");
INSERT INTO phone VALUES (4, 4, "MOBILE", "+49-123-44444");
-- Select all from person and their mobile number if possible
-- This query has to be translated to JPQL
SELECT person.name, mobile.number FROM person LEFT JOIN (
SELECT * FROM phone WHERE type = "MOBILE"
) AS mobile ON person.id = mobile.person_id;
Expected result:
预期结果：
| name | number |
|--------|---------------|
| John | +49-123-11111 |
| Mike | |
| Paul | |
| Walter | +49-123-44444 |
Java:
爪哇：
class Person {
String name;
List<Phone> phones;
}
class Phone {
String type;
String number;
}
JPQL (not working as expected :-( ):
JPQL（未按预期工作:-(）：
SELECT person.name, phone.number FROM Person person
LEFT JOIN person.phones AS phone
WHERE phone.type = "MOBILE"
采纳答案by Dragan Bozanovic
SELECT person.name, phone.number
FROM Person AS person LEFT JOIN person.phones AS phone
ON phone.type = 'MOBILE'
You can also replace the ONkeyword with the hibernate specific WITH:
您还可以ON使用特定于休眠的关键字替换关键字WITH：
SELECT person.name, phone.number
FROM Person AS person LEFT JOIN person.phones AS phone
WITH phone.type = 'MOBILE'
回答by Tobias Liefke
First of all according to the Java Persistence Wikibooksome JPA providers like EclipseLinkand TopLinksupport sub selects in the FROMclause - although this is not defined in the JPA spec.
首先，根据Java Persistence Wikibook，一些 JPA 提供程序（如EclipseLink和TopLink）支持FROM子句中的子选择——尽管这在 JPA 规范中没有定义。
In JPA 2.1 you could use LEFT JOINwith ON:
在JPA 2.1你可以使用LEFT JOIN带ON：
SELECT person.name, phone.number
FROM Person person
LEFT JOIN person.phones AS phone ON phone.person = person AND phone.type = 'MOBILE'
Before JPA 2.1 you could use a caseexpression:
在 JPA 2.1 之前，您可以使用case表达式：
SELECT person.name,
CASE WHEN (phone.type = 'MOBILE') THEN phone.number ELSE '' END
FROM Person person
LEFT JOIN person.phones AS phone
But this will just wipe all none-mobilephone numbers - so there will be a row for each phone number of a person, even if he/she has more than one phone number that is not a mobile number.
但这只会擦除所有非手机号码 - 因此一个人的每个电话号码都会有一排，即使他/她有多个不是手机号码的电话号码。
You could use the list aggregationfunction of your database (like LISTAGGin Oracle), if your JPA provider renders these correctly (Hibernate does in most circumstances). This would make sense for the first two options as well - if a person can have more than one mobile number.
您可以使用数据库的列表聚合功能（如LISTAGG在 Oracle 中），如果您的 JPA 提供程序正确呈现这些功能（Hibernate 在大多数情况下都可以）。这对于前两个选项也有意义 - 如果一个人可以拥有多个手机号码。
回答by Paul Vargas
Try this:
试试这个：
SELECT person.name, phone.number
FROM Person person
LEFT JOIN person.phones AS phone
WHERE phone.type IS NULL OR phone.type = "MOBILE"
