Hibernate subquery detachedCriteria

Question

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.

Peter Bratton · Answer 1 · 2012-10-26T14:27:23.310

7

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)));

edited Oct 26 '12 at 14:27

answered Oct 25 '12 at 19:08

Peter Bratton

6,302
6
39
61

Sounds like it didn't work. If you can provide some more details, perhaps the code you tried, maybe we can help more. – Peter Bratton Oct 28 '12 at 15:35
Well, your example doesn't actually replicate his code. He has a subquery inside a subquery but yours is just an OR. – AHungerArtist Jul 11 '17 at 16:14
This question asks how to do it with DetachedCriteria. Your answer shows how to do it with Criteria. This is different and doesn't exactly answer the question. – Daniel Patrick Apr 23 '18 at 15:21

score 0 · Answer 2 · answered Apr 30 '18 at 19:22

To do it entirely with Detached Criteria (because I like to construct the detached criteria without a session)

DetachedCriteria idQuery = DetachedCriteria.forClass(MyPersistedObject.class)
    .setProjection(Property.forName("id"))
DetachedCriteria recordQuery = DetachedCriteria.forClass(MyPersistedObject.class)
    .add(Property.forName("id").eq(idQuery) );

Hibernate subquery detachedCriteria

2 Answers2

Linked