How to use Group By in Spring Boot JPA

by

Last updated on October 5th, 2024

We can use group by in Spring Boot JPA application by creating a repository interface that extends the JpaRepository interface. We need to add custom methods annotated with @Query annotation to define the group by query using JPQL and Native SQL query in the repository interface. In this topic, we will know how to use group by in Spring Boot JPA in various ways.

Group By in Spring Boot JPA

Example Use Case

Suppose we have an OrderDetail entity with these properties such as id, customerName, orderAmt and orderDate. We want to group orders by customerName and calculate the total orderAmt for each customer.

Using JQPL for Group By

We can use JPQL for Group By to fetch records from the database table by creating a custom method in the repository interface. This custom method is annotated with @Query annotation and returns the DTO class. 

Step 1: Creating a DTO class

import lombok.AllArgsConstructor;
import lombok.Getter;

@Getter
@AllArgsConstructor
public class OrderSummaryDTO {
private String customerName;
private Double totalOrderAmount;
}

Step 2: Adding a custom method in the repository

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

public interface OrderRepository extends JpaRepository<OrderDetail, Long> {
@Query("SELECT new com.springjava.dto.OrderSummaryDTO(od.customerName, SUM(od.orderAmt))"+ "FROM OrderDetail od GROUP BY od.customerName")
List<OrderSummaryDTO> findTotalOrderAmountCustomer();
}
  • Here return the DTO class using JPQL with Constructor Expression.
  • This is Constructor Expression new com.example.OrderSummaryDTO(o.customerName, SUM(o.orderAmount)) creates an OrderSummaryDTO instance for each group of records.
  • GROUP BY od.customerName groups the records by the customerName field of the JPA Entity class.
  • This function SUM(o.orderAmount) calculates the total order amount for each customer.

Using Native SQL Query for Group By

We can use a Native SQL query for Group By to get records from the database table creating a custom method in the repository interface. This method is annotated with @Query annotation with its attribute nativeQuery.

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

public interface OrderRepository extends JpaRepository<OrderDetail, Long> {
@Query(value = "SELECT customer_name, SUM(order_amt) AS total_order_amount " + "FROM order_detail GROUP BY customer_name", nativeQuery = true)
List<Object[]> findTotalOrderAmountCustomerWithNative();
}

Handling Native Query Results 

We can manually map the result of the above native query for the group by clause used in the repository interface.

@Service
public class OrderServiceImpl implements OrderService {
@Autowired
private OrderRepository orderRepo;

@Override
public List<OrderSummaryDTO> getTotalOrderAmtCustomerWithNative() {
List<Object[]> orderSummary = orderRepo.findTotalOrderAmountCustomerWithNative();
return orderSummary.stream()
.map(result -> new OrderSummaryDTO((String) result[0], ((BigDecimal)result[1]).doubleValue()))
.collect(Collectors.toList());
   }
}
  • Here we used Stream API to handle the result of the native query in the Service class.

Implement Group By in Spring Boot JPA

Let’s make a Spring Boot Application step-by-step guide to use group by clause to fetch records from the database table using JPA Repository. We will create an example to provide order details to the client through the API endpoints mentioned above ways.  

These are the following steps:

  1. Create Spring Boot Project
  2. Setup in the IDE
  3. Configure H2 Database
  4. Create a JPA Entity
  5. Create a DTO class
  6. Create a Repository Interface
  7. Create a Service Interface
  8. Implement the Service Interface
  9. Create a Controller
  10. Run the Spring Boot Application

1. Create Spring Boot Project

We are creating a Spring Boot Project from the web tool Spring Initializr. By Providing details for the project and select the following Maven dependencies:

  • Spring Web
  • Spring Data JPA
  • H2 Database
  • Lombok

2. Setup in the IDE

We use Eclipse IDE to set up and configure the created Spring Boot Project. You can use other IDE to setup and configure the Spring Boot project.

Project Structure of Spring Boot

This image shows the project structure of Spring Boot in Eclipse IDE.

Group By in Spring Boot JPA

Maven Dependency

Here is the complete maven dependencies file pom.xml for the project which will implement methods to use Group By in Spring Boot JPA.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project
	xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.3.0</version>
		<relativePath/>
		<!-- lookup parent from repository -->
	</parent>
	<groupId>com.springjava</groupId>
	<artifactId>demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>demo</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>17</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

3. Configure H2 Database

We are going to configure the H2 database connection in the application.properties file.

application.properties

