JPQL Query using Distinct condition




Hey guys in this article, you will learn how to write a JPQL for the Distinct condition in the Spring Data JPA repository.

JPQL stands for Java persistence query language defined in JPA specification. It is used to create queries against entities to store in a relational database. JPQL is developed based on SQL syntax. Behind the scenes Data JPA convert these JPQL queries into native SQL queries.

Scenario: Consider the Employee entity class and if we want to retrieve distinct employee names then here is the Spring data JPA JPQL query:

@Query("SELECT DISTINCT e.name FROM Employee e")
List getDistinctEmployeesByName();
  • @Query annotation to write the JPQL query. We use field names instead of column names and Entity name instead of table name.

Complete Example


We will create this example step by step, follow this tutorial till the end

Read More:

Create database and insert sample data


Open MySQL workbench and execute the following commands

CREATE DATABASE demodb;

USE demodb;

SELECT * FROM demodb.tbl_employees;

INSERT INTO tbl_employees
(age, department, location, name, salary) 
VALUES
(28, "IT", "India", "Bushan", 20000.00);

INSERT INTO tbl_employees
(age, department, location, name, salary) 
VALUES
(28, "Sales", "India", "Bushan", 20000.00);

INSERT INTO tbl_employees
(age, department, location, name, salary) 
VALUES
(28, "Marketing", "India", "Bushan", 20000.00);

INSERT INTO tbl_employees
(age, department, location, name, salary) 
VALUES
(28, "IT", "India", "Bharath", 20000.00);

INSERT INTO tbl_employees
(age, department, location, name, salary) 
VALUES
(28, "IT", "India", "Chethan", 20000.00);

We have created the table tbl_employees. We have inserted the sample data as well.

Create spring boot project


There are many different ways to create a spring boot application, you can follow the below articles to create one –



>> Create spring boot application using Spring initializer
>> Create spring boot application in Spring tool suite [STS]
>> Create spring boot application in IntelliJ IDEA

Add maven dependencies


Open pom.xml and add the following dependencies –

<?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>2.5.4</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>in.bushansirgur</groupId>
	<artifactId>springbootdemo</artifactId>
	<version>1.0.0</version>
	<name>springbootdemo</name>
	<description>Spring boot demo</description>
	<properties>
		<java.version>11</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

Configure the datasource


Open application.properties file and add the following contents

spring.datasource.url=jdbc:mysql://localhost:3306/demodb
spring.datasource.username=root
spring.datasource.password=root

spring.jpa.hibernate.ddl-auto=update

Create entity class


Create Employee.java class in.bushansirgur.springsecurityjwt.entity package and add the following content

package in.bushansirgur.springbootdemo.entity;

import java.math.BigDecimal;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Entity
@Table(name = "tbl_employees")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee {
	
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;
	
	private String name;
	
	private String location;
	
	private String department;
	
	private Long age;
	
	private BigDecimal salary;
}

Create Repository class


Create UserRepository.java class in.bushansirgur.springsecurityjwt.repository package and add the following content

package in.bushansirgur.springbootdemo.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import in.bushansirgur.springbootdemo.entity.Employee;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long>{
	
	@Query("SELECT DISTINCT e.name FROM Employee e")
	List<String> getDistinctEmployeesByName();
	
}

Call repository method from main() method


In order to call the repository method that we have created, we gonna use CommandLineRunner.run() method to call the repository method. Once the application starts, spring boot will execute the following code –

package in.bushansirgur.springbootdemo;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import in.bushansirgur.springbootdemo.repository.EmployeeRepository;

@SpringBootApplication
public class SpringbootdemoApplication implements CommandLineRunner{

	@Autowired
	private EmployeeRepository employeeRepository;
	
	public static void main(String[] args) {
		SpringApplication.run(SpringbootdemoApplication.class, args);
	}

	@Override
	public void run(String... args) throws Exception {
		
		List<String> list = employeeRepository.getDistinctEmployeesByName();
		System.out.println("----------------List of Employees---------------------");
		list.forEach(e -> {
			System.out.println(e); 
		});
		
	}

}

Run the app


Run the application using the below maven command –

mvn spring-boot:run

Once Spring boot application execution completes, you can able to see generated SQL statements in a console and the distinct values

Hibernate: 
    select
        distinct employee0_.name as col_0_0_ 
    from
        tbl_employees employee0_
----------------List of Employees---------------------
Bushan
Bharath
Chethan

References


That’s it for this post, if you like this post, share this with your friends and colleagues or you can share this within your social media platform. Thanks, I will see you in our next post.




Bushan Sirgur

Hey guys, I am Bushan Sirgur from Banglore, India. Currently, I am working as an Associate project in an IT company.

Leave a Reply