Spring Data JPA - Custom Query with multiple aggregate functions in result

Question

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:

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

Answer 1

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

Answer 2

Thanks.

You should prevent NPEs and hibernate parsing tuple errors as following :

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