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

Question

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?

Solution 1

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

Important notes

1. Make sure to provide the fully-qualified path to the bean class, including the package name. For example, if the bean class is called MyBean and it is in package com.path.to, the fully-qualified path to the bean will be com.path.to.MyBean. Simply providing MyBean will not work (unless the bean class is in the default package).
2. Make sure to call the bean class constructor using the new keyword. SELECT new com.path.to.MyBean(...) will work, whereas SELECT com.path.to.MyBean(...) will not.
3. Make sure to pass attributes in exactly the same order as that expected in the bean constructor. Attempting to pass attributes in a different order will lead to an exception.
4. Make sure the query is a valid JPA query, that is, it is not a native query. @Query("SELECT ..."), or @Query(value = "SELECT ..."), or @Query(value = "SELECT ...", nativeQuery = false) will work, whereas @Query(value = "SELECT ...", nativeQuery = true) will not work. This is because native queries are passed without modifications to the JPA provider, and are executed against the underlying RDBMS as such. Since new and com.path.to.MyBean are not valid SQL keywords, the RDBMS then throws an exception.

---

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.

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.

Solution 2

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

Solution 3

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

Solution 4

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

Solution 5

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

Code needs Lombok for resolving @Getter

View more solutions

Comments

[Pranav C Balan]

It's not working, firing error : 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(Abst‌​ractEntityManagerImp‌​l.java:1750) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(Abst‌​ractEntityManagerImp‌​l.java:1677) at org.hibernate.jpa.spi.AbstractEnti..........

[Bunti]

What is this SurveyAnswerReport in your output. I assume you replaced SurveyAnswerStatistics with your own class SurveyAnswerReport. You need to specify the fully qualified class name.

[manish]

The bean class must be fully qualified, that is, include the full package name. Something like com.domain.dto.SurveyAnswerReport.

[Abdullah Khan]

@manish Please post a link where more of this can be read. Thanks!

[manish]

This is part of the JPA specification.

[emeraldjava]

Can you confirm the name of the class which extends the JPARepository in this case, is it called SurveyRepository or SurveyAnswerStatisticsRepository? Thanks

[Dheeraj R]

thanks for your response to this question. It was crisp and clear

[MatMat]

How would you use DATE() in such custom query? I need to group by DATE(created_date);

[marioosh]

I got 'java.lang.IllegalArgumentException: PersistentEntity must not be null!` when i try return custom type from my JpaRepository ? Is some configuration i missed ?

[chrisinmtown]

Don't omit the "new" keyword in the query, if you do you'll see a Hibernate exception "No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode"! (Thanks @manish this was a big help)

[Awanish Kumar]

i am trying to run below query. but it not working can anyone help here plz. @Query(value = "select new com.unilever.d2.integration.transaction.modal.coupons.OfferS‌​tatus(offerId, CASE WHEN (InventoryCount=0 or InventoryCount - ConsumptionCount > 0) and ( StartDate is null or getdate() > StartDate ) and ( EndDate is null or getdate() < EndDate ) THEN 'false' else 'true' end) from OfferMaster where Brand= ?1 and Locale = ?2 and CampaignId = ?3") public List<?> getOffersStatus2(String brand, String locale, String campaignId);

[manish]

@AwanishKumar, I have added some important considerations to the answer. Please review and make sure you are following the listed points. I suspect that you are trying to execute a native SQL query (because CASE, WHEN, THEN and getDate() are not valid JPQL keywords), which is why things are not working for you.

[marioosh]

with native query i get: Couldn't find PersistentEntity for type class com.sun.proxy.$Proxy.... What may be wrong ?

[Ninja]

The solution is better.Or use the projection in the official document.

[user666]

non native query did not work it throws an exception table cannot be mapped

[user666]

native query did not work either. Exception is java.lang.UnsupportedOperationException: Attempted to serialize java.lang.Class: [bean]. Forgot to register a type adapter?

[user666]

@Getter is showing an error before running the code as its not for the object type

[dwe]

Lombok is needed. Just added a footnote to the code.

[Admin]

@manish: If we have more tables and we Join them in this query, We get token (tablename) exception.

[manish]

@Android, can you ask it as a separate question, posting the code that is throwing the error, and error details?

[Mejmo]

Native query does not work for me to. It just returns 0 items, no exception though. If I test the same raw SQL in SQL prompts, it returns the dataset.

[Junaid]

@manish will the Class Base Projection work instead of Projection Interface? Because at then end I need the result as DTO to transfer via REST api. docs.spring.io/spring-data/jpa/docs/current/reference/html/…

[Vineel]

@manish Thanks you saved my night's sleep, your method worked like a charm!!!!!!!

[Yuri Hassle Araújo]

I like your answer because it doesn't force me to create a new class or interface. It worked for me.

[sam]

Works fine but i prefer the usage of Map in the generics instead of ?, as Map will let us access them as key (0) and value (1)

[Mikheil Zhghenti]

While using native query exception says: nested exception is java.lang.IllegalArgumentException: Not a managed type: class ... Why this should be happed ?

[Mikheil Zhghenti]

@manish in my understanding the missed class Survey should be just simple POJO class, right?

[mkczyk]

It doesn't work for me. Constructor is not called. For me works only interface-based projection or class-based projection with new in @Query. It would be great if class-based without new (with this constructor HashMap<String, Object>) worked. But I get org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQu‌​ery$TupleConverter$T‌​upleBackedMap] to type [package.MyClass].

[Pradeepal Sudeshana]

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value.

[sarath]

Thank you... I prefer this solution over the accepted answers native-query solution to avoid a long list of projection interfaces.

[Manoel Stilpen]

seems that spring can't inject the Map object into the constructor because they're of different types. The repository return type is not Map<String, Object> so it cant invoke the apropriated converter. Found the post below which might help bytestree.com/spring/…