JPQL and Join Table

java, jpa, jpql

Solution

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

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

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

Problem

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] ``` 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. Relevant code snippets: 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 ``` @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; } ```

Original source