#H2 Database Configuration
spring.datasource.url=jdbc:h2:mem:test
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=update
spring.h2.console.enabled=true

4. Create a JPA Entity

Let’s create a JPA Entity class. For example, consider an OrderDetail entity and use Lombok for generating setter and getter methods, a constructor, etc.

OrderDetail.java

package com.springjava.entity;

import java.sql.Date;
import org.hibernate.annotations.CurrentTimestamp;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Data;

@Data
@Entity
public class OrderDetail {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String customerName;
private Double orderAmt;
@CurrentTimestamp
private Date orderDate;
}

5. Create a DTO class

We are creating a DTO class and using Lombok annotations @Getter for generating methods and @AllArgsConstructor for generating a constructor with arguments.

OrderSummaryDTO.java

package com.springjava.dto;

import lombok.AllArgsConstructor;
import lombok.Getter;

@Getter
@AllArgsConstructor
public class OrderSummaryDTO {
private String customerName;
private Double totalOrderAmount;
}

6. Create a Repository Interface

Create a repository interface for the OrderDetail JPA Entity class that interface extends the JpaRepository interface to perform persistence operations on the order_detail database table.

OrderRepository.java

package com.springjava.repository;

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import com.springjava.dto.OrderSummaryDTO;
import com.springjava.entity.OrderDetail;

public interface OrderRepository extends JpaRepository<OrderDetail, Long> {
@Query("SELECT new com.springjava.dto.OrderSummaryDTO(od.customerName, SUM(od.orderAmt))"+ "FROM OrderDetail od GROUP BY od.customerName")
List<OrderSummaryDTO> findTotalOrderAmountCustomer();
@Query(value = "SELECT customer_name, SUM(order_amt) AS total_order_amount "
+ "FROM order_detail GROUP BY customer_name", nativeQuery = true)
List<Object[]> findTotalOrderAmountCustomerWithNative();
}

7. Create a Service Interface

Create a Service interface OrderService with some method declaration.

OrderService.java

package com.springjava.service;

import java.util.List;
import com.springjava.dto.OrderSummaryDTO;
import com.springjava.entity.OrderDetail;

public interface OrderService {
void saveAll(List<OrderDetail> orderList);
List<OrderSummaryDTO> getTotalOrderAmtCustomerWithJPQL();
List<OrderSummaryDTO> getTotalOrderAmtCustomerWithNative();
}

8. Implement the Service Interface

Implement the OrderService interface in the OrderServiceImpl class. This class is annotated with @Service annotation, where we inject OrderRepository to call all its methods.

OrderServiceImpl.java

package com.springjava.service;

import java.math.BigDecimal;
import java.util.List;
import java.util.stream.Collectors;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.springjava.dto.OrderSummaryDTO;
import com.springjava.entity.OrderDetail;
import com.springjava.repository.OrderRepository;

@Service
public class OrderServiceImpl implements OrderService {
@Autowired
private OrderRepository orderRepo;

@Override
public void saveAll(List<OrderDetail> orderList) {
orderRepo.saveAll(orderList);
}

@Override
public List<OrderSummaryDTO> getTotalOrderAmtCustomerWithJPQL() {
return orderRepo.findTotalOrderAmountCustomer();
}

@Override
public List<OrderSummaryDTO> getTotalOrderAmtCustomerWithNative() {
List<Object[]> orderSummary = orderRepo.findTotalOrderAmountCustomerWithNative();
return orderSummary.stream()
.map(result -> new OrderSummaryDTO((String) result[0], ((BigDecimal) result[1]).doubleValue()))
.collect(Collectors.toList());
    }
}

9. Create a Controller

Create a controller class OrderController. This is annotated with @RestController to make this class a RestController.

OrderController.java

package com.springjava.controller;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.springjava.dto.WebSeriesDTO;
import com.springjava.dto.WebSeriesDTOI;
import com.springjava.entity.WebSeries;
import com.springjava.service.WebSeriesService;


@RestController
@RequestMapping("/api/webseries")
public class WebSeriesController {
	@Autowired
	private WebSeriesService webSeriesService;
		@PostMapping("/save-all")
		public ResponseEntity<?> save(@RequestBody List<WebSeries> listWebSeries) {
			Map<String, Object> respWebSeries = new LinkedHashMap<String, Object>();
			webSeriesService.saveAll(listWebSeries);
			respWebSeries.put("status", 1);
			respWebSeries.put("message", "Record is Saved Successfully!");
			return new ResponseEntity<>(respWebSeries, HttpStatus.CREATED);
		}
		
