Hey there!
This is what my classes and associations look like
Code:
class User {
@Id
long id;
@ManyToMany
Set<Role> roles;
}
class Role {
@Id
Long id;
String name;
@ManyToMany(mappedBy="roles")
Set<User> users;
}
Now I need to query a User's Roles and do some sorting. My HQL query is
Code:
SELECT r FROM User u JOIN u.roles r WHERE u.id = :uid ORDER BY r.name
This returns what I want, however ..
Hibernate creates the following SQL query
Code:
select role2_.ROLE_ID as ROL1_4_, role2_.NAME as NAME4_ from USERS user0_ inner join USERS_ROLES roles1_ on user0_.USER_ID=roles1_.USER_ID inner join ROLES role2_ on roles1_.ROLE_ID=role2_.ROLE_ID where user0_.USER_ID=? order by role2_.NAME
Bear with me, I'm rather new to SQL itself as well. The following SQL query seems to be more efficient
Code:
SELECT role_id, name FROM roles WHERE role_id IN (SELECT role_id FROM users_roles WHERE user_id=?) ORDER BY name;
So my question is twofold; How would I achieve the same (i.e. selecting and sorting a User's Roles) with pure Criteria API? If there is no way, how would I create a more efficient HQL query?
Login
FAQ
Search