Custom Object Return in Spring Data JPA GROUP BY Queries

Introduction

Retrieving data as custom objects from Spring Data JPA GROUP BY queries enhances data presentation and simplifies further processing. This guide explores how to achieve this, showcasing solutions for both JPQL and native queries.

JPQL Queries

JPQL queries within the JPA specification offer native support for returning custom objects.

Step 1: Create a Custom Bean

Define a simple bean class to represent the desired output structure:

<code class="java">public class SurveyAnswerStatistics {
  private String answer;
  private Long cnt;

  // Constructor
}</code>

Copy after login

Step 2: Return Bean Instances

Update the repository method to return instances of the custom bean:

<code class="java">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();
}</code>

Copy after login

Native Queries

While native queries lack direct support for the new keyword, Spring Data Projection interfaces provide an alternative solution:

Step 1: Define a Projection Interface

Create a projection interface with properties corresponding to the desired output:

<code class="java">public interface SurveyAnswerStatistics {
  String getAnswer();
  int getCnt();
}</code>

Copy after login

Step 2: Return Projected Properties

Update the repository method to return projected properties:

<code class="java">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();
}</code>

Copy after login

Employ the SQL AS keyword to map result fields to projection properties seamlessly.

The above is the detailed content of How to Return Custom Objects from Spring Data JPA GROUP BY Queries?. For more information, please follow other related articles on the PHP Chinese website!