[FIXED] Is it possible to create queries with AND without entity classes in the same method? (JPA + Hibernate)

Issue

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'].

Solution

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'")

Answered By - Lee Greiner
Answer Checked By - Dawn Plyler (JavaFixing Volunteer)