Hibernate subquery detachedCriteria

detachedcriteria, hibernate, subquery

Solution

Here's an example:

DetachedCriteria exampleSubquery = DetachedCriteria.forClass(MyPersistedObject.class)
    .setProjection(Property.forName("id"))
    // plus any other criteria...
    ;

Criteria criteria = getSession().createCriteria(ARelatedPersistedObject.class)
    .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
    .add(Subqueries.propertyIn("myPersistedObjectId", exampleSubquery)));

For multiple subqueries, you can use a boolean operator like Restrictions.or():

DetachedCriteria anotherSubquery = DetachedCriteria.forClass(MyPersistedObject.class)
    .setProjection(Property.forName("id"))
    // plus any other criteria...
    ;

Criteria criteria = getSession().createCriteria(ARelatedPersistedObject.class)
    .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
    .add(Restrictions.or(
        Subqueries.propertyIn("myPersistedObjectId", exampleSubquery),
        Subqueries.propertyIn("myPersistedObjectId", anotherSubquery)));

Problem

How to write a subquery in hibernate which is having multiple subqueries. for example ``` select * from project_dtls where project_id in (select project_id from project_users where user_id = (select user_id from user_dtls where email='abc@email.com')) ``` I know that we can write through DetachedCriteria but couldnot find any example where I can use multiple subqueries.

Original source