Spring Data JPA - Using @Query with Many to Many Relationships / Join Table

Question

I have a User entity with a many-to-many relationship with a Role entity.

    @Entity
@Table(name = "auth_user")
public class OAuthUser {

    // @Autowired
    // @Transient
    // private PasswordEncoder passwordEncoder;
    //
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "username")
    private String userName;

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

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

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

    @Column(name = "is_enabled")
    private boolean isEnabled;

    /**
     * Reference:
     * https://github.com/nydiarra/springboot-jwt/blob/master/src/main/java/com/nouhoun/springboot/jwt/integration/domain/User.java
     * Roles are being eagerly loaded here because they are a fairly small
     * collection of items for this example.
     */
    @ManyToMany(fetch = FetchType.EAGER)
    @Fetch(value = FetchMode.SUBSELECT)
    @JoinTable(name = "user_role", joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
    private List<Role> roles;

    @ManyToMany(fetch = FetchType.EAGER)
    @Fetch(value = FetchMode.SUBSELECT)
    @JoinTable(name = "user_properties", joinColumns = @JoinColumn(name = "AuthID", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "PropertyID", referencedColumnName = "id"))
    private List<Property> properties;

I am using the Spring Data JPA repositories, and would to be able to create a custom @Query that returns a list of users based upon a particular role id.

    @Query("SELECT u FROM auth_user as u WHERE u.isEnabled AND u.id IN"
        + " (SELECT r.user_id FROM user_role as r WHERE r.role_id = ?1)")
public List<OAuthUser> findByRole(int roleID);

The code above results in the error auth_user is not mapped. I do understand why I am getting the error; the framework is using the entity names (OAuthUser) rather than the table (auth_user) to perform the query. This would ordinarily not be a problem, except for there is no entity for user_role; it is simply a join table with two columns: 'user_id' and 'role_id'.

What is the appropriate way to achieve this?

Thanks.

Hopefully you are NOT storing passwords in clear text, but have just failed to name the field passwordHash! — Klaus Groenbaek, May 22 '18 at 16:33

score 1 · Accepted Answer · answered May 22 '18 at 15:31

1

The error says:

auth_user is not mapped

It refers to the auth_user used in the query like SELECT u FROM auth_user. It must be OAuthUser instead, in the query.

answered May 22 '18 at 15:31

Bhesh Gurung

50,430
22
93
142

Thanks. I understand that. Sorry, if I was not clear but my problem is how do I do that for `user_role` since it is not an `@Entity`, but just a join table? – KellyM May 22 '18 at 15:34
1

Check the answers [here](https://stackoverflow.com/q/33438483/738746), no need to explicitly use the join table in your query. – Bhesh Gurung May 22 '18 at 15:35
1

Thanks. I apologize; I did not realize this would end up being a duplicate. For reference, the correct query for my goals was `@Query("SELECT u FROM OAuthUser as u JOIN u.roles as r WHERE r.id = ?1 AND u.isEnabled = true")`, – KellyM May 22 '18 at 16:11

score 1 · Answer 2 · answered May 22 '18 at 15:57

1

You are using the table name (auth_user) inside of you jpql @Query. You should use the class name OAuthUser instead:

@Query("SELECT u FROM OAuthUser u ...")
public List<OAuthUser> findByRole(int roleID);

If you want to use SQL instead of jpql, you need to use like this:

@Query(value = "SELECT * FROM auth_user ..." , nativeQuery=true)
public List<OAuthUser> findByRole(int roleID);

That way you could mention table name and columns.

answered May 22 '18 at 15:57

Carlos Nantes

1,197
1
12
23

1

While I already resolved it a different way, thanks for the answer. The native query option looks useful. I realize one needs to use the class name in `@Query` but with a join table there is no class name. Thanks again. – KellyM May 22 '18 at 16:13

Spring Data JPA - Using @Query with Many to Many Relationships / Join Table

2 Answers2