9

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.

snieguu
  • 2,073
  • 2
  • 20
  • 39
Mahmoud Saleh
  • 33,303
  • 119
  • 337
  • 498
  • 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. – Mahmoud Saleh Dec 05 '11 at 11:16
  • That's because you're using tabs instead of spaces. – Piotr Nowicki Dec 05 '11 at 11:36
  • can you please tell me how was you able to reformat the code, so i can avoid such thing in future ? – Mahmoud Saleh Dec 05 '11 at 12:17
  • 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.) – Piotr Nowicki Dec 05 '11 at 12:45
  • 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 ? – Mahmoud Saleh Dec 05 '11 at 12:52
  • 1
    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 :-) – Piotr Nowicki Dec 05 '11 at 12:58

1 Answers1

24
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.

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • it works fine, thanks, but can you please provide little explanation for the last two lines, i couldn't find doc for them. – Mahmoud Saleh Dec 05 '11 at 12:50