Building Query Using Criteria

amemon · **Joined:** Thu Dec 01, 2005 12:53 am **Posts:** 21

Hibernate version: 3.0

I am building Hibernate query using Criteria ... I need to add restrictions on association and I found examples of it in hibernate docs like

Code:

List cats = sess.createCriteria(Cat.class)
    .add( Restrictions.like("name", "F%") )
    .createCriteria("kittens")
        .add( Restrictions.like("name", "F%") )
    .list();

But how is it possible to OR this kind of condition with other ... like I want "kittens names starting with F OR Cat names starting with F"

I am not able to find any way in Hibernate API to achieve this.

Waiting for your kind response.

-
Adnan

kochcp · **Posted:** Tue Feb 28, 2006 11:31 am

Restrictions.OR(criterion, criterion) or Restrictions.disjunction()

you can chain the hell out of all these, so feel free to stack them any way you like :)

paul.barry · **Posted:** Tue Feb 28, 2006 1:00 pm

Since kittens are just cats, this will get you all cats that start with F:

Code:

List cats = sess.createCriteria(Cat.class)
    .add(Restrictions.like("name", "F%"))
    .list()

If you want all cats that start with F and have a mother that starts with F, then you need:

Code:

List cats = sess.createCriteria(Cat.class)
    .add( Restrictions.like("name", "F%") )
    .createCriteria("kittens")
        .add( Restrictions.like("name", "F%") )
    .list();

If you want cats that start with F OR have a mother that starts with F, that's tricky, I'm still trying to figure out how you could do that. This makes sense to me:

Code:

List cats = sess.createCriteria(DomesticCat.class)
    .createCriteria("kittens")
    .add( Restrictions.or(
        Restrictions.like("mother.name", "F%"),
        Restrictions.like("name", "F%")
        )
    )
    .addOrder(Order.asc("birthdate"))
    .list();

but doesn't work, I get:

Code:

org.springframework.orm.hibernate3.HibernateQueryException: could not resolve property: mother.name of: eg.Cat; nested exception is org.hibernate.QueryException: could not resolve property: mother.name of: eg.Cat

And I changed the mother propertry of Cat to be a DomesticCat, so mother.name should be visable, so I don't know why it doesn't work.

kochcp · **Posted:** Tue Feb 28, 2006 1:14 pm

using the method below, anything after that 2nd createCriteria statement looks in the "mother" class for attributes. If you create an alias for the kittens and one for the mother you can then use them in the OR statement

Code:

Criteria criteria = session.createCriteria(
DomesticCat.class()).createCriteria("mother")...

kochcp · **Posted:** Tue Feb 28, 2006 1:15 pm

oh, you're already doing that with kittens. try doing it again with mothers after using the kittens

like

Code:

Criteria criteria = session.createCriteria(
DBSoftwareVersion.class()).createCriteria("kittens").createCriteria("mother")...

paul.barry · **Posted:** Tue Feb 28, 2006 1:58 pm

kochcp wrote:

oh, you're already doing that with kittens. try doing it again with mothers after using the kittens

like

Code:

Criteria criteria = session.createCriteria(
DBSoftwareVersion.class()).createCriteria("kittens").createCriteria("mother")...

I don't see how that's going to work, because you need to OR the two. Can you provide the full code for what you are talking about?

kochcp · **Posted:** Tue Feb 28, 2006 2:21 pm

oh gosh it was so long ago when i worked with criteria. Can you create aliases for the various collections as you move along?

createCriteria(DomesticCat.class).createCriteria("kittens").createAlias("kittens.mother", "mother").restrictions.Or(...

paul.barry · **Posted:** Tue Feb 28, 2006 2:23 pm

Maybe you can, but I don't see how that helps. What are you going to put inside of your Restrictions.or?

kochcp · **Posted:** Tue Feb 28, 2006 2:27 pm

before the OR add the attribute which gets kittens only

then make the aliases then restrictions.OR(estrictions.like("name", "f"), restrictions.like("mother.name","f"))

paul.barry · **Posted:** Tue Feb 28, 2006 4:19 pm

If you do this:

Code:

List cats = session.createCriteria(Cat.class)
    .createCriteria("kittens").createAlias("kittens.mother", "mother")
        .add(Restrictions.or(
            Restrictions.like("name","F%"),
            Restrictions.like("mother.name","F%")
            )
        ).list();

Hibernate seems to get totally confused. The query it tries to execute is:

Code:

    select
        this_.id as id0_2_,
        this_.birthdate as birthdate0_2_,
        this_.mother_id as mother7_0_2_,
        this_.sex as sex0_2_,
        this_.weight as weight0_2_,
        this_.name as name0_2_,
        this_.subclass as subclass0_2_,
        domesticca1_.id as id0_0_,
        domesticca1_.birthdate as birthdate0_0_,
        domesticca1_.mother_id as mother7_0_0_,
        domesticca1_.sex as sex0_0_,
        domesticca1_.weight as weight0_0_,
        domesticca1_.name as name0_0_,
        domesticca5_.id as id0_1_,
        domesticca5_.birthdate as birthdate0_1_,
        domesticca5_.mother_id as mother7_0_1_,
        domesticca5_.sex as sex0_1_,
        domesticca5_.weight as weight0_1_,
        domesticca5_.name as name0_1_ 
    from
        cats this_ 
    inner join
        cats domesticca1_ 
            on this_.id=domesticca1_.mother_id 
    left outer join
        cats domesticca5_ 
            on domesticca1_.mother_id=domesticca5_.id 
    where
        (
            domesticca1_.name like ? 
            or mother2_.name like ?
        )

Which results in this error:

Code:

ORA-00904: "MOTHER2_"."NAME": invalid identifier

Because there is no mother2_ table in the from clause. I think that even if this worked, it would return cats whose mother start with f and whose mother's mother starts with f.

paul.barry · **Posted:** Tue Feb 28, 2006 4:32 pm

The only way I can figure to do this is:

Code:

                List cats = session.createCriteria(Cat.class)
                    .createCriteria("kittens")
                        .add(Restrictions.like("name","F%")
                    ).list();
                cats.addAll(session.createCriteria(Cat.class)
                .add(Restrictions.like("name","F%"))
                .list());

Which will give you a list of all cats whose name starts with F and cats who have kittens whose name starts with F in one list. It will have duplicates and will not be sorted, but you could use a TreeSet to sort and eliminate the duplicates.