JPA with Hibernate - Many to Many relationship, fetching all data

Question

I have many to many relation ship between User and Roles. For example

public class User {
   @Id
   private Integer id;

   @ManyToMany
   @JoinTable(name = "APP_USER_ROLE", 
     joinColumns = { @JoinColumn(name = "USER_ID") }, 
     inverseJoinColumns = { @JoinColumn(name = "ROLE_ID") })
   private List<Role> roles = new ArrayList<Role>();
}

@Entity     
public class Role {
  @Id
  private Integer id;

  @ManyToMany(mappedBy = "roles")
  private List<User> users = new ArrayList<User>();
}

My requirement is to fetch all users in the system with their roles. When using HQL using this my query is

select u, u.roles from User u

however this fires a query for every user with corresponding roles. If there are 100 users, it will fire 100 queries. However in SQL I can achieve it using follwing query

select u.id, 
u.name, m.roleId, r.name FROM User u left outer join UserRole m ON u.id = m.userId 
inner join Roles r ON m.roleId = r.id  
order by u.id;

I am doing left outer join for user as there are some user who have no associated roles. I prefer not to use native SQL as it has its own disadvantages. Could some one help me to map corresponding HQL or JPQL query?

Answer 1

Try this query

select u from User u left join fetch u.roles