I have a query method that I am attempting to define, however I am currently facing a problem. The query I am attempting to make is one that gets all users in my DB but filters out users with an admin role. I have an entity class for User
and another for Role
. My User
class has fields for a users id, username, and email while the 'Role' entity contains a role id and role name as fields. Inside my user class entity, I define a many to many relationship defined as follows:
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable( name = "user_roles",
joinColumns = @JoinColumn(name = "user_id"),
inverseJoinColumns = @JoinColumn(name = "role_id"))
private Set<Role> roles = new HashSet<>();
The catch is that this is also where the user_roles table is generated, so I do not have an entity class related to user_roles.
So basically I am wondering if it is possible to create a query that uses values with and without entitles in the same method and if I need to use prepared statements in it?
@Query("SELECT u.id,u.username,u.email FROM User u,Role r, user_role ur WHERE u.id=ur.id AND r.id=ur.id AND r.name<>\'ADMIN\'")
List<User> getUsers();
I am currently getting an error user_role is not mapped [SELECT u.id,u.username,u.email FROM com.Application.models.User u,com.Application.models.Role r, user_role ur WHERE u.id=ur.id AND r.id=ur.id AND r.name<>'ADMIN']
.
CodePudding user response:
You can do this with a JPA inner join. It will traverse the join table for you.
@Query("SELECT u.id, u.username, u.email FROM User u join u.roles r where r.name <> 'ADMIN'")