How to Return a Custom Object from a Spring Data JPA Group by Query

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

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.

Return custom object from Spring Data Jpa query

You can easily achive this using projection.
Here you have the bellow columns :

private String firstName;
private String lastName;
private Long id;

Create an Interface with getter from your query. Your projection will like this:

public interface ITestProjection {
    Long getId();
    Integer getCount();
    String getFirstName();
    String getLastName();
}

Your Query will like this :

@Query(value = "SELECT professor.id, professor.department_id, " +
                    "professor.first_name, professor.last_name, " +
                    "professor.email, COUNT(professor_id) as count " +
                    "FROM professor LEFT JOIN student_internship ON professor.id = professor_id " +
                    "GROUP BY professor_id ORDER BY count DESC LIMIT =?1", nativeQuery = true)
    ArrayList<ITestProjection> findDataWithCount(Integer limit);

Hope this will solve your problem.

For more details visit this thread.

Thanks :)

How to return a pageable custom object from a Spring Data JPA with multiple counts and Group by query?

I guess Spring Data just doesn't support that, but I'm not sure.

However, this is a perfect use case for Blaze-Persistence Entity Views.

Blaze-Persistence is a query builder on top of JPA which supports many of the advanced DBMS features on top of the JPA model. I created Entity Views on top of it to allow easy mapping between JPA models and custom interface defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure the way you like and map attributes(getters) via JPQL expressions to the entity model. Since the attribute name is used as default mapping, you mostly don't need explicit mappings as 80% of the use cases is to have DTOs that are a subset of the entity model.

A projection with Entity Views could look as simple as the following

@EntityView(Laptop.class)
interface LaptopModelCount {
    @IdMapping
    long getLaptopModel();
    @Mapping("COUNT(*)")
    long getQty();
    @Mapping("COUNT(*) FILTER (WHERE status = 'ready')")
    long getReady();
    @Mapping("COUNT(*) FILTER (WHERE status = 'partsOnly')")
    long getPartsOnly();
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

LaptopModelCount dto = entityViewManager.find(entityManager, LaptopModelCount.class, id);

But the Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

This will create exactly the query that you are expecting and also works with pagination.

Page<LaptopModelCount> findAll(Pageable pageable);

You can also control which of the attributes you want to fetch through a custom EntityViewSettingProcessor:

List<LaptopModelCount> findAll(EntityViewSettingProcessor<LaptopModelCount> processor);

In that you can call EntityViewSetting.fetch("qty") to override what should actually be fetched, which you seem to require as you use the same model with different requirements.

Need to return List of Custom Object using JPA @Query

Spring has an annotation called @SqlResultSetMapping
This annotation can be used for above problem. Details about this annotation can be found from following link:

Annotation Type ConstructorResult

This is actually mapping a hibernate query result into a plain POJO Class. Thats what actually needed.

Example:

   Query q = em.createNativeQuery(
      "SELECT c.id, c.name, COUNT(o) as orderCount, AVG(o.price) AS avgOrder " +
      "FROM Customer c, Orders o " +
      "WHERE o.cid = c.id " +
      "GROUP BY c.id, c.name",
      "CustomerDetailsResult");

   @SqlResultSetMapping(
       name="CustomerDetailsResult",
       classes={
          @ConstructorResult(
               targetClass=com.acme.CustomerDetails.class,
                 columns={
                    @ColumnResult(name="id"),
                    @ColumnResult(name="name"),
                    @ColumnResult(name="orderCount"),
                    @ColumnResult(name="avgOrder", type=Double.class)
                    }
          )
       }
      )

Put @SqlResultSetMapping into top of any @Entity and make sure the datatype returning from database is same as your POJO Class datatype.

Hope this will help you. Enjoy :)

How to return custom object using projection in Spring Data Jpa?

For anyone running into the same problem

I am using Hibernate 5.1.2.Final

I guess there's a bug and I need to upgrade to hibernate 5.2.11 to make it work
See this link

Return custom object from Spring Data with Native Query

Found the answer on another post. Basically I used SqlResultSetMapping along with ConstructorResult (no other way worked out) with a special attention to a comment on the accepted answer of the mentioned post: you need to add the @NamedNativeQuery annotation to the entity of the used interface AND prepend the entity's name with a . otherwise it won't work.

Example:

@Entity
@Table(name = "grupo_setorial")
@SqlResultSetMapping(
        name = "mapeamentoDeQuadrantes",
        classes = {
                @ConstructorResult(
                        targetClass = Coordenada.class,
                        columns = {
                                @ColumnResult(name = "latitude"),
                                @ColumnResult(name = "longitude")
                        }
                )
        }
)
@NamedNativeQuery(
        name = "GrupoCensitario.obterPerimetroDosSetores",
        query = "SELECT latitude as latitude, longitude as longitude FROM coordenadas where id_setor IN (:setores)",
        resultSetMapping = "mapeamentoDeQuadrantes"
)
public class GrupoCensitario {

Create Map object from GROUP BY in JPA Query

What you need is an intermediate function like this:

public interface SomeRepository extends JpaRepository<SomeEntity, Long> {
    
    @Query("SELECT " +
            "c.id, d.deviceName " +
            "FROM Company c " +
            "JOIN c.employees e " +
            "JOIN e.devices d " +
            "WHERE c.id IN :companyIds"
    )
    List<Object[]> findDevicesForCompanies0(@Param("companyIds") List<Long> companyIds);

    default Map<Long, List<String>> findDevicesForCompanies(List<Long> companyIds) {
        return findDevicesForCompanies(companyIds).stream()
            .collect(
                Collectors.groupingBy(
                    o -> (Long) o[0],
                    Collectors.mapping( o -> (String) o[1], Collectors.toList() )
                )
            );
    }

}

Another nice way to solve this would be to use Blaze-Persistence Entity Views which I think this is a perfect use case for.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(Company.class)
public interface CompanyDevices {
    @IdMapping
    Long getId();
    @Mapping("employees.devices.deviceName")
    Set<String> getDeviceNames();
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

CompanyDevices a = entityViewManager.find(entityManager, CompanyDevices.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Page<CompanyDevices> findAll(Pageable pageable);

Or in your particular case

List<CompanyDevices> findByIdIn(List<Long> companyIds);

The best part is, it will only fetch the state that is actually necessary!

---

Related Topics