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 lt   gt  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

User · Answer

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

User · Answer

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 lt SureveyQueryAnalytics gt  calculateSurveyCount

User · Answer

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 lt Survey  Long gt         Query  SELECT                     new com path to SurveyAnswerStatistics v answer  COUNT v                   FROM                     Survey v                 GROUP BY                     v answer       List lt SurveyAnswerStatistics gt  findSurveyCount        Important notes   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   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  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  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 lt Survey  Long gt         Query nativeQuery   true  value               SELECT                     v answer AS answer  COUNT v  AS cnt                 FROM                     Survey v                 GROUP BY                     v answer       List lt SurveyAnswerStatistics gt  findSurveyCount        Use the SQL AS keyword to map result fields to projection properties for unambiguous mapping

User · Answer

in Service                               public List lt DevicesPerCustomer gt  findDevicesPerCustomer                         LOGGER info TAG NAME    quot     inside findDevicesPerCustomer    quot                        List lt Object   gt  list   iDeviceRegistrationRepo findDevicesPerCustomer                        List lt DevicesPerCustomer gt  out   new ArrayList lt  gt                         if  list    null  amp  amp   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    quot SELECT d customerId count    FROM senseer DEVICE REGISTRATION d  where d customerId is not null group by d customerId quot   nativeQuery true              List lt Object   gt  findDevicesPerCustomer

User · Answer

Using interfaces you can get simpler code  No need to create and manually call constructors  Step 1  Declare intefrace with the required fields   public interface SurveyAnswerStatistics      String getAnswer      Long getCnt         Step 2  Select columns with same name as getter in interface and return intefrace from repository method   public interface SurveyRepository extends CrudRepository lt Survey  Long gt          Query  select v answer as answer  count v  as cnt                 from Survey v                 group by v answer       List lt SurveyAnswerStatistics gt  findSurveyCount

User · Answer

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  quot  showDataUsingQuery  Query  quot       public List lt Map lt String Object gt  gt  ShowColumNameAndValue  PathVariable  quot Query quot  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 lt Map lt String Object gt  gt  css   jdbc queryForList Query msp            return css

User · Answer

Repository public interface ExpenseRepo extends JpaRepository lt Expense Long gt        List lt Expense gt  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 lt   gt  getAmountByCategory         The above code worked for me

User · Answer

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

User · Answer

This SQL query  return List lt  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 lt Long String gt  findSurvey           List lt Object   gt  result   surveyRepository findSurveyCount           Map lt Long String gt  map   null         if result    null  amp  amp   result isEmpty               map   new HashMap lt Long String gt               for  Object   object   result                map put   Long object 0   object 1                              return map

User · Answer

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

User · Answer

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 lt   gt  findSurveyCount

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

Examples related to java

Examples related to spring

Examples related to spring-mvc

Examples related to jpa

Examples related to repository