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?

Salmi

Answer 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.

Gilgilba

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(AbstractEntityManagerImpl.java:1750) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677) at org.hibernate.jpa.spi.AbstractEnti.......... – Salmi

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. – Scifi

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

@Gilgilba Please post a link where more of this can be read. Thanks! – Canaday

This is part of the JPA specification. – Gilgilba

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

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

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

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 @Gilgilba this was a big help) – Phytology

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.OfferStatus(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); – Colourable

@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. – Gilgilba

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

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

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

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

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

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. – Beck

@Gilgilba 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/… – Menstruum

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

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

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

Dode

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

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

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

This only works while comparing above answers. – Lidialidice

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

Lanceolate

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

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) – Egocentric

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

Allegorize

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

Answer 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

Ghibelline

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

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

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.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [package.MyClass]. – Superscription

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/… – Indoctrinate

Answer 6

@Repository
public interface ExpenseRepo extends JpaRepository<Expense,Long> {
    List<Expense> findByCategoryId(Long categoryId);

    @Query(value = "select category.name,SUM(expense.amount) from expense JOIN category ON expense.category_id=category.id GROUP BY expense.category_id",nativeQuery = true)
    List<?> getAmountByCategory();

}

The above code worked for me.

Quadroon

Answer 7

I used custom DTO (interface) to map a native query to - the most flexible approach and refactoring-safe.

The problem I had with this - that surprisingly, the order of fields in the interface and the columns in the query matters. I got it working by ordering interface getters alphabetically and then ordering the columns in the query the same way.

Strung

Answer 8

I just solved this problem :

• Class-based Projections doesn't work with query native(@Query(value = "SELECT ...", nativeQuery = true)) so I recommend to define custom DTO using interface .
• Before using DTO should verify the query syntatically correct or not

Godden

Answer 9

Get data with column name and its values (in key-value pair) using JDBC:

/*Template class with a basic set of JDBC operations, allowing the use
  of named parameters rather than traditional '?' placeholders.
 
  This class delegates to a wrapped {@link #getJdbcOperations() JdbcTemplate}
  once the substitution from named parameters to JDBC style '?' placeholders is
  done at execution time. It also allows for expanding a {@link java.util.List}
  of values to the appropriate number of placeholders.
 
  The underlying {@link org.springframework.jdbc.core.JdbcTemplate} is
  exposed to allow for convenient access to the traditional
  {@link org.springframework.jdbc.core.JdbcTemplate} methods.*/


@Autowired
protected  NamedParameterJdbcTemplate jdbc;


@GetMapping("/showDataUsingQuery/{Query}")
    public List<Map<String,Object>> ShowColumNameAndValue(@PathVariable("Query")String Query) throws SQLException {

      /* MapSqlParameterSource class is intended for passing in a simple Map of parameter values
        to the methods of the {@link NamedParameterJdbcTemplate} class*/

       MapSqlParameterSource msp = new MapSqlParameterSource();

       // this query used for show column name and columnvalues....
        List<Map<String,Object>> css = jdbc.queryForList(Query,msp);

        return css;
    }

Karney

Answer 10

    //in Service      
      `
                public List<DevicesPerCustomer> findDevicesPerCustomer() {
                    LOGGER.info(TAG_NAME + " :: inside findDevicesPerCustomer : ");
                    List<Object[]> list = iDeviceRegistrationRepo.findDevicesPerCustomer();
                    List<DevicesPerCustomer> out = new ArrayList<>();
                    if (list != null && !list.isEmpty()) {
                        DevicesPerCustomer mDevicesPerCustomer = null;
                        for (Object[] object : list) {
                            mDevicesPerCustomer = new DevicesPerCustomer();
mDevicesPerCustomer.setCustomerId(object[0].toString());
                            mDevicesPerCustomer.setCount(Integer.parseInt(object[1].toString()));
                            
                            out.add(mDevicesPerCustomer);
                        }
                    }
            
                    return out;
                }`
        
    //In Repo
        `   @Query(value = "SELECT d.customerId,count(*) FROM senseer.DEVICE_REGISTRATION d  where d.customerId is not null group by d.customerId", nativeQuery=true)
            List<Object[]> findDevicesPerCustomer();`

Lelahleland

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. – Peseta