JPQL和Join Table [英] JPQL and Join Table

问题描述

我对SQL和JPQL的理解并不是很好，我一直在尝试创建以下sql语句的JPQL查询：

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:

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]

编辑：Woops我忘了将用户ID部分的搜索添加到查询中。我想获得用户所属的所有群组。

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.

但我无法正确理解语法。任何帮助将不胜感激。

But I just cannot get the syntax correct. Any help would be greatly appreciated.

相关代码段：

Group.java

Group.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

User.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;
}

推荐答案

使用JPQL它将是：

Using JPQL it would be:

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();

其中 em 是你的EntityManager和 user 是要加载组列表的User类的实例。如果您只有用户ID，请更改为：

where em is your EntityManager and user is the instance of the User class for which to load group list. If you only have the user id, change with:

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);

最好使用 Set 或者 SortedSet （或者如果用户可以多次在同一组中，可以是 List ）而不是收藏。

It would be better to use a Set or SortedSet (or maybe a List if the user can be in the same group more than once) instead of a Collection.

这篇关于JPQL和Join Table的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！