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

Question

I've done this Custom object from JPA with GROUP BY query and multiple counts with one SQL query

The following two methods findLaptopTotalQty and findLaptopQtySummary working perfectly

@Query("SELECT "
        + "new com.path.to.LaptopModelCount"
        + "(l.laptopModel AS laptopModel, COUNT(l.laptopModel) AS qty) "
        + "FROM Laptop l "
        + "GROUP by l.laptopModel")
Page<LaptopModelCount> findLaptopTotalQty(Pageable pageable);


@Query("SELECT "
            + "new com.path.to.LaptopModelCount"
            + "(l.laptopModel AS laptopModel, COUNT(l.laptopModel) AS qty,"
            + "sum(case when l.status = 'ready' then 1 else 0 end) AS ready," 
            + "sum(case when l.status = 'partsOnly' then 1 else 0 end) AS partsOnly)"
            + "FROM Laptop l "
            + "GROUP by l.laptopModel")
List<LaptopModelCount> findLaptopQtySummary();

However when I change List<LaptopModelCount> findLaptopQtySummary(); to Page<LaptopModelCount> findLaptopQtySummary(Pageable pageable);, I got org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: select near...

LaptopModelCount.Class

package com.path.to;

public class LaptopModelCount {
    long laptopModel;
    long qty;
    long ready;
    long partsOnly;
    public LaptopModelCount(long laptopModel, long qty) {...}
    public LaptopModelCount(long laptopModel, long qty, long ready,long partsOnly) {...}
}

score 0 · Accepted Answer · answered Nov 06 '20 at 09:44

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.

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

1 Answers1