CODEWITHSUNDEEP
javahibernate
Mr.Eddart
Mr.Eddart

Reputation: 10273

Translate HQL subqueries to Criteria

I would like to translate this structure of HQL:

FROM Entity_1 obj
WHERE obj IN (FROM Entity2) OR 
      obj IN (FROM Entity3)

How can it be done?

Upvotes: 2

Views: 1054

Answers (2)

M F
M F

Reputation: 323

I am pretty new to HQL but I would suggest something like the following:

 DetachedCriteria sub1 = DetachedCriteria.forClass(Entitiy2);
 DetachedCriteria sub2 = DetachedCriteria.forClass(Entity3);

 Criteria criteria = getYourSession().createCriteria(Entity_1.class, "obj");
 criteria.add(Restrictions.or(Subqueries.propertyIn("obj", sub1), Subqueries.propertyIn("obj", sub2));

Please, correct me if I am wrong.

Upvotes: 1

Guillaume
Guillaume

Reputation: 5555

It is explained in Hibernate criteria documentation: http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html#querycriteria-detachedqueries

A DetachedCriteria can also be used to express a subquery. Criterion instances involving subqueries can be obtained via Subqueries or Property.

DetachedCriteria avgWeight = DetachedCriteria.forClass(Cat.class)
    .setProjection( Property.forName("weight").avg() );
session.createCriteria(Cat.class)
    .add( Property.forName("weight").gt(avgWeight) )
    .list();

DetachedCriteria weights = DetachedCriteria.forClass(Cat.class)
    .setProjection( Property.forName("weight") );
session.createCriteria(Cat.class)
    .add( Subqueries.geAll("weight", weights) )
    .list();

Correlated subqueries are also possible:

DetachedCriteria avgWeightForSex = DetachedCriteria.forClass(Cat.class, "cat2")
    .setProjection( Property.forName("weight").avg() )
    .add( Property.forName("cat2.sex").eqProperty("cat.sex") );
session.createCriteria(Cat.class, "cat")
    .add( Property.forName("weight").gt(avgWeightForSex) )
    .list();

Upvotes: 2

Related Questions