OR between two sub-Criteria objects

somakd · **Posted:** Thu Feb 15, 2007 5:06 am

I have a query like
"select
this_.appNum as y0_
from
CoreApplication this_
inner join
LoanAndDeposit loananddep2_
on this_.appNum=loananddep2_.CoreApplication_appNum
inner join
OptionTrade optiontrad1_
on this_.appNum=optiontrad1_.CoreApplication_appNum
where
this_.applicationType in (
?, ?, ?, ?
)
and this_.status in (
?, ?, ?
)
and optiontrad1_.lastModifiedUser=?
and loananddep2_.status=? ".

For the last part i.e. OptionTrade.lastModifiedUser and LoanAndDeposit.status I have to do an OR instead of AND. These are generated by sub-Criteria objects are here's the code :

Code:

Criteria criteria = session.createCriteria(CoreApplicationModel.class);
criteria.setProjection(Projections.property("appNum"));
criteria.add(Restrictions.in("applicationType", appTypeParams));
criteria.add(Restrictions.in("status",statusParams));
criteria.createCriteria("optionTradeModels").add(Expression.eq("LastModifiedUser", "Anonymous"));
criteria.createCriteria("loanAndDepositModels").add(Expression.eq("Status", "Done"));
List objects = criteria.list();

I have an idea that this is done by Conjunction object but can anyone please tell me how in this case ? To rephrase the question how can I do an OR operation between two sub-Criteria s ? Please help.

dharmendra.pandey · **Posted:** Thu Feb 15, 2007 5:10 am

Hi somakd

use Restrictions.or(Criterion, Criterion)

somakd · **Posted:** Thu Feb 15, 2007 5:34 am

Ok, how can I obtain the Criterion object ? I have to do an OR between

Code:

criteria.createCriteria("optionTradeModels").add(Expression.eq("LastModifiedUser", "Anonymous"));

and

Code:

criteria.createCriteria("loanAndDepositModels").add(Expression.eq("Status", "Done"));

robertmacgrogan · **Joined:** Tue Nov 09, 2004 3:09 pm **Posts:** 9

If there's a way to use Restrictions.or on properties in different entities, it's not obvious how this is done. I am no Hibernate Newbie--I've been using Hibernate vigorously for the past 3 years.

Only recently I realized I needed to use Criteria to perform an Or restriction on properties in different entities and I figured it would be a no brainer. Well, it's not--for me anyway.

Can anyone out there help with this?

If you have something like:

Code:

Criteria c = session.createCriteria(Person.class);
c.add(Restrictions.eq("name", "John");
Criteria cGroup = c.createCriteria("group");
cGroup.add(Restrictions.eq("groupType", "Coders");

You basically construct a query looking for Persons where name is John and group.groupType is Coders.

Now, suppose instead of an AND query, you want an OR query? How do you do this? Restrictions.or using only Criterion objects. How do we get a usable Criterion object out of our cGroup criteria? Constructing such an OR query is trivial in SQL. Is there some straightforward way to make this happen using the Criteria API as well? Please forgive me if I'm just missing something obvious.

Thanks.

--Rob

thatmikewilliams · **Posted:** Fri Jul 13, 2007 2:37 pm

What about this...

Code:

Criteria c = session.createCriteria(Person.class)
                      .createAlias("group", "grp")
                      .add(Restrictions.or(
                              Restrictions.eq("name", "John"),
                              Restrictions.eq("grp.groupType", "Coders")));

Mike

robertmacgrogan · **Joined:** Tue Nov 09, 2004 3:09 pm **Posts:** 9

Thanks a bunch, Mike! That worked perfectly!