How to Delete All Records from Table using JPA Repository

Deleting all records from the database table is a common requirement. This is used in various scenarios like testing, data cleanup and resetting the application states. Using JPA Repository delete all records from the database table through Query Methods, JPQL and Native SQL queries. In this topic, we will know how to delete all records from a table using JPA Repository with Spring Boot application using various methods.

Methods to Delete All Records

1. Using Query Methods

These query methods (deleteAll() and deleteAllInBatch()) are used to delete all records from the entity table.

deleteAll(): This method first selects all the records and then executes the delete queries.
deleteAllInBatch(): This method is used to delete records from the table in a batch call by executing a single query in the database.

Example

public interface TaskRepository extends JpaRepository<Task, Long> {
}

We need to create a Service class to use these methods.

@Service

public class TaskServiceImpl implements TaskService {

  @Autowired

  private TaskRepository taskRepo;

  @Override

  public void deleteAll() {

    taskRepo.deleteAll();

  }

  @Override

  public void deleteTaskBatch() {

    taskRepo.deleteAllInBatch();

  }

}

2. Using JPQL Query

We need to add a custom method annotated with @Query, @Modifying and @Transactional annotations in the repository interface that extends the JpaRepository interface of the Spring Data JPA. The JQPL delete query to delete all records in a single delete query. It is

Example

public interface TaskRepository extends JpaRepository<Task, Long> {

@Transactional
@Modifying
@Query("DELETE FROM Task")
void deleteAllTasks();
}

3. Using Native SQL Queries

Using Native SQL queries to delete all records from the table we need to add custom methods in the repository interface. These added methods are annotated with @Modifying and @Transactional annotations and @Query annotation with nativeQuery=true attribute. Using Native SQL queries to delete larger datasets can offer the best performance by directly interaction with the database and bypassing JPA.

Example

public interface TaskRepository extends JpaRepository < Task, Long > {

  @Transactional

  @Modifying

  @Query(value = "DELETE FROM task", nativeQuery = true)

  void deleteTasks();

  @Modifying

  @Transactional

  @Query(value = "TRUNCATE TABLE task", nativeQuery = true)

  void truncateAllTasks();

}

Step-by-Step Guide to Delete All Records

Let’s make a Spring Boot Application step-by-step guide to delete all records from the table in the JPA Repository. We will create an example for this several tasks are inserted into the database and then we are going to delete all records through the methods that are mentioned above.

These are the following steps:

Create 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 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 such as STS, IntelliJ IDEA, etc.

Project Structure of Spring Boot

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

Maven Dependency

Here is the complete maven dependencies file pom.xml for the project which will implement methods to delete all records.

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 a Task entity and use Lombok for generating setter and getter methods, a constructor, etc.

Task.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 Task {

  @Id

  @GeneratedValue(strategy = GenerationType.IDENTITY)

  private Long id;

  private String name;

  private String desc;

}

5. Create a Repository Interface

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

TaskRepository.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.Task;

import jakarta.transaction.Transactional;

public interface TaskRepository extends JpaRepository < Task, Long > {

  @Transactional

  @Modifying

  @Query("DELETE FROM Task")

  void deleteAllTasks();

  @Transactional

  @Modifying

  @Query(value = "DELETE FROM task", nativeQuery = true)

  void deleteTasks();

  @Modifying

  @Transactional

  @Query(value = "TRUNCATE TABLE task", nativeQuery = true)

  void truncateAllTasks();

}

@Modifying annotation allows us to modify queries and increase the capability of @Query annotation.
@Query annotation allows us to define the query using JPQL and Native SQL queries.
@Transactional annotation allows us to execute a query within the transaction.

6. Create a Service Interface

Create a Service interface TaskService with some method declaration.

TaskService.java

package com.springjava.service;

import java.util.List;

import com.springjava.entity.Task;

public interface TaskService {

  void saveAll(List < Task > listTask);

  void deleteAll();

  void deleteTaskBatch();

  void deleteAllTasks();

  void deleteTasks();

  void trancateAllTasks();

}

7. Implement the Service Interface

Implement the TaskService interface in the TaskServiceImpl class. This class is annotated with @Service annotation.

TaskServiceImpl.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.Task;

import com.springjava.repository.TaskRepository;

@Service

public class TaskServiceImpl implements TaskService {

  @Autowired

  private TaskRepository taskRepo;

  @Override

  public void saveAll(List < Task > listTask) {

    taskRepo.saveAll(listTask);

  }

  @Override

  public void deleteAll() {

    taskRepo.deleteAll();

  }

  @Override

  public void deleteTaskBatch() {

    taskRepo.deleteAllInBatch();

  }

  @Override

  public void deleteAllTasks() {

    taskRepo.deleteAllTasks();

  }

  @Override

  public void deleteTasks() {

    taskRepo.deleteTasks();

  }

  @Override

  public void trancateAllTasks() {

    taskRepo.truncateAllTasks();

  }

}

8. Create a Controller

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

TaskController.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.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.Task;

import com.springjava.service.TaskService;

@RestController

@RequestMapping("/api/task")

public class TaskController {

  @Autowired

  private TaskService taskService;

  @PostMapping("/save-all")

  public ResponseEntity < ? > save(@RequestBody List < Task > tasks) {

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

    taskService.saveAll(tasks);

    respTask.put("status", 1);

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

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

  }

  @DeleteMapping("/delete-all")

  public ResponseEntity < ? > deleteAll() {

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

    taskService.deleteAll();

    respTask.put("status", 1);

    respTask.put("data", "Record deleted successfully!");

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

  }

  @DeleteMapping("/delete-all-batch")

  public ResponseEntity < ? > deleteAllTaskBatch() {

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

    taskService.deleteTaskBatch();

    respTask.put("status", 1);

    respTask.put("data", "Record deleted successfully!");

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

  }

  @DeleteMapping("/delete-all-tasks")

  public ResponseEntity < ? > deleteAllTasks() {

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

    taskService.deleteAllTasks();

    respTask.put("status", 1);

    respTask.put("data", "Record deleted successfully!");

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

  }

  @DeleteMapping("/delete-tasks")

  public ResponseEntity < ? > deleteTasks() {

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

    taskService.deleteTasks();

    respTask.put("status", 1);

    respTask.put("data", "Record deleted successfully!");

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

  }

  @DeleteMapping("/truncate-tasks")

  public ResponseEntity < ? > truncateTasks() {

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

    taskService.trancateAllTasks();

    respTask.put("status", 1);

    respTask.put("data", "Record deleted successfully!");

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

  }

}

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

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

[
  {
    "name": "Test",
    "desc": "Some Details"
  },
  {
    "name": "ABC",
    "desc": "Some Details"
  },
  {
    "name": "XYZ",
    "desc": "Some Details"
  }
]

Test the APIs on the Postman Tool

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