Hibernate criteria api 'Select in'

hibernate subquery hibernate-criteria
ABX picture ABX · Nov 4, 2010 · Viewed 7.5k times · Source

is it possible to create a 'select in'-query with the hibernate critiria api ?

Example : I have two tables in a 1:n relation, company and department

select * from company c where c.id in (select company_id from department d 
where d.departmentname = 'HR' and d.location = 'xyz')

Answer

Damian Leszczyński - Vash picture Damian Leszczyński - Vash · Nov 4, 2010

You can use for this DetachedCriteria 

DetachedCriteria subCriteria= DetachedCriteria.forClass(Departament.class);
     subCriteria.add(Property.forName("departmentname ").eq("HR"));
     subCriteria.add(Property.forName("location ").eq("xyz"));
     subCriteria.setProjection(Projections.property("company_id "));

DetachedCriteria criteria = DetachedCriteria.forClass(Company.class);
     criteria.add(Property.forName("id").in(subCriteria));

Related questions

Hibernate Criteria With Property Not In (Subquery)

I want to execute query something like Select id, name from information where name not in (select firstname from contact where id = 1) Information Id Name 1 Test Contact id firstname 1 name 2 Test If I am using neProperty() function, it will returns …

Read More
Hibernate Criteria and Count Column

I am trying to return an entity with a column that has the count of another table that is a one to many relation. I want to do this using hibernate criteria, not HQL. select p.*, (select count(*) from child …

Read More
Hibernate Criteria Subquery

I need to do this SQL query with detachedCriteria: SELECT g.id FROM games g WHERE NOT EXISTS ( SELECT 1 FROM users_games ug WHERE ug.user_id = 1 AND g.id = ug.game_id) The idea is to get the ids …

Read More