How to use AND OR operator in hibernate Detached criteria query

Question

I have a requirement to use AND/OR operator to hibernate detached criteria queries. I want to emulate SQL eqivalent to:

Select * from myTable where city in ( X, Y ) OR city in (A,B);  

// Note i need to use multiple 'In' here

How to create citeria query to use 'OR' operator.

Something like

DetachedCriteria criteria = DetachedCriteria.forClass(
    myClass.class)
   .add(Property.forName("city").in(X,Y));
criteria.**Or**(add(Property.forName("city").in(X,Y));

Unfortunatly there is no OR method in criteria , only add there.

Thanks in advance

Answer 1

Use Restrictions.conjunction() and Restrictions.disjunction() to create the hierarchy of conditions.

Look here - section 15.2 and here

---

EDIT:

I suppose that your code Property.forName("city").in(X,Y)) is correct ( I don't remember this clausule )

DetachedCriteria criteria = DetachedCriteria.forClass(
    myClass.class)
   .add(Restrictions.disjunction()
       .add( Property.forName("city").in(X,Y) )
       .add(Property.forName("city").in(X,Y)  )
   );

Answer 2

Do the following steps:

Step-1.0: Create Query Criteria

Criteria myQueryCrit = session.createCriteria(XYZ.class, "xyz");

Step-2.0: For Handling OR conditions of Query:

2.1) First you need to create Disjunction object, say, myQueryDisjunc.

Disjunction myQueryDisjunc = Restrictions.disjunction();

2.2) Then create the All OR Criterion objects. Like:

Criterion xyzName = Restrictions.ilike("xyz.name", "%"+searchStr1+"%", MatchMode.ANYWHERE);

Criterion xyzSpeciality = Restrictions.ilike("xyz.specs", "%"+searchStr1+"%", MatchMode.ANYWHERE);

Criterion xyzServices = Restrictions.ilike("xyz.services", "%"+searchStr1+"%", MatchMode.ANYWHERE);

2.3) Adding all OR Criterions object into myQueryDisjunc

myQueryDisjunc.add(xyzName);

myQueryDisjunc.add(xyzSpeciality);

myQueryDisjunc.add(xyzServices);

Step-3.0: For Handling AND conditions of Query:

3.1) First you need to create Conjunction object, say, myQueryConjunc.

Conjunction myQueryConjunc = Restrictions.conjunction();

3.2) Then create the All AND Criterion objects. Like:

Criterion xyzLoc = Restrictions.ilike("xyz.locStr", "%"+searchStr2+"%", MatchMode.ANYWHERE);

Criterion xyzZip = Restrictions.ilike("xyz.zipStr", "%"+searchStr3+"%", MatchMode.ANYWHERE);

3.3) Adding all AND Criterions object into myQueryConjunc

myQueryConjunc.add(xyzLoc);

myQueryConjunc.add(xyzZip);

Step-4.0: Now add myQueryDisjunc, myQueryConjunc into myQueryCrit:

myQueryCrit.add(myQueryDisjunc);
myQueryCrit.add(myQueryConjunc);

Step-5.0: Now add any Result Trnasformer if needed [ optional ]:

myQueryCrit.setResultTransformer(
CriteriaSpecification.DISTINCT_ROOT_ENTITY);

Step-6.0: Execute the myQueryCrit by, invoking list() on it.

List <myObj> allResults = myQueryCrit.list();

Step-7.0: Thats All.