Hibernate multiple table join

Question

I have following tables

User (has FK_RoleKey) -> UserRole (Role Key)
UserRoleResourceAccess(FK_RoleKey) -> UserRole(RoleKey)
UserRoleResourceAccess (has FK_ResourceKey) -> Resource (ResourceKey Key)

Eg Row:

User-> EmailId = user@abc.com  | FK_RoleKey = 1
UserRole -> Role Key=1| RoleName= Admin
Resource -> Resource Key=1 | Resource name= "page1"
UserRoleResourceAccess -> FK_RoleKey =1 |FK_ResourceKey = 1

How can i get all the user privileges(role and resources) of a given user using hibernate criteria query.

Briefly : this is what i am trying to achieve in a single hibernate query

select * from User user 
LEFT JOIN UserRole userRole 
ON user.`FK_RoleKey` = userRole.`RoleKey`
LEFT JOIN UserRoleResourceAccess userRoleResourceAccess 
ON userRoleResourceAccess.`FK_RoleKey` = userRole.`RoleKey`
LEFT JOIN `Resource` resource 
ON resource.`ResourceKey` = userRoleResourceAccess.`FK_ResourceKey`
 where user.`EmailID` = 'abc';

score 0 · Answer 1 · answered Sep 12 '17 at 11:35

0

As per your example, suppose email is the PK on user table, then you have to just fetch a user from the table using HQL.

 String hql = "FROM User WHERE email = '"+email+"'";<br>
 TypedQuery<User> query = sessionFactory.getCurrentSession().createQuery(hql);<br>
 User user = query.getSingleResult();

Hibernate automatically retrieves the other foreign key data and stores it to User if your mappings are correct. To retrieve them back, you can use...

String role = user.getUserRole().getRoleName();

Similarly for resource. Hope this helps.

answered Sep 12 '17 at 11:35

Kaustubh Kallianpur

349
2
16

but then i have to write another query to get all Resource related to Roles which is present in UserRoleResourceAccess. – saurabh jain Sep 12 '17 at 11:42
could you add the code of Entity of User Table... have you mapped the UserRoleResourceAccess to User table properly with @OnetoOne and @JoinColumn? If yes, the above stated SQL should work and you can retrieve the data like the way I have stated... – Kaustubh Kallianpur Sep 12 '17 at 11:47
No i don't have ..check the listing in the original question. User table has FK for UserRole User Role table and Resource table only have PK and no FK. UserRoleResourceAccess has FK for UserRole and FK for Resource table – saurabh jain Sep 12 '17 at 12:01
https://stackoverflow.com/questions/21762328/java-hibernate-onetoone-mapping Check this question, this would help you. – Kaustubh Kallianpur Sep 12 '17 at 12:11

Hibernate multiple table join

1 Answers1