Home > Software engineering >  Is it possible to create queries with AND without entity classes in the same method? (JPA Hibernat
Is it possible to create queries with AND without entity classes in the same method? (JPA Hibernat

Time:04-26

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