Criteria subquery with not null

Mahmoud Saleh picture Mahmoud Saleh · Dec 5, 2011 · Viewed 20.6k times · Source

I want to convert the following subquery to use hibernate subquery:

getCurrentSession().createQuery("from Employee where id in (select adminId from Department where adminId is not null)")
                   .list();
  • Employee:

    @ManyToOne
    @JoinColumn(name = "fk_department_id", nullable = true) 
    private Department department;
    
  • Department:

    @OneToMany(fetch = FetchType.EAGER)
    @JoinColumn(name = "fk_department_id")
    private Set<Employee> employees = new HashSet<Employee>(0);
    

Can anyone please provide me with an example of this convert, because i read some examples and i still cannot figure out how to do that.

Answer

JB Nizet picture JB Nizet · Dec 5, 2011
Criteria c = session.createCriteria(Employee.class, "e");
DetachedCriteria dc = DetachedCriteria.forClass(Departemt.class, "d");
dc.add(Restrictions.isNotNull("d.adminId");
dc.setProjection(Projections.property("d.adminId"));
c.add(Subqueries.propertyIn("e.id", dc));

The setProjection call makes the subquery return the adminId property only instead of the whole Department entity. The Subqueries.propertyIn creates a restriction: the property id of the searched employee must be in the set of results returned by the subquery.