-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 11 posts ] 
  Previous topic | Next topic 
Author Message
bremmington
 Post subject: Criteria API: OR across criteria
PostPosted: Tue Mar 07, 2006 3:46 pm 
Newbie

Joined: Fri Oct 14, 2005 11:43 am
Posts: 18
Hi all

I'm trying to use the Criteria API for the first time, and have stumbled across a problem that I could use some advice on.

For ease of example, assume that I have a model in which entities of type "Adult" contain a collection of entities of type "Child". Each Child has a field named "sex" which is either set to "M" or "F".

I want to build a Criteria that will return all adults that have either no children at all or at least one boy.

What I'm thinking I need is something like:
Code:
Criteria rootCriteria = session.createCriteria(Adult.class);
rootCriteria.add(Restrictions.isEmpty("children"));
Criteria childrenCriteria = rootCriteria.createCriteria("children");
childrenCriteria.add(Restrictions.eq("sex", "M"));
rootCriteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);


The problem is that this doesn't give me the "or" behaviour that I'm looking for, and because the "isEmpty" restriction is on the rootCriteria and the "sex==M" restriction is on the children criteria I don't see how to solve this.

All ideas very welcome.


Regards
Brian
Top
 Profile  
 
simonwillnauer
 Post subject: Re: Criteria API: OR across criteria
PostPosted: Tue Mar 07, 2006 5:23 pm 
Regular
Regular

Joined: Tue Mar 07, 2006 11:18 am
Posts: 54
Location: Berlin
Hi,
to put a disjunction on your criteria use the
Code:
Restrictions.or(Criterion c,Criterion c1)
Method
Code:
Criteria rootCriteria = session.createCriteria(Adult.class);

rootCriteria.add(Restrictions.or(Restrictions.isEmpty("children"),Restrictions.eq("children.sex", "M")));
rootCriteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);



I'm not 100% sure but I can remember doing things like that.

give it a go!!

simon


Last edited by simonwillnauer on Wed Mar 08, 2006 7:53 am, edited 1 time in total.

Top
 Profile  
 
bremmington
 Post subject:
PostPosted: Wed Mar 08, 2006 4:26 am 
Newbie

Joined: Fri Oct 14, 2005 11:43 am
Posts: 18
Thanks for your answer, Simon.

Unfortunately it isn't possible to add "Restrictions.isEmpty("children")" to the childrenCriteria, since "children" is out of scope on this criteria (it is selecting Child entities, not Adult entities).

Perhaps this isn't possible with the Criteria API. I hope it is though. Any other ideas?


Regards
Brian
Top
 Profile  
 
simonwillnauer
 Post subject:
PostPosted: Wed Mar 08, 2006 7:55 am 
Regular
Regular

Joined: Tue Mar 07, 2006 11:18 am
Posts: 54
Location: Berlin
Well you are right :) i missed that....

i did alter the statement above thats the way it should work....
You can access the "sex" attribute of the children entity by "children.sex" but it has to be in the rootCriteria!!

check it out..
simon

bremmington wrote:
Thanks for your answer, Simon.

Unfortunately it isn't possible to add "Restrictions.isEmpty("children")" to the childrenCriteria, since "children" is out of scope on this criteria (it is selecting Child entities, not Adult entities).

Perhaps this isn't possible with the Criteria API. I hope it is though. Any other ideas?


Regards
Brian
Top
 Profile  
 
dennisb
 Post subject:
PostPosted: Wed Mar 08, 2006 8:24 am 
Beginner
Beginner

Joined: Thu Oct 27, 2005 11:53 am
Posts: 42
You cannot OR criteria's. Every createCriteria is AND together. Strange enough with HQL this can be achieved easy. I also asked how to do this but i got no reply whatsoever. You can use disjunction to OR criterions but not criteria. I am building my own criteria AIP that generates HQL so that i can query every way i like.
Top
 Profile  
 
simonwillnauer
 Post subject:
PostPosted: Wed Mar 08, 2006 11:12 am 
Regular
Regular

Joined: Tue Mar 07, 2006 11:18 am
Posts: 54
Location: Berlin
Ok guys, set up your mapping and worked it out. It is definitely possible to create this query with criteria. First I thought I'm on the right way but wasn't sure. So I tried it....

