Clash Royale CLAN TAG#URR8PPP

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, "a1"],
[2, "a2"]
]


I would like to get something like this:

[
"cnt":1, "answer":"a1" ,
"cnt":2, "answer":"a2"
]


How can I achieve this?

5 Answers
5

Solution for JPQL queries

This is supported for JPQL queries within the JPA specification.

Step 1: Declare a simple bean class

package com.path.to;

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

public interface SurveyRepository extends CrudRepository<Survey, Long>
@Query("SELECT " +
" new com.path.to.SurveyAnswerStatistics(v.answer, COUNT(v)) " +
"FROM " +
" Survey v " +
"GROUP BY " +
" v.answer")
List<SurveyAnswerStatistics> findSurveyCount();



MyBean

com.path.to

com.path.to.MyBean

MyBean

new

SELECT new com.path.to.MyBean(...)

SELECT com.path.to.MyBean(...)

@Query("SELECT ...")

@Query(value = "SELECT ...")

@Query(value = "SELECT ...", nativeQuery = false)

@Query(value = "SELECT ...", nativeQuery = true)

new

com.path.to.MyBean

Solution for native queries

As noted above, the new ... syntax is a JPA-supported mechanism and works with all JPA providers. However, if the query itself is not a JPA query, that is, it is a native query, the new ... syntax will not work as the query is passed on directly to the underlying RDBMS, which does not understand the new keyword since it is not part of the SQL standard.

new ...

new ...

new

In situations like these, bean classes need to be replaced with Spring Data Projection interfaces.

Step 1: Declare a projection interface

package com.path.to;

public interface SurveyAnswerStatistics
String getAnswer();

int getCnt();



Step 2: Return projected properties from the query

public interface SurveyRepository extends CrudRepository<Survey, Long>
@Query(nativeQuery = true, value =
"SELECT " +
" v.answer AS answer, COUNT(v) AS cnt " +
"FROM " +
" Survey v " +
"GROUP BY " +
" v.answer")
List<SurveyAnswerStatistics> findSurveyCount();



Use the SQL AS keyword to map result fields to projection properties for unambiguous mapping.

AS

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Unable to locate class [SurveyAnswerReport] [select new SurveyAnswerReport(v.answer,count(v.id)) from com.furniturepool.domain.Survey v group by v.answer] at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1750) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677) at org.hibernate.jpa.spi.AbstractEnti..........

SurveyAnswerReport

SurveyAnswerStatistics

SurveyAnswerReport

com.domain.dto.SurveyAnswerReport

JpaRepository

This SQL query return List< Object > would.

You can do it this way:

@RestController
@RequestMapping("/survey")
public class SurveyController

@Autowired
private SurveyRepository surveyRepository;

@RequestMapping(value = "/find", method = RequestMethod.GET)
public Map<Long,String> findSurvey()
List<Object> result = surveyRepository.findSurveyCount();
Map<Long,String> map = null;
if(result != null && !result.isEmpty())
map = new HashMap<Long,String>();
for (Object object : result)
map.put(((Long)object[0]),object[1]);


return map;




I know this is an old question and it has already been answered, but here's another approach:

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


define a custom pojo class say sureveyQueryAnalytics and store the query returned value in your custom pojo class

@Query(value = "select new com.xxx.xxx.class.SureveyQueryAnalytics(s.answer, count(sv)) from Survey s group by s.answer")
List<SureveyQueryAnalytics> calculateSurveyCount();


I do not like java type names in query strings and handle it with a specific constructor.
Spring JPA implicitly calls constructor with query result in HashMap parameter:

@Getter
public class SurveyAnswerStatistics
public static final String PROP_ANSWER = "answer";
public static final String PROP_CNT = "cnt";

private String answer;
private Long cnt;

public SurveyAnswerStatistics(HashMap<String, Object> values)
this.answer = (String) values.get(PROP_ANSWER);
this.count = (Long) values.get(PROP_CNT);



@Query("SELECT v.answer as "+PROP_ANSWER+", count(v) as "+PROP_CNT+" FROM Survey v GROUP BY v.answer")
List<SurveyAnswerStatistics> findSurveyCount();


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.