JPA和聚合函数。如何使用查询结果？ [英] JPA and aggregate functions. How do I use the result of the query?

问题描述

我是ORM的新手，我需要一些帮助来理解一些东西。

I'm new to ORM stuff and I need some help understanding something.

假设我有以下标准SQL查询：

Let's assume I have the following standard SQL query:

SELECT *, COUNT(test.testId) AS noTests FROM inspection
LEFT JOIN test ON inspection.inspId = test.inspId
GROUP BY inspection.inspId

我想在JPA中使用。

我有一个与Test实体有一对多关系的Inspection实体。 （检查有很多测试）
我试过在JPQL中写这个：

I have an Inspection entity with a one-to-many relationship to a Test entity. (an inspection has many tests) I tried writing this in JPQL:

Query query = em.createQuery("SELECT insp, COUNT(???what???) " +
      "FROM Inspection insp LEFT JOIN insp.testList " +  
      "GROUP BY insp.inspId");

1）如何编写COUNT子句？我必须将count应用于测试表中的元素，但testList是一个集合，所以我不能像 COUNT（insp.testList.testId）

1) How do I write the COUNT clause? I'd have to apply count to elements from the test table but testList is a collection, so I can't do smth like COUNT(insp.testList.testId)

2）假设1已解决，将返回什么类型的对象。它肯定不是检验对象......我如何使用结果？

2) Assuming 1 is resolved, what type of object will be returned. It will definitely not be an Inspection object... How do I use the result?

推荐答案

1. 您可以为已加入的实体提供别名（使用 AS ）


2. 您可以使用返回值创建新对象或 List

1. You can give an alias to the joined entity (with AS)
2. You can create either a new object, or a List with the returned values

所以：

SELECT new com.yourproject.ResultHolder(insp, COUNT(test.testId)) 
    FROM Inspection insp LEFT JOIN  insp.testList AS test GROUP BY insp.inspId

或

SELECT new list(insp, COUNT(test.testId)) 
    FROM Inspection insp LEFT JOIN  insp.testList AS test GROUP BY insp.inspId

结果是然后可以作为 ResultHolder 的实例访问，或者作为 java.util.List 访问，其中 insp 是 list.get（0），计数是 list.get（1）

The result is then accessible either as an instance of ResultHolder, or as a java.util.List, where the insp is list.get(0), and the count is list.get(1)

这篇关于JPA和聚合函数。如何使用查询结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！