Returning a list from a ManyToMany relationship?

Question

I have two tables: Users and Roles. A user may have more roles, so this is a ManyToMany relationship. I've generated the entity classes with Netbeans, and they look like this:

@Table(name = "users")
public class Users implements Serializable {
    @Id
    @Basic(optional = false)
    @Column(name = "user_name")
    private String userName;
    @JoinTable(name = "user_roles", joinColumns = {
        @JoinColumn(name = "user_name", referencedColumnName = "user_name")}, inverseJoinColumns = {
        @JoinColumn(name = "role_name", referencedColumnName = "role_name")})
    @ManyToMany(fetch = FetchType.LAZY)
    private Collection<Roles> rolesCollection;

(etc)

@Table(name = "roles")
public class Roles implements Serializable {
    @Id
    @Basic(optional = false)
    @Column(name = "role_name")
    private String roleName;
    @ManyToMany(mappedBy = "rolesCollection", fetch = FetchType.LAZY)
    private Collection<Users> usersCollection;

Here is my problem: I'd like to list all users in a table, and for each user all the roles they have. I couldn't come up with a solution. How do I refer to the collections from within JPQL? How do I return a collection? Is this possible at all?

Even worse, I have my own class that I'd like to use to display the results on my JSF page. Here is the current query:

Query query = em.createQuery("SELECT NEW UserListQueryObject"
        + "(u.userName, ...)"
        + " FROM Users u",
        UserListQueryObject.class);
List<UserListQueryObject> users = query.getResultList();

It works for simple attributes and OneToOne relationships. But not for ManyToMany. I tried JOINing like this:

Query query = em.createQuery("SELECT NEW UserListQueryObject"
        + "(u.userName, ...., r.roleName)"
        + " FROM Users u JOIN u.rolesCollection r",
        UserListQueryObject.class);

... but the runtime complained about bad syntax near "r", and also about r.roleName. And it wanted to return a single role, but I need all the roles for a user!

I'm not an expert at SQL either, so it is quite possible that the solution is simple... still after having read the "Pro JPA 2..." book I'm stuck. Thanks for any help.

Answer 1

I'd like to list all users in a table, and for each user all the roles they have. (...)

If you want to read multiple objects in a single query, you can use a join fetch:

SELECT u FROM Users u JOIN FETCH u.rolesCollection

This will perform an INNER join which means that this will filter any Users from the result set that did not have roles.

To avoid the above behavior, use an OUTER join:

SELECT u FROM Users u LEFT JOIN FETCH u.rolesCollection