Spring Data JPA query on a many-to-many relationship is returning all records instead of matching records

Question

I have two entities:

@Entity
@Table(schema = "public", name = "app_user")
public class AppUser implements Serializable {
    @Id
    @Column(name = "id")
    private int id;
    
    @Column(name = "username")
    private String username;
    
    @ManyToMany()
    @JoinTable(
            name = "app_user_app_role",
            joinColumns = {
                @JoinColumn(name = "app_user_id", referencedColumnName = "id")},
            inverseJoinColumns = {
                @JoinColumn(name = "app_role_id", referencedColumnName = "id")})
    private Set<AppRole> roles;
    
}

@Entity
@Table(schema = "public", name = "app_role")
public class AppRole implements Serializable {
    @Id
    @Column(name = "id")
    private int id;
    
    @Column(name = "app_role_name")
    private String roleName;
}

The join table app_user_app_role has three columns: "id", "app_user_id", and "app_role_id", the last two of which are foreign keys to the corresponding tables.

I have the following JpaRepository method:

@Repository
public interface AppUserRepository extends JpaRepository<AppUser, Integer> {
    /**
     * Get a list of usernames for users with the specified role name assigned.
     * @param roleName
     * @return 
     */
    @Query("SELECT au.username FROM AppUser au \n" +
        "INNER JOIN AppRole ar \n" +
        "WHERE ar.roleName = :roleName")
    List<String> getAppUsersWithRole(@Param("roleName") String roleName);
}

I have three users, "johndoe", "sam", and "janegoodall". I have two roles, "User Admin" and "Test". "johndoe" and "sam" both have the "User Admin" roles, and "sam" has the "Test" role. "janegoodall" has no roles.

When I call AppUserRepository.getAppUsersWithRole("User Admin"), I get all three users' usernames rather than "johndoe" and "sam". When I call AppUserRepository.getAppUsersWithRole("Test"), I also get all three users' usernames rather than just "sam". When I call AppUserRepository.getAppUsersWithRole("nonexistent role"), I get an empty list as expected.

When I retrieve the individual user records and check their AppRole list they all have the expected roles, so I know the @ManyToMany and @JoinTable in AppUser are set up correctly.

Why is my jpql query returning all usernames instead of the usernames of the users who have roles matching the :roleName parameter? (And why does it return no usernames when the role doesn't exist?)

Answer 1

Seems like you are not specifying the relationship between the AppUser and AppRole entities in your INNER JOIN statement. You should be using the roles field in the AppUser entity to establish it:

@Repository
public interface AppUserRepository extends JpaRepository<AppUser, Integer> {
    /**
     * Get a list of usernames for users with the specified role name assigned.
     * @param roleName
     * @return 
     */
    @Query("SELECT au.username FROM AppUser au " +
        "INNER JOIN au.roles ar " +
        "WHERE ar.roleName = :roleName")
    List<String> getAppUsersWithRole(@Param("roleName") String roleName);
}

If the role doesn't exist then no rows are returned because ar.roleName = :roleName will never be true.