Spring JPA Data - Custom query with multiple aggregate functions as a result

Question

I tried to return the average value and the number of sets of ratings in one query. I dealt with this quite easily in two queries, following the example I found in the browser. For instance:

@Query("SELECT AVG(rating) from UserVideoRating where videoId=:videoId") public double findAverageByVideoId(@Param("videoId") long videoId); 

but as soon as I wanted to average and count in the same query, the problem started. After many hours of experimentation, I found that it worked, so I share it here. Hope this helps.

1) I need a new class for the results:

I had to refer to this class in the request:

 @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 the average rating and the number of ratings

Answer 1

I decided it myself.

Custom class to get 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); 

Answer 2

Thanks.

You should disable NPE and hibernate parsing error tuples as follows:

 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; }}