java Spring Data JPA - 结果中具有多个聚合函数的自定义查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32049001/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Spring Data JPA - Custom Query with multiple aggregate functions in result
提问by formica
I was trying to return an average and count of a set of ratings in one query. I managed it fairly easily in two queries following the example I found browsing. For example:
我试图在一个查询中返回一组评分的平均值和计数。按照我发现的浏览示例，我在两个查询中相当容易地管理它。例如：
@Query("SELECT AVG(rating) from UserVideoRating where videoId=:videoId")
public double findAverageByVideoId(@Param("videoId") long videoId);
but as soon as I wanted an average and a count in the same query, the trouble started. After many hours experimenting, I found this worked, so I am sharing it here. I hope it helps.
但是一旦我想要在同一个查询中获得平均值和计数，麻烦就开始了。经过几个小时的试验，我发现这很有效，所以我在这里分享。我希望它有帮助。
1) I needed a new class for the results:
1）我需要一个新的结果类：
The I had to reference that class in the query:
我必须在查询中引用该类：
@Query("SELECT new org.magnum.mobilecloud.video.model.AggregateResults(AVG(rating) as rating, COUNT(rating) as TotalRatings) from UserVideoRating where videoId=:videoId")
public AggregateResults findAvgRatingByVideoId(@Param("videoId") long videoId);
One query now returns average rating and count of ratings
一个查询现在返回平均评分和评分计数
回答by formica
Solved myself.
自己解决了。
Custom class to receive results:
接收结果的自定义类：
public class AggregateResults {
private final double rating;
private final int totalRatings;
public AggregateResults(double rating, long totalRatings) {
this.rating = rating;
this.totalRatings = (int) totalRatings;
}
public double getRating() {
return rating;
}
public int getTotalRatings() {
return totalRatings;
}
}
and
和
@Query("SELECT new org.magnum.mobilecloud.video.model.AggregateResults(
AVG(rating) as rating,
COUNT(rating) as TotalRatings)
FROM UserVideoRating
WHERE videoId=:videoId")
public AggregateResults findAvgRatingByVideoId(@Param("videoId") long videoId);
回答by kCH
Thanks.
谢谢。
You should prevent NPEs and hibernate parsing tuple errors as following :
您应该防止 NPE 和休眠解析元组错误，如下所示：
public class AggregateResults {
private final double rating;
private final int totalRatings;
public AggregateResults(Double rating, Long totalRatings) {
this.rating = rating == null ? 0 : rating;
this.totalRatings = totalRatings == null ? 0 : totalRatings.intValue();
}
public double getRating() {
return rating;
}
public int getTotalRatings() {
return totalRatings;
}}
