Nested query with Hibernate HQL

Question

Please I want to include a nested select from in a hql query like this:

Query query1 = session.createQuery("from Component where ( AC_ID IS NULL) AND ( WIP_ID IS NOT NULL ) AND ( SELECT USER_ID FROM WorkInProcess WHERE WIP_ID  = 'WIP_ID' IS NULL)  ");

How could I achieve this with hibernate hql query.

Thank you

Please care to provide more information about your entities and the hibernate version you are using. — Ubercool
– Ubercool, Commented Nov 8, 2017 at 12:53

Monkey D. Luffy · Accepted Answer · 2017-11-08 14:09:44Z

With minimal information given i would say there are two classes Component.class and WorkInProcess.class. I guess WIP_ID is foreign key for WorkInProcess.class. You can use the following hql query or detached criteria based query to achieve it:

session.createQuery("from Component m where m.AC_ID is NULL and WIP_ID IS NOT NULL and m.USER_ID IN (" +
                "select e.USER_ID from WorkInProcess e where e.WIP_ID:=wip_id) is null").setParameter("wip_id", wip_id).uniqueResult();

or

DetachedCriteria Query1 = DetachedCriteria.forClass(WorkInProcess.class);
                           Query1.add(Restrictions.eq("WIP_ID", WIP_ID));

Criteria Query2 = session.createCriteria(Component.class);
                  Query2.add(Restrictions.isNull("AC_ID"));
                  Query2.add(Restrictions.isNotNull("WIP_ID"));
                  Query2.add(Property.forName("USER_ID").eq(Query1));

Please refer : hql-and-nested-queries

Collectives™ on Stack Overflow

Nested query with Hibernate HQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related