将本机查询映射到 JPQL(具有额外的连接条件>> ON ...和...

Question

JPA实体

组织实体

@Entity
public class Organization implements Serializable{
    private Integer id; // @Id
    private String name;
    // Getters and Setters
}

放置实体

@Entity
public class Place implements Serializable{
    private Integer id; // @Id
    private String name;
    private Organization organization; // @ManyToOne relation
    // Getters and Setters
}

配置文件实体

@Entity
public class Profile implements Serializable{
    private String username;// @Id
    private Set<VisitedPlace> visitedPlaces; // @OneToMany(mappedBy = "profile")
    // Getters and Setters
}

访问场所实体

@Entity
public class VisitedPlace implements Serializable{
    private Integer id; // @Id
    private Date visitedDate;
    private Place place; // @ManyToOne relation
    private Profile profile; // @ManyToOne relation
    // Getters and Setters
}

它将创建这些表，但我也会使用示例数据显示

组织表

 ===================== 
| id |      name      |
-----+----------------+
| 1  | Organization 1 |
 =====================

位置表

 ================================= 
| id |   name   | organization_id |
-----+----------+-----------------+
| 1  | Place 1  |        1        |
-----+----------+-----------------+
| 2  | Place 2  |        1        |
-----+----------+-----------------+
| 3  | Place 3  |        1        |
 =================================

配置文件表

 ============= 
|  username   |
--------------
| [e-mail] |
--------------
| [e-mail] |
 =============

访问场表

 ================================================ 
| id | visiteddate | place_id | profile_username |
-----+-------------+----------+---------------- -+
| 1  | 2017-01-01  |     1    |   [e-mail]    |
-----+-------------+----------+------------------+
| 2  | 2017-02-01  |     2    |   [e-mail]    |
-----+-------------+----------+------------------+
| 3  | 2017-01-15  |     1    |   [e-mail]    |
 ================================================

我的问题是如何将下一个本机SQL查询映射到JPQL

SELECT p.username, count(distinct pl.id) 
FROM profile p 
LEFT OUTER JOIN visitedplace vp
ON p.username = vp.profile_username
LEFT JOIN place place
ON vp.place_id = pl.id AND pl.organization_id = 1
GROUP BY p.username;

所以我将获得下一个结果

 ===================== 
|  username   | count |
 -------------+-------
| [e-mail] |   2   |
 -------------+-------
| [e-mail] |   0   |
 =====================

我已经尝试下一个JPQL

SELECT profile.username, COUNT( DISTINCT place.id )
FROM Profile profile
LEFT OUTER JOIN profile.visitedPlaces visitedPlace
LEFT JOIN visitedPlace.place place
WHERE place.organization.id = 1
GROUP BY profile.username

但确实返回

 ===================== 
|  username   | count |
 -------------+-------
| [e-mail] |   2   |
 =====================

所以我真正的问题是将下一行转换为jpql

左JOIN PLACE PLACE on vp.place_id = pl.id 和 pl.organization_id = 1

Answer 1

不幸的是，在子句上仅支持jpa 2.1 spec。

所以到目前为止，我们不能使用和与 on 子句。

您可以从http://download.oracle.com/otndocs/jcp/persistence-2_1-fr-eval-spec/index.html

获得JPA 2.1规范

因此，希望我们在下一个版本中能得到它。