How to use Distinct in JPA Repository

Last updated on October 5th, 2024

The ‘Distinct’ keyword is commonly used in SQL to remove duplicates and return unique records. We can use Distinct in the JPA Repository using JPQL, Native SQL query and Distinct with the query method. This topic will teach us how to use Distinct in the JPA Repository using various methods.

Using the DISTINCT keyword in JPA Repository for unique query results

Use Distinct in JPA Repository

Query Method with Distinct Keyword

We can add a custom query method with a distinct keyword in the repository interface. A method starts with the findDistinctBy keyword and then adds a field name after that add the parameter[findDistinctByFieldName(Parameter param)].

Example

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> {
List<OrderDetail> findDistinctByCustomerId(Long customerId);
}

JPQL with Distinct

A custom method with @Query annotation is added to specify a Distinct query using JPQL in the repository interface.

Example

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 DISTINCT o.customerId FROM OrderDetail o")
List<Long> findDistinctCustomerIds();
}

Native SQL Query with Distinct

Adding a custom method with @Query annotation with attribute nativeQuery to specify a Distinct query using Native SQL query in the repository interface.

Example

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 DISTINCT customer_id FROM order_detail", nativeQuery = true)
List<Long> findByDistinctCustomerId();
}

Step-by-step to Guide the Use of Distinct in JPA Repository

Let’s make a Spring Boot Application step-by-step guide to use the Distinct to fetch records from the database table using the JPA Repository. We will create an example to provide a list of unique customers who have placed the order using API endpoints through the methods mentioned above.

These are the following steps:

Create a Spring Boot Project
Setup in the IDE
Configure H2 Database
Create a JPA Entity
Create a Repository Interface
Create a Service Interface
Implement the Service Interface
Create a Controller
Run the Spring Boot Application

1. Create a Spring Boot Project

We are creating a Spring Boot Project using 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 set up and configure the Spring Boot project.

Project Structure of Spring Boot

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

Maven Dependency

Here is the complete maven dependencies file pom.xml for the project which will implement methods to use Distinct in JPA Repository.

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 to generate setter and getter methods, a constructor, etc.

OrderDetail.java

package com.springjava.entity;

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 Long customerId;

  private double amount;

}

5. 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.entity.OrderDetail;

public interface OrderRepository extends JpaRepository < OrderDetail, Long > {

  List < OrderDetail > findDistinctByCustomerId(Long customerId);

  @Query("SELECT DISTINCT o.customerId FROM OrderDetail o")

  List < Long > findDistinctCustomerIds();

  @Query(value = "SELECT DISTINCT customer_id FROM order_detail", nativeQuery = true)

  List < Long > findByDistinctCustomerId();

}

6. 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.entity.OrderDetail;

public interface OrderService {

  void saveAll(List < OrderDetail > orderList);

  List < OrderDetail > getDistinctCustId(Long customerId);

  List < Long > getDistinctCustIds();

  List < Long > getByDistinctCustId();

}

7. 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.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

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<OrderDetail> getDistinctCustId(Long customerId) {

return orderRepo.findDistinctByCustomerId(customerId);

}

@Override

public List<Long> getDistinctCustIds() {

return orderRepo.findDistinctCustomerIds();

}

@Override

public List<Long> getByDistinctCustId() {

return orderRepo.findByDistinctCustomerId();

}

}

8. 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.entity.OrderDetail;

import com.springjava.service.OrderService;

@RestController

@RequestMapping("/api/order")

public class OrderController {

  @Autowired

  private OrderService orderService;

  @PostMapping("/save-all")

  public ResponseEntity < ? > save(@RequestBody List < OrderDetail > orderList) {

    Map < String, Object > respOrder = new LinkedHashMap < String, Object > ();

    orderService.saveAll(orderList);

    respOrder.put("status", 1);

    respOrder.put("message", "Record is Saved Successfully!");

    return new ResponseEntity < > (respOrder, HttpStatus.CREATED);

  }

  @GetMapping("/distinct-with-query-method")

  public ResponseEntity < ? > getDistinctCustomerId() {

    Map < String, Object > respOrder = new LinkedHashMap < String, Object > ();

    List < OrderDetail > OrderList = orderService.getDistinctCustId(1 L);

    if (!OrderList.isEmpty()) {

      respOrder.put("status", 1);

      respOrder.put("data", OrderList);

      return new ResponseEntity < > (respOrder, HttpStatus.OK);

    } else {

      respOrder.clear();

      respOrder.put("status", 0);

      respOrder.put("message", "Data is not found");

      return new ResponseEntity < > (respOrder, HttpStatus.NOT_FOUND);

    }

  }

  @GetMapping("/distinct-with-jpql")

  public ResponseEntity < ? > getDistinctCustomerIds() {

    Map < String, Object > respOrder = new LinkedHashMap < String, Object > ();

    List < Long > custIdList = orderService.getDistinctCustIds();

    if (!custIdList.isEmpty()) {

      respOrder.put("status", 1);

      respOrder.put("data", custIdList);

      return new ResponseEntity < > (respOrder, HttpStatus.OK);

    } else {

      respOrder.clear();

      respOrder.put("status", 0);

      respOrder.put("message", "Data is not found");

      return new ResponseEntity < > (respOrder, HttpStatus.NOT_FOUND);

    }

  }

  @GetMapping("/distinct-with-native")

  public ResponseEntity < ? > getByDistinctCustomerIds() {

    Map < String, Object > respOrder = new LinkedHashMap < String, Object > ();

    List < Long > custIdList = orderService.getDistinctCustIds();

    if (!custIdList.isEmpty()) {

      respOrder.put("status", 1);

      respOrder.put("data", custIdList);

      return new ResponseEntity < > (respOrder, HttpStatus.OK);

    } else {

      respOrder.clear();

      respOrder.put("status", 0);

      respOrder.put("message", "Data is not found");

      return new ResponseEntity < > (respOrder, HttpStatus.NOT_FOUND);

    }

  }

}

9. 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

Checking the H2 database console 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.

[
  {
    "customerId": 1,
    "amount": 700
  },
  {
    "customerId": 1,
    "amount": 800
  },
  {
    "customerId": 2,
    "amount": 900
  },
  {
    "customerId": 3,
    "amount": 800
  }
]

Test the APIs on the Postman Tool

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

GET: http://localhost:8080/api/order/distinct-with-query-method

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

Hibernate: select distinct od1_0.id,od1_0.amount,od1_0.customer_id from order_detail od1_0 where od1_0.customer_id=?

GET: http://localhost:8080/api/order/distinct-with-jpql

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

Hibernate: select distinct od1_0.customer_id from order_detail od1_0

GET: http://localhost:8080/api/order/distinct-with-native

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

Hibernate: select distinct od1_0.customer_id from order_detail od1_0

Conclusion

Distinct is used in the JPA Repository through query method with Distinct keyword, JPQL and Native SQL query.

Use Distinct in JPA Repository

Query Method with Distinct Keyword

JPQL with Distinct

Native SQL Query with Distinct

Step-by-step to Guide the Use of Distinct in JPA Repository

1. Create a Spring Boot Project

2. Setup in the IDE

Project Structure of Spring Boot

Maven Dependency

3. Configure H2 Database

4. Create a JPA Entity

5. Create a Repository Interface

6. Create a Service Interface

7. Implement the Service Interface

8. Create a Controller

9. Run the Spring Boot Application

H2 Database Console

JSON Array

Test the APIs on the Postman Tool

Conclusion

Leave a Comment Cancel reply