Hibernate Criteria for "in subselect"

Question

I'm trying to do something like this, but using Criteria instead of HQL:

select user from User where user in (
    select user from UserDomain where domain.id = "XXX"
)

User being an entity having a one-to-many relationship to the join table UserDomain. The point here is simply to find Users that are linked to a Domain having id = "XXX".

This seems like it should be very simple... but I'm having no luck so far turning up any useful docs.

Radim Köhler · Answer 1 · 2014-08-21T05:00:22.473

28

The subquery is very useful in cases, that you need to search the User, having one-to-many UserDomains. In that case, the WHERE UserId IN (subquery) brings big advanatage: we are still working with a flat User table/entity... so we can do the proper paging.

Here is the documentation 15.8. Detached queries and subqueries

The draft could be: subquery:

DetachedCriteria userSubquery = DetachedCriteria.forClass(UserDomain.class, "ud")
    // Filter the Subquery
    .add(Restrictions.eq(UserDomain.DOMAIN, domain))
    // SELECT The User Id  
    .setProjection( Projections.property("ud.userId") );

And the main query:

Criteria query = session.createCriteria(User.class, "u")
    .add( Subqueries.propertyIn("u.id", userSubquery) );

Now we have a query, which could be used for paging

edited Aug 21 '14 at 05:00

answered Dec 06 '13 at 15:34

Radim Köhler

122,561
47
239
335

On small correction, I think you meant ".add(Subqueries.propertyIn("id", userSubquery))". Your example causes some cryptic class cast exceptions. – Steve Skrla Jul 17 '14 at 12:20
As a side note: you may encounter 'Class Cast Exception' as I did if you try to change `Subqueries.propertyIn("u.id", userSubquery)` to `Subqueries.eq("u.id", userSubquery)` - don't do that even if there is a single match for a subquery. – dominik Feb 23 '17 at 16:20

score 3 · Answer 2 · answered Dec 06 '13 at 15:02

3

In clauses usualy can be transformed into join. Tyr this:

Criteria c = session.createCriteria(User.class, "u");
c.createAlias("u.userDomain", "ud"); // inner join by default
c.add(Restrictions.le("ud.id", 1));

answered Dec 06 '13 at 15:02

Evgeni Dimitrov

21,976
33
120
145

1

No, there is no "u.userDomain" relationship. User.userDomains is a to-many relationship to the join table UserDomain. – Marc Dec 06 '13 at 15:23

score 3 · Accepted Answer · answered Dec 06 '13 at 15:22

I finally found it. Turns out it wasn't so hard after all... once you know!

criteria = criteria.createCriteria(User.USER_DOMAINS).add(Restrictions.eq(UserDomain.DOMAIN, domain));

Yep, there is was, staring me right in the face in the Javadoc: http://www.dil.univ-mrs.fr/~massat/docs/hibernate-3.1/api/org/hibernate/Criteria.html

Hibernate Criteria for "in subselect"

3 Answers3

Linked