JPA JPQL：使用COUNT，GROUP BY和ORDER BY选择NEW [英] JPA JPQL: SELECT NEW with COUNT, GROUP BY and ORDER BY

问题描述

有2个表/实体： AppleTree 和 Apples 。一棵苹果树产生0 ... n个苹果。每个苹果都是第二个表的实体/行，并引用生成它的苹果树（ ManyToOne ）。

<我想在最有成效的苹果树上制作一份高分报告。它应按COUNT列按降序排序：

  APPLE TREE | COUNT（A）
 --------------------- 
 10304 | 1000 
 72020 | 952 
 31167 | 800 
  

为了处理这些结果，我创建了一个非实体bean，它结合了AppleTree对象和一个长值（对于COUNT）：

  //构造函数
 public AppleStats（AppleTree at，long howManyApples）{。 ..} 
  

我想使用JPQL获取此组合类型的行。我的方法基于 SELECT NEW 语法：

  SELECT NEW foo .bar.AppleStats（a.appleTree，COUNT（a）AS c）
 FROM Apples a 
 GROUP BY a.appleTree 
 ORDER BY c DESC 
  

不幸的是它产生了很多错误信息。我认为一个问题是COUNT值的列别名。我使用它，因为我想按这个聚合值排序。这意味着我使用COUNT（a）AS c还是COUNT（a）c没有区别。它说参数没有用逗号分隔。而且，表达式无效，这意味着它不遵循JPQL语法。最后它说没有找到与参数类型匹配的构造函数。

有没有办法获取AppleStats结果行？或者做我必须回归原生查询？

解决方案

试试这个查询：

  SELECT NEW foo.bar.AppleStats（a.appleTree，COUNT（a.appleTree））
 FROM Apples a 
 GROUP BY a.appleTree 
 ORDER BY COUNT（a.appleTree）DESC 
  

不幸的是我没有JPA平台来测试它现在，但上面应该修复原始查询的语法问题。并且不要担心 COUNT（）聚合函数出现两次的事实，这是一个很好的编写查询时常见的习惯用法，任何体面的优化器都应该能够处理它并只执行一次操作。

There are 2 tables / entities: AppleTree and Apples. An apple tree produces 0...n apples. Each apple is an entity / row of the second table and references the apple tree that has produced it (ManyToOne).

I want to generate a "high score" report on the most productive apple trees. It should be ordered by the COUNT column in descending order:

APPLE TREE | COUNT(A)
---------------------
10304      | 1000
72020      | 952
31167      | 800

In order to handle these results, I created a non-entity bean that combines an AppleTree object and a long value (for COUNT):

// constructor
public AppleStats (AppleTree at, long howManyApples) { ... }

I want to fetch rows of this combined type using JPQL. My approach is based on the SELECT NEW syntax:

SELECT NEW foo.bar.AppleStats ( a.appleTree, COUNT(a) AS c )
FROM Apples a
GROUP BY a.appleTree
ORDER BY c DESC

Unfortunately it produces quite a few error messages. I think one problem is the column alias for the COUNT value. I use it as I want to sort by this aggregate value. It means no difference whether I use "COUNT(a) AS c" or "COUNT(a) c". It says the arguments were not separated by comma. Moreover, the "expression is invalid, which means it does not follow the JPQL grammar". Finally it says No constructors can be found that match the argument types."

Is there a way to get my AppleStats result rows? Or do I have to regress to native queries?

解决方案

Try this query:

SELECT NEW foo.bar.AppleStats(a.appleTree, COUNT(a.appleTree))
FROM Apples a
GROUP BY a.appleTree
ORDER BY COUNT(a.appleTree) DESC

Unfortunately I don't have a JPA platform to test it right now, but the above should fix the syntax problems of the original query. And don't worry about the fact that the COUNT() aggregate function appears twice, that's a pretty common idiom when writing queries and any decent optimizer should be able to handle it and perform the operation only once.

这篇关于JPA JPQL：使用COUNT，GROUP BY和ORDER BY选择NEW的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！