Hibernate Community • View topic - Many-to-Many criteria problem

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Many-to-Many criteria problem

Page 1 of 1

[ 7 posts ]

Previous topic | Next topic

Author

Message

rapthor

Post subject: Many-to-Many criteria problem

Posted: Sun Oct 07, 2007 6:27 am

Regular

Joined: Wed Nov 01, 2006 2:17 pm
Posts: 78

Hello,

the last weeks I tried to build a Criteria that queries many-to-many associations, without success.

I have USERs, ROLEs and USER_ROLEs where USER_ROLE is the jointable. I want to build a Criteria that gets me all USERs containing a number of ROLEs at the same time (for example 2 or more).

The following attempt does not work properly, because it gets me all USERs that have EITHER Role1 OR Role2. I want them to have BOTH. A Conjunction in place of a Disjunction does not work due to Hibernate creates an SQL like "... where (role_id=1 AND role_id=2)".

Code:

Criteria crit = session.createCriteria(User.class);
      Criteria subcrit = crit.createCriteria("roles", CriteriaImpl.INNER_JOIN);

      Disjunction any = Expression.disjunction();
      any.add( Restrictions.ideq(roleone.getId));
      any.add( Restrictions.ideq(roletwo.getId));

      subcrit.add(any);
      crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)     return crit.list(); 

Can anybody help me with this please? I am frustrated because I struggled with that some days already.

Top

ganga_tanwar

Post subject:

Posted: Mon Oct 08, 2007 5:01 am

Newbie

Joined: Mon Oct 01, 2007 8:59 am
Posts: 18

I don't know how to create criteria for this. But You can solve this problem by using createQuery();

Query query = session.createQuery("select urole.user from USER_ROLEs urole group by urole.user having count(urole.user)>=2");
List role_list=query.list();

Top

rapthor

Post subject:

Posted: Mon Oct 08, 2007 6:20 am

Regular

Joined: Wed Nov 01, 2006 2:17 pm
Posts: 78

Thanks, but I want to keep to Criteria in order to simply connect several Criteria objects to build one that covers everything.

Any ideas on Criteria?

Top

rapthor

Post subject:

Posted: Mon Oct 08, 2007 6:27 am

Regular

Joined: Wed Nov 01, 2006 2:17 pm
Posts: 78

Thanks, but I want to keep to Criteria in order to simply connect several Criteria objects to build one that covers everything.

Any ideas on Criteria?

Top

rapthor

Post subject:

Posted: Mon Oct 08, 2007 6:29 am

Regular

Joined: Wed Nov 01, 2006 2:17 pm
Posts: 78

Thanks, but I want to keep to Criteria in order to simply connect several Criteria objects to build one that covers everything.

Any ideas on Criteria?

Top

rapthor

Post subject:

Posted: Mon Oct 08, 2007 6:30 am

Regular

Joined: Wed Nov 01, 2006 2:17 pm
Posts: 78

Sorry for posting multiple times. Have had bad connection problems!

Top

rapthor

Post subject:

Posted: Tue Oct 09, 2007 1:43 pm

Regular

Joined: Wed Nov 01, 2006 2:17 pm
Posts: 78

I tried Restrictions.sizeGe() due to I want to have all users that have at least the specified roles. That works almost perfectly except for one thing: It gets me also users that have at least one of "roleone" or "roletwo" AND any other role. "greater or equal" is set to "2" in my example, so every user that has one of the specified roles and ANY other is processed. This is not what I want.

Code:

Criteria crit = session.createCriteria(User.class);
      Criteria subcrit = crit.createCriteria("roles", CriteriaImpl.INNER_JOIN);
       crit.add(Restrictions.sizeGe("roles", 2));

       Disjunction any= Restrictions.disjunction();
       any.add( Restrictions.ideq(roleone.getId));
       any.add( Restrictions.ideq(roletwo.getId));
       subcrit.add(any);

       crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);      return crit.list(); 

Any ideas how to exclude users that have only one of the roles but unfortunately 2 (or more) other roles?

Top

Page 1 of 1

[ 7 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