AND/OR in Hibernate - Criteria object

[Somak Dalui]

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 and not an AND. These are generated by sub-Criteria objects are here's the 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 ?? Thanks in advance.

[Halcon Guatemala]

Hi Somak, I suggest You use Restrictions instead Expression. You must remember Expression supports nested Expression(s):

Expression.or(Expression1, Expression2);


I hope it helps

[Somak Dalui]

Hi Halcon, thanks for the reply - I want to create an OR between

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

and

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

Can you tell me how can I acheive that ? I want an OR between these two sub-Criteria-s.

[Halcon Guatemala]

Hi again Somak, sorry but I didn't know about your 2 criterias You want to do "or" between. It seems any case of sql "union" clause, You know it isn't supported by Criteria API, You might considerate to use SQLQuery for it...

I hope this helps

[Somak Dalui]

Thanks a ton Halcon, been searching for the solution for a long time.

[Somak Dalui]

Just find out that this is possible by creating alias.

criteria.createAlias(...);    

[John Bartlett]

Hi,

Just curious whether Restrictions.disjunction(); would help you in any way?

e.g.

Disjunction disc = Restrictions.disjunction();
 
disc.add( Restrictions.eq("add restriction here...", ""));
disc.add( Restrictions.eq("add restriction here...", ""));
 
crit.add(disc);

This is the Restriction version of OR (conjunction is obviously the AND equivelent)

Would this work for you?

John

[Somak Dalui]

Yes John this will work, but I have to create an alias before I can refer the properties.Because the properties that I am referring to belongs to the mapped class and not to the parent class.So I am doing

criteria.createAlias("optionTradeModels", "optionTrade");
criteria.createAlias("loanAndDepositModels", "loanAndDeposit");
criteria.add(Expression.or(Expression.eq("optionTrade.LastModifiedUser", "XXX"), Expression.eq("loanAndDeposit.Status", "YYY")));    

This gives me a fine SQL.