java HQL 左连接条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39778877/
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
HQL left join with condition
提问by ChambreNtheitroade
This may seem basic but it's late and I'm having trouble with the following.
这可能看起来很基本，但为时已晚，我在以下方面遇到了麻烦。
class Group {
@Id
String id;
}
class Participation {
@Id
String id;
@ManyToOne
@JoinColumn(name = "GROUP_ID")
Group group;
@ManyToOne
@JoinColumn(name = "USER_ID")
User user;
}
class User {
@Id
String id;
@OneToMany(mappedBy = "user", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
Set<Participation> participations;
}
So
所以
Participation -->1 Group
参与-->1组
and User 1<-->N Participation
和用户 1<-->N 参与
How can I retrieve all Groups with, for a given User, the associated Participation (or null is there is none)? I've been playing with join fetches but to no avail so far...
对于给定的用户，我如何检索所有具有关联参与的组（或 null 表示没有参与）？我一直在玩连接提取，但到目前为止无济于事......
Many thanks,
非常感谢，
CN
中文网
PS. I can do this in SQL thus :
附注。因此，我可以在 SQL 中执行此操作：
select g.d, p.id
from group as g
left join participation as p
on p.group_id = g.id and p.user_id = 2;
回答by Adrian Shum
(Probably some typo in the HQL itself but the idea should be correct)
（可能是 HQL 本身的一些错字，但这个想法应该是正确的）
What you are asking, based on your SQL and description, is find out all Participation(and its corresponding Group) based on User, which is simply
根据您的 SQL 和描述，您要问的是基于 找出所有Participation（及其对应的Group）User，这只是
select p.id, p.group.id from Participation p where p.user.id = :userId
To make it better, you should fetch the entities instead:
为了使它更好，您应该取而代之的是获取实体：
from Participation p left join fetch p.group where p.user.id = :userId
There were some confusions in understanding what you were trying to do: You want all groups (regardless of condition). And, for a given user, you want to find all groups and participations that user is involved.
在理解您想要做什么时存在一些混淆：您想要所有组（无论条件如何）。并且，对于给定的用户，您希望找到该用户所涉及的所有组和参与。
Though it should be possible using Right-outer-join:
虽然使用 Right-outer-join 应该是可能的：
select g, p from Participation p
right outer join p.group g
where p.user.id=:userId
Or, in later version of Hibernate (>= 5.1 ?), it allow explicit join (haven't tried before, you may give it a try) (Replace withwith onif you are using JPQL):
或者，在更高版本的 Hibernate (>= 5.1 ?) 中，它允许显式连接（以前没有尝试过，您可以尝试一下）（如果您使用的是 JPQL with，on则替换为）：
select g, p from Group g
left outer join Participation p
with p.group = g
left outer join p.user u
where u.id = :userId
Or you may use other techniques such as subquery etc. However I would rather separate them into simpler queries in your case and do a simple aggregation in your code.
或者您可以使用其他技术，例如子查询等。但是，在您的情况下，我宁愿将它们分成更简单的查询，并在您的代码中进行简单的聚合。
The basic idea is to have
基本思想是有
- Query for all groups:
from Groups - Query for all participations for a user:
from Participation p join fetch p.group where p.user.id=:userId
- 查询所有组：
from Groups - 查询一个用户的所有参与情况：
from Participation p join fetch p.group where p.user.id=:userId
Then you can aggregate them easily to the form you want, e.g. Map<Group, List<Participation>>, or even more meaningful value object.
然后您可以轻松地将它们聚合为您想要的形式，例如Map<Group, List<Participation>>，或者甚至更有意义的值对象。
The benefit is the data access query is simpler and more reusable (esp if you are wrapping them in DAO/Repository finder method). One more round trip to DB shouldn't cause any obvious performance impact here.
好处是数据访问查询更简单且更可重用（尤其是如果您将它们包装在 DAO/Repository finder 方法中）。再一次到 DB 的往返不应该在这里造成任何明显的性能影响。
回答by Dherik
You need to map the participationrelationship in the Groupentity. If the relationship between Participationand Groupis 1..N:
您需要映射实体中的participation关系Group。如果之间的关系Participation，并Group为1..1：
class Group {
String id
List<Participation> participations
}
The JPQL can be:
JPQL 可以是：
SELECT g.id, p.id FROM Group g
JOIN g.participations p
JOIN p.user user
WHERE user.id = :idUser
You can receive this information as List<Object[]>(Object[0]is group id and Object[1]is participation id) or use SELECT NEW.
您可以将这些信息作为List<Object[]>(Object[0]是组 id 和Object[1]参与 id) 或使用SELECT NEW 接收。
Withoutmap the Group/Participation relationship, you can do:
不映射组/参与关系，您可以执行以下操作：
SELECT g.id, p.id FROM Group g, Participation p
JOIN p.user user
JOIN p.group groupp
WHERE g.id = groupp.id
AND user.id = :idUser
But you can't do a LEFT JOINusing this strategy. The query above behaviours like a JOIN.
但是你不能LEFT JOIN使用这种策略。上面的查询行为类似于JOIN.
It's normal with Hibernate you map the side of a relationship that you would like to make a query. So, I recommend the first approach, mapping the relationship.
使用 Hibernate 映射关系的一侧是正常的，您希望进行查询。所以，我推荐第一种方法，映射关系。
