java JPQL 和连接表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13485752/
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
JPQL and Join Table
提问by user1394884
My understanding of SQL and JPQL are not that great and I have been trying to create a JPQL query of the following sql statement:
我对 SQL 和 JPQL 的理解不是很好，我一直在尝试创建以下 sql 语句的 JPQL 查询：
select group.* from user, user_group, group
where user_group.user_id = user.id
and user_group.group_id = group.id
and user.id = [userID to search]
edit: Woops I forgot to add the search by user id part to the query. I would like to get all groups that a user belongs in.
编辑：糟糕，我忘了将按用户 ID 部分的搜索添加到查询中。我想获取用户所属的所有组。
But I just cannot get the syntax correct. Any help would be greatly appreciated.
但我就是无法获得正确的语法。任何帮助将不胜感激。
Relevant code snippets:
相关代码片段：
Group.java
组.java
@Table(name = "group")
@Entity
public class Group implements Serializable {
@Id
@GeneratedValue
@Column(name = "id")
private Integer id;
@JoinTable(name = "user_group", joinColumns = {
@JoinColumn(name = "group_id", referencedColumnName = "id")}, inverseJoinColumns = {
@JoinColumn(name = "user_id", referencedColumnName = "id")})
@ManyToMany
private Collection<User> userCollection;
}
User.java
用户.java
@Table(name = "user")
@Entity
public class User implements Serializable {
@Id
@NotNull
@GeneratedValue
@Column(name = "id")
private Integer id;
@Column(name = "email", unique=true, nullable=false)
private String email;
@ManyToMany(mappedBy = "userCollection")
private Collection<Group> GroupCollection;
}
回答by guido
Using JPQL it would be:
使用 JPQL 将是：
TypedQuery<Group> query = em.createQuery(
"SELECT DISTINCT g FROM User u LEFT JOIN u.groupCollection g " +
"WHERE u = :user", Group.class);
query.setParameter("user", user);
List<Group> = query.getResultsList();
where emis your EntityManager and useris the instance of the User class for which to load group list. If you only have the user id, change with:
em您的 EntityManager在哪里，是要user为其加载组列表的 User 类的实例。如果您只有用户 ID，请更改为：
TypedQuery<Group> query = em.createQuery(
"SELECT DISTINCT g FROM User u LEFT JOIN u.groupCollection g " +
"WHERE u.id = :user", Group.class);
query.setParameter("user", userId);
It would be better to use a Setor SortedSet(or maybe a Listif the user can be in the same group more than once) instead of a Collection.
最好使用 a Setor SortedSet（或者List如果用户可以多次在同一组中，则使用 a ）而不是 a Collection。
