Criteria subquery with not null

Question

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.

Calvin

BTW, i have a serious problem recently with adjusting any code block when posting a new question, i do as usual copy the code from my IDE or from text file and highlight the code then surround it with the code button, but it gets displayed badly. – Calvin

That's because you're using tabs instead of spaces. – Hammy

can you please tell me how was you able to reformat the code, so i can avoid such thing in future ? – Calvin

In Eclipse I have settings to replace tabs with space, so when I copy my code I don't have to do nothing. In your case I've just removed the tabs and inserted spaces (while in lists you might need to type 8 spaces instead of 4.) – Hammy

two more questions: 1- how to do such setting in eclipse ? 2- how did you removed the tabs and inserted spaces in my case, copied the code to your eclipse or did that manually ? – Calvin

1. Preferences -> Java -> CodeStyle -> Formatter -> Edit -> Indentation -> Tab policy: Spaces only. I've just done it manually - it was few lines, so it wasn't a big pain :-) – Hammy

Answer 1

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.

Patty

it works fine, thanks, but can you please provide little explanation for the last two lines, i couldn't find doc for them. – Calvin