CODEWITHSUNDEEP
javasqlspringhibernate
Achaius
Achaius

Reputation: 6124

Hibernate like query on Many-to-Many collection

I am having Userentity which has mapped to user_roles. I want to filter these roles based on User.idand roles.name

Same like as this SQL query

SELECT ur 
FROM user 
     JOIN user_roles ur 
          ON ur.user_id = user.id 
WHERE user.id = 1 
  AND ur.name like '%admin%';

How to achieve this SQL query in hibernate? How to pass parameter to role name?

User.java

@Entity
class User {
    @ManyToMany
    @JoinTable(name="user_roles",
        joinColumns=@JoinColumn(
            name="user_id", referencedColumnName="id"),
        inverseJoinColumns=@JoinColumn(
            name="role_id", referencedColumnName="id")
        )
    public Set<Role> getRoles() {
        return roles;
    }
}

Upvotes: 0

Views: 172

Answers (2)

thanhnguyen
thanhnguyen

Reputation: 214

HQL supports exactly what you want, you can create an HQL query and then pass parameters to it. The following code block may be a reference:

Long userId = 1L;
String roleNamePattern = "%admin%";

Query query = session.createQuery("SELECT role FROM User user JOIN user.roles role WHERE user.id = :userid AND role.name LIKE :rolename");
query.setLong("userid", userId);
query.setString("rolename", roleNamePattern);
List<Role> roles = query.list();

Upvotes: 0

Suganthan Madhavan Pillai
Suganthan Madhavan Pillai

Reputation: 5655

Hope it will help

select "your_req" from User us join usr.role usr where usr.name like '%admin%' and us.id=1

Upvotes: 1

Related Questions