JPA: How to count child records without loading a lazy loaded set

Question

I'm writing a J2EE/JPA/Spring 3 application, trying to stay pure JPA 2.0. I want to get a count of child objects without having to load them, as it's obviously an expensive operation. For example here's a simplified example

Organisation
 - OrgID
 - OrgName

Employee
 - EmployeeID
 - OrgID (key to Organisation table)
 - EmployeeName

On a jsp page I want to show the list of all organisations and a count of the number of employees without loading the employees themselves. If it can be a single database hit that loads all the Organisation objects and somehow loads a count of the Employee objects that would be great. I'd rather avoid one query to list the organisations then one for each organisation to count the employees. I guess I could add a transient property to hold the count, I'm not sure how to best do that.

Just to give an idea of scale, there will be around 50 organisations, and each organisation will have between 0 and 500 employees. I would rather avoid any implementation specific extensions, as I've changed JPA providers once and may change again.

In SQL I'd just do a join, group, and count, but I don't have a clue how to do it in JPA. Any help appreciated!

Answer 1

You can select directly into an object that you define to be the result that holds the organisation and the count. Then you just write the query. The only trick is that you have to manually group by every field on the Organisation. 'GROUP BY ORGANISATION' is not legal.

public class OrgWithEmpCount {
  private Organisation org;
  private Long empCount;
  public OrgWithEmpCount(Organisation org, Long empCount) {
    this.org = org;
    this.empCount = empCount;
  }
}


Select new full.package.OrgWithEmpCount(o, count(e.employeeId)) 
from Organisation o, IN(o.employees) e 
group by o.orgId, o.orgName, o.whateverElse

Answer 2

Going off of the the accepted answer from Affe - this worked except for the case of you wanting to have the count for companies even with no employees. The IN query will end up just excluding those companies. Hard to imagine a company with zero employees, but for the sake of the Query example, you could do it like this:

select new full.package.OrgWithEmpCount(o, count(e.employeeId))
FROM Organisation o LEFT JOIN e.employees AS e
group by o.orgId, o.orgName, o.whateverElse

You get the idea... just instead of doing an IN, do a LEFT JOIN.