Hibernate Community • View topic - Multiple Table Criteria Join Help

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Multiple Table Criteria Join Help

Page 1 of 1

[ 5 posts ]

Previous topic | Next topic

Author

Message

Apoptosis66

Post subject: Multiple Table Criteria Join Help

Posted: Sun May 14, 2006 4:50 pm

Newbie

Joined: Wed May 05, 2004 12:22 am
Posts: 5

Hi guys. I am a bit stumped on how to do joins using Criteria queries. I am sure its pretty simple but the documentation doesn't seem very clear on this topic.

Say I have the following sql:

Code:

SELECT DISTINCT * FROM permission p
JOIN role_permission rp ON p.permission_id = rp.permission_id
JOIN user_role ur ON rp.role_id = ur.role_id
WHERE ur.user_id = ?;

I am trying to retrieve all the permissions for a given user. To do this I need to join with the role_permission table which is many to many
and join with the user_role table which is also many to many.

I have tried using createAlias and createCriteria but neither are doing what I expect.

If someone can turn this example into a Criteria query, I think I will be able to do anything I need to do.[/code]

Top

Apoptosis66

Post subject:

Posted: Mon May 15, 2006 7:56 pm

Newbie

Joined: Wed May 05, 2004 12:22 am
Posts: 5

Are people not answering because I asked wrong, or because it can't be done with a criteria query?

Top

Apoptosis66

Post subject:

Posted: Mon May 15, 2006 10:51 pm

Newbie

Joined: Wed May 05, 2004 12:22 am
Posts: 5

Since nobody wanted to help me I helped myself...

After lots of trial and error I came up with

Code:

public List<Permission> findByUser(User user) {
    Criteria crit = session.createCriteria(Permission.class);
    crit = crit.createCriteria("roles");
    crit = crit.createCriteria("users");
    crit = crit.add(Restrictions.eq("userId", user.getUserId()));
    return crit.list();
}

Where "roles" is a collection on the Permission Object, and users is the collection on the Role Object, and userId is a member of the User object.

I think this is a pretty good example.

Top

bkmr_77

Post subject:

Posted: Mon May 15, 2006 11:38 pm

Expert

Joined: Tue Apr 25, 2006 12:04 pm
Posts: 260

This was the reply another user has posted few days before. Check if this is the one you were looking for.

Code:

DetachedCriteria uCrit = DetachedCriteria.forClass( User.class, "user" ); 
uCrit.add( Restrictions.eq("user.userID", SOME_VALUE ) ); 
uCrit.setProjection( Projections.property("user.role_id") ); 

DetachedCriteria rCriteria = DetachedCriteria.forClass( Role.class, "role"); 
rCriteria.setProjection( Projections.property( "role.role_id" ) ); 
rCriteria.add(Property.forName("role.role_id").in( uCriteria ) ); 
rCriteria.getExecutableCriteria( getSession() ).list(); 

DetachedCriteria criteria = DetachedCriteria.forClass(Permission.class, "permission"); 
criteria.add( Property.forName("permission.role_id").in( rCriteria ) ); 
criteria.getExecutableCriteria( getSession() ).list(); 

This is the post link http://forum.hibernate.org/viewtopic.ph ... highlight=

Top

chuck77

Post subject:

Posted: Fri Sep 08, 2006 3:33 pm

Newbie

Joined: Fri Sep 08, 2006 3:27 pm
Posts: 1

Hi,
I was wondering if someone could post roughly what the .hbm.xml code would look like to execute a query like this (for "roles" and "users"). I am trying to do a join 3 tables deep with Criteria but dont know how to form the xml part.

>public List<Permission> findByUser(User user) {
> Criteria crit = session.createCriteria(Permission.class);
> crit = crit.createCriteria("roles");
> crit = crit.createCriteria("users");
> crit = crit.add(Restrictions.eq("userId", user.getUserId()));
> return crit.list();
>}

Top

Page 1 of 1

[ 5 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum