How to write delete query in JPA Repository

by

Last updated on July 13th, 2024

We can write delete query in the JPA Repository by adding custom methods, which must be annotated with @Query annotation. This @Query annotation helps to define queries through JPQL and Native SQL queries. In this topic, we will learn how to write a delete query in the JPA Repository using the Spring Boot application.

write_delete_query

Using @Query with JPQL

Using @Query with JPQL we need to create a custom method in the repository interface that extends the JpaRepository interface. The created custom method must be annotated with @Query, @Modifying and @Transactional annotations.

import org.springframework.data.jpa.repository.JpaRepository;

import org.springframework.data.jpa.repository.Modifying;

import org.springframework.data.jpa.repository.Query;

import com.springjava.entity.User;

import jakarta.transaction.Transactional;

public interface UserRepository extends JpaRepository < User, Long > {

  @Modifying

  @Transactional

  @Query("DELETE FROM User us WHERE us.email = :email")

  int userDeleteByEmail(String email);

}
  • @Query annotation is used to define delete query with JPQL.
  • @Modifying annotation is used to that query modification operation(INSERT, UPDATE, DELETE).
  • @Transactional annotation helps to execute the delete operation within the transaction.

Using @Query with Native SQL Query

To write a delete query in the JPA Repository using @Query with Native SQL query we need to create a custom method in the repository interface. This method is annotated with @Modifying and@Transactional annotations and @Query annotation with nativeQuery= true attribute.

import org.springframework.data.jpa.repository.JpaRepository;

import org.springframework.data.jpa.repository.Modifying;

import org.springframework.data.jpa.repository.Query;

import com.springjava.entity.User;

import jakarta.transaction.Transactional;

public interface UserRepository extends JpaRepository < User, Long > {

  @Transactional

  @Modifying

  @Query(value = "DELETE FROM user_tbl WHERE active = :active", nativeQuery = true)

  int userDeleteByActive(boolean active);

}

Practical Example with Implementation 

Let’s make a Spring Boot Application step-by-step to complete the example demonstrating how to write a delete query in the JPA Repository using the @Query annotation mentioned above. 

These are the following steps: 

  1. Create a Spring Boot Starter Project
  2. Keep the IDE ready
  3. Maven Dependency
  4. Configure H2 Database
  5. Create a JPA Entity
  6. Create a JPA Repository
  7. Implement the Service 
  8. Create a Controller 
  9. Run the Spring Boot Application

1.  Create a Spring Boot Starter Project

 We are creating a Spring Boot Application from the web tool Spring Initializr or you can make it from the IDE(STS, VS Code etc.). 

Add the following dependencies: 

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

2. Keep the IDE ready

We are importing this created application into our Eclipse IDE’s workspace or you can import it into another IDE you use.

Project Structure of Write Delete Query

3. Maven Dependency

Here is the complete pom.xml file for the Spring Boot Application(write-query-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>

4. Configure H2 Database

We are configuring the H2 database configuration 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

5. Create a JPA Entity 

We are creating a JPA Entity class User with properties(id, name, email and active) to create a table in the database.

User.java

package com.springjava.entity;

import jakarta.persistence.Entity;

import jakarta.persistence.GeneratedValue;

import jakarta.persistence.GenerationType;

import jakarta.persistence.Id;

import jakarta.persistence.Table;

import lombok.Data;

@Data

@Entity

@Table(name = "user_tbl")

public class User {

  @Id

  @GeneratedValue(strategy = GenerationType.IDENTITY)

  private Long id;

  private String name;

  private String email;

  private boolean active;

}

➔ This @Data annotation is used for a constructor, setter method, getter method, etc.
➔ This @Entity annotation is used to create a table through Java code in the database. 
➔ This @Table annotation is used to assign the name of the table which will be created in the database.
➔ This @Id annotation is used to create a primary key of the entity class.
➔ This @GeneratedValue(strategy = GenerationType.IDENTITY) annotation is used to generate increment value of the specified property of entity class

6. Create a JPA Repository

We are creating a repository interface UserRepository for the User entity class. Adding some custom methods with @Modifying, @Transactional and @Query annotations.

UserRepository.java

package com.springjava.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import org.springframework.data.jpa.repository.Modifying;

import org.springframework.data.jpa.repository.Query;

import com.springjava.entity.User;

import jakarta.transaction.Transactional;

public interface UserRepository extends JpaRepository < User, Long > {

  @Transactional

  @Modifying

  @Query("DELETE FROM User us WHERE us.email = :email")

  int userDeleteByEmail(String email);

  @Transactional

  @Modifying

  @Query(value = "DELETE FROM user_tbl WHERE active = :active", nativeQuery = true)

  int userDeleteByActive(boolean active);

}

7. Implement the Service

We are creating a Service Interface UserService to declare the methods.

UserService.java

package com.springjava.service;

import java.util.List;

import com.springjava.entity.User;

public interface UserService {

  void saveAll(List < User > userList);

  int userDeleteByEmail(String email);

  int userDeleteByActive(boolean active);

}

We are creating the Service class UserServiceImpl to implement the UserService interface and provide the implementation of its methods. This class is annotated with @Service annotation provides business functionality for this application. Injecting the UserRepository in the class to use its method for executing delete query in the database table.

UserServiceImpl.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.User;

import com.springjava.repository.UserRepository;

@Service

public class UserServiceImpl implements UserService {

  @Autowired

  private UserRepository userRepo;

  @Override

  public void saveAll(List < User > userList) {

    userRepo.saveAll(userList);

  }

  @Override

  public int userDeleteByEmail(String email) {

    return userRepo.userDeleteByEmail(email);

  }

  @Override

  public int userDeleteByActive(boolean active) {

    return userRepo.userDeleteByActive(active);

  }

}

8. Create a Controller class

We are creating a Controller class UserController to create API endpoint methods that handle requests from the client.

UserController.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.DeleteMapping;

import org.springframework.web.bind.annotation.PathVariable;

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.User;

import com.springjava.service.UserService;

@RestController

@RequestMapping("/api/user")

public class UserController {

  @Autowired

  private UserService userService;

  @PostMapping("/save-all")

  public ResponseEntity < ? > save(@RequestBody List < User > users) {

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

    userService.saveAll(users);

    respUser.put("status", 1);

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

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

  }

  @DeleteMapping("/delete-by-email/{email}")

  public ResponseEntity < ? > deleteByEmail(@PathVariable String email) {

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

    int count = userService.userDeleteByEmail(email);

    respUser.put("status", 1);

    respUser.put("data", count + " Record is deleted successfully!");

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

  }

  @DeleteMapping("/delete-by-active/{active}")

  public ResponseEntity < ? > deleteByEmail(@PathVariable boolean active) {

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

    int count = userService.userDeleteByActive(active);

    respUser.put("status", 1);

    respUser.put("data", count + " Records are deleted successfully!");

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

  }

}

➔ This class is annotated with @RestController annotation to make this class act as a RestController for giving responses in JSON form.
➔ This @RequestMapping annotation to define the base URL for the application.
➔ These @PostMapping and @DeleteMapping annotations to handle HTTP requests from the client.
➔ This ResponseEntity is used to represent the entire HTTP response.
➔ This @Autowired  annotation is used to inject UserService in the class.
➔ This @RequestBody annotation takes a JSON array in the save() method as the List of User class parameter.

10. Run the Spring Boot Application and Check

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/user/save-all.

[
  {
    "name": "Test",
    "email": "test@gmail.com",
    "active": true
  },
  {
    "name": "ABC",
    "email": "abc@gmail.com",
    "active": true
  },
  {
    "name": "DEF",
    "email": "def@gmail.com",
    "active": true
  },
  {
    "name": "XYZ",
    "email": "xyz@gmail.com",
    "active": true
  },
  {
    "name": "PQR",
    "email": "pqr@gmail.com",
    "active": true
  },
  {
    "name": "JKL",
    "email": "jkl@gmail.com",
    "active": true
  },
  {
    "name": "MNO",
    "email": "mno@gmail.com",
    "active": false
  }
]

Test the APIs on the Postman Tool

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

write_delete_query

DELETE: http://localhost:8080/api/user/delete-by-email/xyz@gmail.com

write_delete_query

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

Hibernate: delete from user_tbl u1_0 where u1_0.email=?

DELETE: http://localhost:8080/api/user/delete-by-active/true

write_delete_query

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

Hibernate: DELETE FROM user_tbl WHERE active = ?

Conclusion

In this topic, we learnt various ways to write a delete query in the JPA Repository and implement it with Spring Boot Application, demonstrating how to write a delete query in the JPA Repository using @Query.

Leave a Comment