How to get multiple averages with jpa repository (spring boot)

Question

Based in the thread: Spring Data JPA - Custom Query with multiple aggregate functions in result

I have this query in my jpa repository

@Query("SELECT new mx.com.sk.AveragesPojo(AVG(a.initial), AVG(a.initialEFSL), AVG(a.finalEFSL), AVG(a.entitySettlement)) FROM AveragesModel AS a WHERE groupName = :groupName AND idRemote = :idRemote")
    public AverajesPojo getLastSurveyAverages(@Param("groupName") String groupName, @Param("idRemote") Long idRemote);
}

And in my pojo constructor is:

public AverajesPojo(Float initial, Float initialEFSL, Float entitySettlement, Float finalEFSL) {
    super();
    this.initial = initial;
    this.initialEFSL = initialEFSL;
    this.entitySettlement = entitySettlement;
    this.finalEFSL = finalEFSL;
}

But I have this error:

Error creating bean with name 'averagesRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract mx.com.sk.pojos.AverajesPojo mx.com.sk.admin.repositories.AveragesRepository.getLastSurveyAverages(java.lang.String,java.lang.Long)!

what's my error?

kj007 · Accepted Answer · 2018-10-14T04:51:43.600

Please use double instead of float in your DTO constructure, if you want to return float from DTO then you can mange it in your constructure..

The AVG function takes a state field path expression as an argument and calculates the average value of the sate field over the group. The state field must be numeric, and the result is returnd as a Double.

public class AveragesPojo {
    private double initial;
    private double initialEFSL;
    private double entitySettlement;
    private double finalEFSL;

    public AveragesPojo(double initial, double initialEFSL, double entitySettlement, double finalEFSL) {
        super();
        this.initial = initial;
        this.initialEFSL = initialEFSL;
        this.entitySettlement = entitySettlement;
        this.finalEFSL = finalEFSL;
    }

}

Please also use a.groupName & a.idRemote and return type should be same as constrcuture as they have same type values will be return but they will be according to your query parameters. so have them like your constructure. entitySettlement and finalEFSL:

@Query("SELECT new mx.com.sk.AveragesPojo(AVG(a.initial), AVG(a.initialEFSL), AVG(a.entitySettlement), AVG(a.finalEFSL )) FROM AveragesModel AS a WHERE a.groupName = :groupName AND a.idRemote = :idRemote")
public AverajesPojo getLastSurveyAverages(@Param("groupName") String groupName, @Param("idRemote") Long idRemote);
}

How to get multiple averages with jpa repository (spring boot)

1 Answers1