How to do Criteria with SubCriteria OR Clause?

hibforum · **Joined:** Thu Mar 29, 2007 12:07 pm **Posts:** 3

Hi.,

I have a Parent class (A) with onetoone association to a class (B).
HQL :
From A WHERE A.B.property1="1" OR A.B.property2="2"

Criteria API:

Criteria aCrit = session.createCriteria(A.Class);
Criteria bCriteria = aCrit.createCriteria("B"); // SubCriteria

bCriteria.add(Restrictions.eq("property1","1"));

Problem:
With above kind of Criteria query, i always get AND clause but I need to have OR clause.

Any help would be highly appreciated.

Thanks

batmat · **Posted:** Fri Mar 30, 2007 6:57 am

To define an Or, use Restrictions.or()

hibforum · **Joined:** Thu Mar 29, 2007 12:07 pm **Posts:** 3

Hi.,
Thank for ur reply.

Quote:

To define an Or, use Restrictions.or()

This didn't help becos Restiction.or can be used only for 2/more properties of same criteria/subcriteria but not as criteria and subcriteria.
For eg:

Code:

FROM A as a WHERE a.property1="1" OR a.B.property1="1"

Now for this how can i set restrictions.

Please help me.

Thanks

Ananasi · **Posted:** Fri Mar 30, 2007 10:14 am

Code:

Criteria crit = session.createCriteria(A.class)
                       .createAlias("B","b")
                       .add(
                            Restrictions.or(
                               Restrictions.eq("property1","1"),
                               Restrictions.eq("b.property1","1")
                            )
                        );

batmat · **Posted:** Fri Mar 30, 2007 10:47 am

Well, I don't think so. Did you check it? Restrictions.or() accepts a Criteria. So maybe you'll have to create a subCriteria or use a DetachedCriteria for this, but this should work.

hibforum · **Joined:** Thu Mar 29, 2007 12:07 pm **Posts:** 3

Hi.,
Ananasi's reply helped!!

Credits to Ananasi.

Thanks to
batmat

blima · **Joined:** Fri Apr 06, 2007 1:29 pm **Posts:** 4

I was wondering if there is any shortcut I missed for making multple or restrictions.

Let's say I have an object.

Code:

class TestObj {
 private String testStr=null;
 public getTestStr(){
  return testStr;
 }
 public setTestStr(String testStr){
  this.testStr=testStr;
 }
}

Let's assume I have all my mappings and configuration set up.

At some point I have a List<TestObj> and I want to get all of the TestObj that have any of the values of testStr in my list.
(This is a good test if testStr is unique in your db as you should get back the same List<TestObj> you had in the first place, if the or's are correct.)

I am looking to build a "querry" that looks like

Code:

SELECT * FROM test_objects WHERE (test_str='a' OR test_str='b' OR test_str='c' );

from the List<TestObj>.

Notice there are more than two possibilities and they are all on the same object and data member.

I am looking for an example of how best to do the above without using hard coded SQL.

I have been playing arround with Restrictions.or and I can't seem to wrap my head arround this one.

I tried using multiple Restrictions.or in .add()
like;

.add(
Restrictions.or(Restrictions.eq("testStr","a"),Restrictions.eq("testStr","b"))
)
.add(
Restrictions.or(Restrictions.eq("testStr","c"),Restrictions.eq("testStr","d"))
)

and the SQL output lookes like

Code:

   where
        (
            this_.testStr=? 
            or this_.testStr=?
        ) 
        and (
            this_.testStr=? 
            or this_.testStr=?
        ) 

The above makes sense to me but does not give me any clue as to how to restrict by 3 Criteria or more.

I assume I am missing something simple.

Any help will be greatly apreciated.

Ananasi · **Posted:** Fri Apr 06, 2007 4:53 pm

You did miss something simple, but don't worry about it. Sometimes the simple things are the easiest to overlook ;D.

Code:

criteria.add( Restrictions.disjunction()
                  .add(Restrictions.eq("testStr","a"))
                  .add(Restrictions.eq("testStr","b"))
                  .add(Restrictions.eq("testStr","c"))
                  .add(Restrictions.eq("testStr","d"))
            )

blima · **Joined:** Fri Apr 06, 2007 1:29 pm **Posts:** 4

Well isn't that nice, and simple!

Thank you Anasasi, that was exactly what I was looking for.