java - JPA/JPQL JOIN 子选择/子查询

Question

我无法将一些简单的 SQL 语句转换为 JPQL，因为使用了 JPQL 不支持的子查询。

谁能给我一个提示，如何使用 JPQL 或 JPA2 Criteria API 获得相同的结果？

给定(简化的假数据来证明问题):

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;

预期结果:

| 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(没有按预期工作 :-( ):

SELECT person.name, phone.number FROM Person person
    LEFT JOIN person.phones AS phone
    WHERE phone.type = "MOBILE"

Answer 1

SELECT person.name, phone.number 
   FROM Person AS person LEFT JOIN person.phones AS phone 
   ON phone.type = 'MOBILE'

您还可以将 ON 关键字替换为 hibernate 特定的 WITH:

SELECT person.name, phone.number 
   FROM Person AS person LEFT JOIN person.phones AS phone 
   WITH phone.type = 'MOBILE'