		@GetMapping("/dto-with-jpql")
		public ResponseEntity<?> getAllWebSeriesDTO() {
			Map<String, Object> respWebSeries = new LinkedHashMap<String, Object>();
			List<WebSeriesDTO> webSeriesDTOList=webSeriesService.getAllWebSeriesDTO();
			if(!webSeriesDTOList.isEmpty()) {
			respWebSeries.put("status", 1);
			respWebSeries.put("data", webSeriesDTOList);
			return new ResponseEntity<>(respWebSeries, HttpStatus.OK);
			}else {
				respWebSeries.clear();
				respWebSeries.put("status", 0);
				respWebSeries.put("message", "Data is not found");
				return new ResponseEntity<>(respWebSeries, HttpStatus.NOT_FOUND);
			}
		}
		
		@GetMapping("/dto-with-projection")
		public ResponseEntity<?> getAllWebSeriesDTOI() {
			Map<String, Object> respWebSeries = new LinkedHashMap<String, Object>();
			List<WebSeriesDTOI> webSeriesDTOList=webSeriesService.getAllWebSeriesDTOI();
			if(!webSeriesDTOList.isEmpty()) {
			respWebSeries.put("status", 1);
			respWebSeries.put("data", webSeriesDTOList);
			return new ResponseEntity<>(respWebSeries, HttpStatus.OK);
			}else {
				respWebSeries.clear();
				respWebSeries.put("status", 0);
				respWebSeries.put("message", "Data is not found");
				return new ResponseEntity<>(respWebSeries, HttpStatus.NOT_FOUND);
			}
		}
		
		@GetMapping("/dto-with-native-class-based")
		public ResponseEntity<?> getAllClassBased() {
			Map<String, Object> respWebSeries = new LinkedHashMap<String, Object>();
			List<WebSeriesDTO> webSeriesDTOList=webSeriesService.getAllWebSeriesDTO();
			if(!webSeriesDTOList.isEmpty()) {
			respWebSeries.put("status", 1);
			respWebSeries.put("data", webSeriesDTOList);
			return new ResponseEntity<>(respWebSeries, HttpStatus.OK);
			}else {
				respWebSeries.clear();
				respWebSeries.put("status", 0);
				respWebSeries.put("message", "Data is not found");
				return new ResponseEntity<>(respWebSeries, HttpStatus.NOT_FOUND);
			}
		}
		
		@GetMapping("/dto-with-native-interface-based")
		public ResponseEntity<?> getAllInterfaceBased() {
			Map<String, Object> respWebSeries = new LinkedHashMap<String, Object>();
			List<WebSeriesDTOI> webSeriesDTOList=webSeriesService.getAllWithInterfaceBased();
			if(!webSeriesDTOList.isEmpty()) {
			respWebSeries.put("status", 1);
			respWebSeries.put("data", webSeriesDTOList);
			return new ResponseEntity<>(respWebSeries, HttpStatus.OK);
			}else {
				respWebSeries.clear();
				respWebSeries.put("status", 0);
				respWebSeries.put("message", "Data is not found");
				return new ResponseEntity<>(respWebSeries, HttpStatus.NOT_FOUND);
			}
		}

}

10. Run the Spring Boot Application

Right-click this Spring Boot application on the DemoApplication.java, then click Run As and select Java Application.

H2 Database Console

If we want to check the H2 database console then we need to browse this URL “http://localhost:8080/h2-console”  on the browser.  

JSON Array

We are creating a sample JSON Array to test the API http://localhost:8080/api/order/save-all.

[
  {
    "customerName": "Demo",
    "orderAmt": 900
  },
  {
    "customerName": "Test",
    "orderAmt": 700
  },
  {
    "customerName": "Demo",
    "orderAmt": 500
  }
]

Test the APIs on the Postman Tool

POST: http://localhost:8080/api/order/save-all

Group By in Spring Boot JPA

GET: http://localhost:8080/api/order/group-by-jpql

Group By in Spring Boot JPA

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: select od1_0.customer_name,sum(od1_0.order_amt) from order_detail od1_0 group by od1_0.customer_name

GET: http://localhost:8080/api/order/group-by-native

Group By in Spring Boot JPA

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: SELECT customer_name, SUM(order_amt) AS total_order_amount FROM order_detail GROUP BY customer_name

Conclusion

Group By clause is used in Spring Boot JPA  through JPQL and Native SQL query in the JPA Repository.

Leave a Comment