Mulitple JOIN in Spring JpaRepository @Query-CodePudding

I have a User entity which has Role. The Role entity has Privileges. My entities look like this :

@Entity
public class User {

    @ManyToOne
    public Role role;

    public Role getRole() {
        return role;
    }

    public void setRole(Role role) {
        this.role = role;
    }
}


@Entity
public class Role {

    @ManyToMany
    private Set<Privilege> privileges;
    
    public Set<Privilege> getPrivileges() {
        return privileges;
    }
    
    public void setPrivileges(Set<Privilege> privileges) {
        this.privileges = privileges;
    }

}


@Entity
public class Privilege {

    private String action;
    
    public String getAction() {
        return action;
    }
    
    public void setAction(String action) {
        this.action = action;
    }

}

I would like to load a User with his Role and Privileges of this Role.

I tried to define a method in my JpaRepository with the @Query annotation


public interface UserRepository extends JpaRepository<User, Long> {

    Optional<User> findByEmail(String email);
    
    @Query("SELECT u FROM User u "  
            "INNER JOIN u.role r "  
            "INNER JOIN r.privileges p "  
            "WHERE u.id = ?1")
    Optional<User> findByIdWithRoleAndPrivileges(Long id);

}

My User is loaded with his Role object, but not With the Privileges of the Role. Any idea on what i'm doing wrong ?

CodePudding user response：

By default most associations are fetched lazily. To eagerly fetch the many-to-many Role-Privilege association change your query to:

@Query("SELECT u FROM User u "  
        "INNER JOIN FETCH u.role r "  
        "INNER JOIN FETCH r.privileges p "  
        "WHERE u.id = ?1")

The 'JOIN FETCH' is what causes JPA/Hibernate to eagerly fetch the associated entities.