JPQL query to fetch all roles of user based on user id

Question

In my project I have many-to-many relationship between User and Role. For this reason I have also new entity UserRole which connects this two entities.

It looks like this:

User:

@Data
@Entity
@Table(NAME = "USERS")
public class User {
    @Id
    @Column(name = "USER_ID")
    private String userId;
    @Basic
    @Column(name = "EMAIL")
    private String email;
   
    @OneToMany(fetch = LAZY, mappedBy = "user")
    private Set<UserRole> userRoles;
}

Role:

@Data
@Entity
@Table(NAME = "ROLES")
public class Role {
    @Id
    @Column(name = "ROLE_ID")
    private String roleId;
    @Basic
    @Column(name = "NAME")
    private String name;
   
    @OneToMany(fetch = LAZY, mappedBy = "role")
    private Set<UserRole> userRoles;
}

UserRole:

@Data
@Entity
@IdClass(UserRolePK.class)
@Table(NAME = "USER_ROLES")
public class UserRole {
    @Id
    @Column(name = "USER_ID")
    private String userId; 

    @Id
    @Column(name = "ROLE_ID")
    private String roleId;
    
    @ManyToOne(fetch = LAZY)
    @JoinColumn(name = "USER_ID", insertable = false, updatable = false)
    private User user;

    @ManyToOne(fetch = LAZY)
    @JoinColumn(name = "ROLE_ID", insertable = false, updatable = false)
    private Role role;
}

In this scenario User can have multiple roles.

Question: How to fetch User by his id (userId) with all assigned to him Roles using one query (JPQL)?

I know I can first fetch User by id, and than I can fetched separately Roles based on UserRole table.

But I want to do that in one query. I want to have User with List of Roles.

Answer 1

I would suggest you to correct your mapping in the following way:

@Data
@Entity
@Table(NAME = "USERS")
public class User {
    @Id
    @Column(name = "USER_ID")
    private String userId;

    @Column(name = "EMAIL")
    private String email;
   
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "USER_ROLES",
              joinColumns = @JoinColumn(name = "USER_ID"),
              inverseJoinColumns = @JoinColumn(name = "ROLE_ID"))
    private Set<Role> roles;
}

@Data
@Entity
@Table(NAME = "ROLES")
public class Role {
    @Id
    @Column(name = "ROLE_ID")
    private String roleId;

    @Column(name = "NAME")
    private String name;
   
    @ManyToMany(fetch = FetchType.LAZY, mappedBy = "roles")
    private Set<User> users;
}

This correction will not affect the database schema only hibernate mapping. Then you will be able to do as suggested in the Andronicus answer:

@Query(
    "select u " +
    "from User u " +
    "left join fetch u.roles "+
    "where u.userId = :id "
)
List<User> getUsersWithFetchedRoles(@Param("id") String id)

If you stay with your current mapping you will not be able to fetch more than one association at a time as it is explained in this article.

Additional details related to the @ManyToMany association see in the documentation.