JPA：如何在不加载延迟加载集的情况下计算子记录 [英] JPA: How to count child records without loading a lazy loaded set

问题描述

我正在编写J2EE / JPA / Spring 3应用程序，试图保持纯粹的JPA 2.0。我想得到一些子对象，而不必加载它们，因为它显然是一个昂贵的操作。例如，这是一个简化的例子

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

在jsp页面上，我想显示所有组织的列表以及没有自己加载员工的员工数量。如果它可以是单个数据库命中加载所有组织对象，并以某种方式加载一个很好的Employee对象的计数。我宁愿避免一个查询列出组织，然后为每个组织列出一个计算员工的计数。我想我可以添加一个瞬态属性来保持计数，我不知道如何最好地做到这一点。

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.

为了给出规模的概念，将有大约50个组织，每个组织将有0到500名员工。我宁愿避免任何特定于实现的扩展，因为我已经更改了JPA提供程序一次并且可能会再次更改。

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.

在SQL中我只是做一个连接，组和数，但我不知道如何在JPA中做到这一点。任何帮助表示赞赏！

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!

推荐答案

您可以直接选择您定义的对象作为保存组织的结果和计数。然后你只需编写查询。唯一的诀窍是您必须按组织的每个字段手动分组。 'GROUP BY ORGANIZATION'不合法。

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

这篇关于JPA：如何在不加载延迟加载集的情况下计算子记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！