4 ways to use group by in Spring Data JPA

@Entity
@IdClass(ReportId.class)
@Table(name = "report")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Report {
    private Integer year;
    private Integer month;
    private BigDecimal loanSmall;
    private BigDecimal loanMajor;
    private BigDecimal loanTotal;
Response Model
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class YearReportSum {
    private Integer year;
    private BigDecimal smallSum;
    private BigDecimal majorSum;
    private BigDecimal totalSum;
1. Using Java Stream

@Service
@Slf4j
@RequiredArgsConstructor
public class ReportSimpleService implements ReportService {
    private final ReportRepository reportRepository;
    @Transactional
    public List<YearReportSum> getReportByStream() {
        return
            reportRepository.findAll()
                            .parallelStream()
                            .map(report -> new YearReportSum(report.getYear(), report.getLoanSmall(), report.getLoanMajor(), report.getLoanTotal()))
                            .collect(
                                Collectors.toMap(
                                    sum -> sum.getYear(),
                                    Function.identity(),
                                    (sum1, sum2) -> new YearReportSum(
                                                            sum1.getYear(),
                                                            sum1.getSmallSum().add(sum2.getSmallSum()),
                                                            sum1.getMajorSum().add(sum2.getMajorSum()),
                                                            sum1.getTotalSum().add(sum2.getTotalSum())
                            .values()
                            .stream()
                            .sorted(Comparator.comparing(YearReportSum::getYear))
                            .collect(Collectors.toList())
Easy to understand, but slower as there is more data
2. Using JPQL with Projection

@Repository
public interface ReportRepository extends JpaRepository<Report, ReportId> {
    @Query(value =
        "SELECT "+
        " new moon.odyssey.entity.YearReportSum(rp.year, SUM(rp.loanSmall), SUM (rp.loanMajor), SUM (rp.loanTotal))" +
        "FROM Report rp " +
        "GROUP BY rp.year"
    List<YearReportSum> findGroupByReportWithJPQL();
Guaranteed type stability, but long query
3. Using NativeQuery

Declare Interface based Projection
public interface YearReportSumInterface {
    Integer getYear();
    BigDecimal getSmallSum();
    BigDecimal getMajorSum();
    BigDecimal getTotalSum();
Using Native Query
@Repository
public interface ReportRepository extends JpaRepository<Report, ReportId> {
    @Query(value =
        "SELECT "+
            " rp.year AS year " +
            ", SUM(rp.loan_small) AS smallSum " +
            ", SUM(rp.loan_major) AS majorSum " +
            ", SUM(rp.loan_total) AS totalSum " +
            "FROM report rp " +
            "GROUP BY rp.year"
        , nativeQuery = true
    List<YearReportSumInterface> findGroupByReportWithNativeQuery();
Native query that are easy to verify, but need to convert to another DTO
4. Using QueryDSL

@Service
@Slf4j
@RequiredArgsConstructor
public class ReportSimpleService implements ReportService {
    @PersistenceContext
    private EntityManager entityManager;
    @Transactional
    public List<YearReportSum> getReportByQueryDSL() {
        QReport qReport = QReport.report;
        JPAQueryFactory qf = new JPAQueryFactory(entityManager);
        JPAQuery<YearReportSum> query = qf.from(qReport)
                                       .groupBy(qReport.year)
                                       .select(
                                           Projections.bean(
                                               YearReportSum.class,
                                               qReport.year,
                                               qReport.loanSmall.sum().as("smallSum"),
                                               qReport.loanMajor.sum().as("majorSum"),
                                               qReport.loanTotal.sum().as("totalSum")
        return query.fetch();
Easy to extend, but direct using EntityManager
Appendix

Docker run
~] cd docker
~] docker-compose up -d
Docker down
~] cd docker
~] docker-compose down