Code:
Criteria criteria = session.createCriteria(Adult.class).createAlias("children", "childAlias");
criteria(Restrictions.or(Restrictions.isEmpyt("children"),Restrictions.eq("childAlias.sex","M");
criteria.list();


This does the job.


yours simon


dennisb wrote:
You cannot OR criteria's. Every createCriteria is AND together. Strange enough with HQL this can be achieved easy. I also asked how to do this but i got no reply whatsoever. You can use disjunction to OR criterions but not criteria. I am building my own criteria AIP that generates HQL so that i can query every way i like.
[/code]
Top
 Profile  
 
slepit
 Post subject:
PostPosted: Wed Mar 08, 2006 3:44 pm 
Newbie

Joined: Fri Aug 06, 2004 1:01 pm
Posts: 13
Hi,

I have got the same problem but I am using the library net.sf.hibernate... and not org.hibernate...

So I don't have the Restrictions class.

I tried to di the same thing with that

Code:
criteria.createAlias("children", "childAlias");
Disjunction disjunction = Expression.disjunction();
disjunction.add(Expression.isNull("children"));
disjunction.add(Expression.eq("childAlias.sex","M"));



But I guess Expression.isNull("children")) does not do what I expect.

Do you know the solution with that library ?

Actually what is the exact difference between net.sf.hibernate and org.hibernate ? I did not realize I downloaded a specific library when I got the library ...

thanks
Top
 Profile  
 
bremmington
 Post subject:
PostPosted: Wed Mar 08, 2006 3:46 pm 
Newbie

Joined: Fri Oct 14, 2005 11:43 am
Posts: 18
Thanks Simon. This does get very close to the right result. However I'm either still doing it wrong, or this is failing to retrieve adults who have no children.

The SQL that this is generating is something like:

Code:
select
   this_.ID,
   this_.NAME,
   child1_.ID,
   child1_.NAME,
   child1_.SEX,
   child1_.PARENTID
from
   ADULTS this_
inner join
   CHILDREN child1_ on this_.ID=child1_.PARENTID
where
   not exists (select 1 from CHILDREN where this_.ID=PARENTID)
   or child1_.SEX='M'


The problem is that the inner join on CHILDREN is causing any adults with no children to be missed. This is a pity, because the where clause looks perfect.

If you (or anyone else) can suggest a solution to this, I'd be very grateful. It seems so close :)



Regards
Brian
Top
 Profile  
 
bremmington
 Post subject:
PostPosted: Wed Mar 08, 2006 3:52 pm 
Newbie

Joined: Fri Oct 14, 2005 11:43 am
Posts: 18
Hi slepit

The net.sf.hibernate package name is from an older version (2.x and earlier, I think).

You're right that Expression.isNull will not be doing the correct job. Is there an Expression.isEmpty available to you?


Regards
Brian
Top
 Profile  
 
simonwillnauer
 Post subject:
PostPosted: Wed Mar 08, 2006 5:28 pm 
Regular
Regular

Joined: Tue Mar 07, 2006 11:18 am
Posts: 54
Location: Berlin
Hey man, which Version of hibernate are you using? I used 3.1.2 which has an Criteria.createAlias(String,String,int) Method. The int value sets the join mode.
I'm not sure whether this method is included in your version of hibernate but the following code does the job... I actually don't really know why the query above doesn't work... the sql looks quite alright.
Code:
Criteria crit = session.createCriteria(Adult.class)
.createAlias("children","child",CriteriaSpecification.LEFT_JOIN);
crit.add(Restrictions.or(Restrictions.eq("child.sex","m")
.ignoreCase(),Property.forName("children").isEmpty()));


yours simon





bremmington wrote:
Thanks Simon. This does get very close to the right result. However I'm either still doing it wrong, or this is failing to retrieve adults who have no children.

The SQL that this is generating is something like:

Code:
select
   this_.ID,
   this_.NAME,
   child1_.ID,
   child1_.NAME,
   child1_.SEX,
   child1_.PARENTID
from
   ADULTS this_
inner join
   CHILDREN child1_ on this_.ID=child1_.PARENTID
where
   not exists (select 1 from CHILDREN where this_.ID=PARENTID)
   or child1_.SEX='M'


The problem is that the inner join on CHILDREN is causing any adults with no children to be missed. This is a pity, because the where clause looks perfect.

If you (or anyone else) can suggest a solution to this, I'd be very grateful. It seems so close :)



Regards
Brian
Top
 Profile  
 
bremmington
 Post subject:
PostPosted: Thu Mar 09, 2006 4:32 am 
Newbie

Joined: Fri Oct 14, 2005 11:43 am
Posts: 18
Fantastic! That works a treat.

Thanks very much for your help, Simon.


Regards
Brian
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 11 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

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.