Hibernate subquery

Question

I have a problem in creating subqueries with Hibernate. Unfortunately the Subqueries class is almost entirely undocumented, so I have absolutely no clue how to convert the following SQL into a Hibernate Criteria:

SELECT id
FROM car_parts
WHERE car_id IN ( SELECT id FROM cars WHERE owner_id = 123 )

I was hoping the following would 'just work':

session.createCriteria(CarParts.class).add(eq("car.owner", myCarOwner));

but unfortunately it does not. So it seems I actually have to use the Subqueries class to create the Criteria. But I was unable to find a reasonable example though Google, so that leads me to asking it here.

Answer 1

Try Like this:

Table details): Category (id, name, desc, parentId, active)

    DetachedCriteria subCriteria = DetachedCriteria
            .forClass(Category.class);
    subCriteria.add(Restrictions.isNull("parent"));
    subCriteria.add(Restrictions.eq("active", Boolean.TRUE));
    subCriteria.add(Restrictions.eq("name", categoryName));
    subCriteria.setProjection(Projections.property("id"));

    Criteria criteria = getSession().createCriteria(Category.class);
    criteria.add(Restrictions.eq("active", Boolean.TRUE));
    criteria.add(Subqueries.propertyEq("parent", subCriteria));

It will generate the query like:

select
    *
from
    Categories this_ 
where
    this_.active=1
    and this_.parentId = (
        select
            this0__.id as y0_ 
        from
            Categories this0__ 
        where
            this0__.parentId is null 
            and this0__.active=1
            and this0__.name='Health Plan'
    )

Good Luck!

-Rohtash Singh

Answer 2

Try to create an alias for the "car" property before adding the eq expression like this:

session.createCriteria(CarParts.class)  
        .createAlias("car", "c")  
        .add(eq("c.owner", myCarOwner));