title: Join Query with multiple tables in Spring Data JPA
body: <p>I have implemented @ManyToMany relationship but with extra table using @OneToMany and @ManyToOne between <strong>User</strong> ,<strong>Roles</strong> , <strong>Permissions</strong> and  extra tables are "<strong>UserRole</strong>","<strong>RolePermissions</strong>"</p>
  
  <p>one user can have multiple roles and ,one role can also have multiple permissions . I have to get permissions of users on base of roles . so i wrote a query that is working fine </p>
  
  <pre><code>@Query("SELECT DISTINCT p FROM Permission p INNER JOIN p.roles rp INNER JOIN rp.role r INNER JOIN r.users ur INNER JOIN ur.user u WHERE u.id=:userId")
  List&lt;Permission&gt; findAllUserRolePermissions(@Param("userId") long userId);
  </code></pre>
  
  <p>this query looks lengthy,I want to make it short or optimize it if possible.</p>
  
  <p>Entities are Following here</p>
  
  <pre><code>@Entity
  @Table(name = "USER")
  @JsonIgnoreProperties(ignoreUnknown = true)
  public class User {
  
      @JsonIgnore
      @OneToMany(targetEntity = UserRole.class, mappedBy = "user", fetch = FetchType.LAZY)
      private List&lt;UserRole&gt; roles;
  
  
  //Role
  @Entity
  @Table(name = "Role")
  public class Role implements Serializable {
  @OneToMany(targetEntity = UserRole.class, mappedBy = "role", fetch = FetchType.LAZY)
      private List&lt;UserRole&gt; users;
  
      @OneToMany(targetEntity = RolePermission.class, mappedBy = "role", fetch = FetchType.LAZY)
      private List&lt;RolePermission&gt; rolePermissions;
  
  //Use_Role  
  @Entity
  @Table(name = "USER_ROLE")
  @JsonIgnoreProperties(ignoreUnknown = true)
  public class UserRole {
  
      @ManyToOne
      @JoinColumn(name = "USER_ID")
      private User user;
  
      @ManyToOne
      @JoinColumn(name = "ROLE_ID")
      private Role role;
  
  
  
  //Permission
  
  @Entity
  @Table(name = "PERMISSION")
  public class Permission {
  
      @OneToMany(targetEntity = RolePermission.class, mappedBy = "permission", fetch = FetchType.LAZY)
      private List&lt;RolePermission&gt; roles;
  
  
  //Role_Permission
  @Entity
  @Table(name = "ROLE_PERMISSION")
  @JsonIgnoreProperties(ignoreUnknown = true)
  public class RolePermission {
  
      @ManyToOne
      @JoinColumn(name = "ROLE_ID")
      private Role role;
  
      @ManyToOne
      @JoinColumn(name = "PERMISSION_ID")
      private Permission permission;
  </code></pre>
  
  <p>what i want ?</p>
  
  <p>1)is this query fine ? or can be improved if yes then how ?</p>
  
  <p>2)can i achieve this using  spring data interface methods like following</p>
  
  <pre><code>List&lt;Permission&gt; findAllUserRolePermissions(List&lt;Role&gt; roles);// all 
  permissions by roles of user 
  </code></pre>
  
  <p>3)any resource to learn spring data jpa join queries   </p>
  
  <p>any suggestion would be appreciated.thanks </p>