Pranav C Balan Pranav C Balan - 1 year ago 2361
Java Question

How to return a custom object from a Spring Data JPA GROUP BY query

I'm developing a Spring Boot application with Spring Data JPA. I'm using a custom JPQL query to group by some field and get the count. Following is my repository method.

@Query(value = "select count(v) as cnt, v.answer from Survey v group by v.answer")
public List<?> findSurveyCount();

It's working and result is obtained as follows:

[1, ""],
[2, "a2"]

I would like to get something like this:

{ "cnt":1, "answer":"a1" },
{ "cnt":2, "answer":"a2" }

How can I achieve this?

Answer Source

Step 1: Declare a simple bean class

public class SurveyAnswerStatistics {
  private String answer;
  private Long   cnt;

  public SurveyAnswerStatistics(String answer, Long cnt) {
    this.answer = answer;
    this.count  = cnt;

Step 2: Return bean instances from the repository method

@Query(value = "select new SurveyAnswerStatistics(v.answer, count(v)) from Survey v group by v.answer")
List<SurveyAnswerStatistics> findSurveyCount();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download