Pranav C Balan Pranav C Balan - 7 months ago 1142
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

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