[FIXED] How to attach a table using Query in spring data?

Issue

I have two tables:

@Entity
@Table(name = "users")
@Getter
@Setter
@ToString
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class User {

    @Id
    @Column(name = "user_login")
    private String login;

    @Column(name = "user_name")
    private String name;

    @Column(name = "password")
    private String password;


    @JsonManagedReference
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "user_role",
            joinColumns = @JoinColumn(name = "user_login"),
            inverseJoinColumns = @JoinColumn(name = "role_id")
    )
    private Set<Role> roles;


}

and

@Entity
@Table(name = "roles")
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class Role {
    @Id
    @Column(name = "role_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "role_name")
    private String role;

    @JsonBackReference
    @ManyToMany(mappedBy = "roles")
    private List<User> clients;

    public Role(Long id, String role) {
        this.id = id;
        this.role = role;
    }

    @Override
    public String toString() {
        return "Role{" + "id=" + id + ", role='" + role + '\'' + '}';
    }
}

To work with them, I'm trying to create a repository:

public interface UserRepository extends JpaRepository<User, String> {
    @Query("SELECT u FROM User JOIN FETCH u.roles")
    List<User> findAllUsers();
}

I want to list all users, but I get an error:

 org.hibernate.hql.internal.ast.InvalidPathException: Invalid path: 'u.roles'

I tried to write different queries, but I always get this error. Can someone tell me what's wrong? Without using JPA, it worked, but using it now I get an error.Thanks.

Solution

You didn't specify the alias for User. This should work:

@Query("SELECT u FROM User u JOIN FETCH u.roles")

Answered By - Davide D'Alto
Answer Checked By - Marilyn (JavaFixing Volunteer)