Hibernate Subquery

Question

I have a problem creating subqueries with Hibernate. Unfortunately, the subquery class is almost completely undocumented, so I totally don’t understand how to convert the following SQL to Hibernate criteria:

SELECT id FROM car_parts WHERE car_id IN ( SELECT id FROM cars WHERE owner_id = 123 ) 

I was hoping the following “just work”:

 session.createCriteria(CarParts.class).add(eq("car.owner", myCarOwner)); 

but unfortunately this does not happen. It seems I really need to use a subquery class to create criteria. But I could not find a reasonable example, although Google, therefore, asks me here.

Answer 1

Try creating an alias for the "car" property before adding the eq expression as follows:

 session.createCriteria(CarParts.class) .createAlias("car", "c") .add(eq("c.owner", myCarOwner)); 

Answer 2

Try:

Information about the table): Category (id, name, desc, parentId, active)

  DetachedCriteria subCriteria = DetachedCriteria .forClass(Category.class); subCriteria.add(Restrictions.isNull("parent")); subCriteria.add(Restrictions.eq("active", Boolean.TRUE)); subCriteria.add(Restrictions.eq("name", categoryName)); subCriteria.setProjection(Projections.property("id")); Criteria criteria = getSession().createCriteria(Category.class); criteria.add(Restrictions.eq("active", Boolean.TRUE)); criteria.add(Subqueries.propertyEq("parent", subCriteria)); 

It will generate a request like:

select * from Category this_ where this_.active = 1 and this_.parentId = (Select this0_.id as y0 from Category this0__ where this0_.parentId is null and this0_.active = 1 and this0 __. name = 'Health Plan')

Good luck

-Rohtash Singh

Answer 3

Once you first verify the ORM configuration between the Car and CarPart objects, you usually need to establish a relationship between them. After that, try the following code:

 List result = session.createQuery("from " + CarPart.class.getName() + " as parts join parts.car as car where car.owner = :myOwner") .setParameter("myOwner", 123) .list();