Close

Spring Data JPA - Derived Delete Queries

[Updated: Aug 21, 2018, Created: Aug 14, 2018]

Similar to Derived Query methods for Select, Spring Data JPA also supports Delete Derived Query methods. That means we can use all keywords reserved for query method names as listed here, but instead of starting a method name with 'find' we need to start it with 'delete' or 'remove' keyword. For example:

   @Transactional
   @Modifying
   int deleteByNameAndDept(String name, String dept);

As seen above we also need to use @Modifying and @Transactional annotations on the derived delete query methods.

Also following delete methods are already defined in CrudRepository interface:

   void deleteById(ID var1);
   void delete(T var1);
   void deleteAll(Iterable<? extends T> var1);
   void deleteAll();

Example

Entity

@Entity
public class Employee {
  private @Id
  @GeneratedValue
  Long id;
  private String name;
  private String dept;
  private int salary;
    .............
}

Repository

package com.logicbig.example;

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.repository.CrudRepository;
import org.springframework.transaction.annotation.Transactional;

public interface EmployeeRepository extends CrudRepository<Employee, Long> {

  @Transactional
  @Modifying
  int deleteByNameAndDept(String name, String dept);

  @Transactional
  @Modifying
  int deleteBySalaryGreaterThanEqual(int salary);
}

Example Client

@Component
public class ExampleClient {

  @Autowired
  private EmployeeRepository repo;

  public void run() {
      List<Employee> employees = createEmployees();
      repo.saveAll(employees);

      System.out.println("-- all employees --");
      repo.findAll().forEach(System.out::println);

      System.out.println(" -- Delete employee Charlie in IT dept --");
      int updateCount = repo.deleteByNameAndDept("Charlie", "IT");
      System.out.println("Update count: " + updateCount);

      System.out.println("-- all employees after delete --");
      repo.findAll().forEach(System.out::println);

      System.out.println(" -- Delete all employees with salaries greater than 3000 inclusively --");
      updateCount = repo.deleteBySalaryGreaterThanEqual(3000);
      System.out.println("Update count: " + updateCount);

      System.out.println("-- all employees after delete --");
      repo.findAll().forEach(System.out::println);
 }

  private List<Employee> createEmployees() {
      return Arrays.asList(
              Employee.create("Diana", "Sales", 2000),
              Employee.create("Rose", "IT", 4000),
              Employee.create("Charlie", "Sales", 3000),
              Employee.create("Charlie", "IT", 2500)
      );
  }
}

Main class

public class ExampleMain {

  public static void main(String[] args) {
      AnnotationConfigApplicationContext context =
              new AnnotationConfigApplicationContext(AppConfig.class);
      ExampleClient exampleClient = context.getBean(ExampleClient.class);
      exampleClient.run();
      EntityManagerFactory emf = context.getBean(EntityManagerFactory.class);
      emf.close();
  }
}
-- all employees --
Employee{id=1, name='Diana', dept='Sales', salary=2000}
Employee{id=2, name='Rose', dept='IT', salary=4000}
Employee{id=3, name='Charlie', dept='Sales', salary=3000}
Employee{id=4, name='Charlie', dept='IT', salary=2500}
-- Delete employee Charlie in IT dept --
Update count: 1
-- all employees after delete --
Employee{id=1, name='Diana', dept='Sales', salary=2000}
Employee{id=2, name='Rose', dept='IT', salary=4000}
Employee{id=3, name='Charlie', dept='Sales', salary=3000}
-- Delete all employees with salaries greater than 3000 inclusively --
Update count: 2
-- all employees after delete --
Employee{id=1, name='Diana', dept='Sales', salary=2000}

Example Project

Dependencies and Technologies Used:

  • spring-data-jpa 2.0.9.RELEASE: Spring Data module for JPA repositories.
    Uses org.springframework:spring-context version 5.0.8.RELEASE
  • hibernate-core 5.3.5.Final: Hibernate's core ORM functionality.
    Implements javax.persistence:javax.persistence-api version 2.2
  • h2 1.4.197: H2 Database Engine.
  • JDK 1.8
  • Maven 3.5.4

Derived Delete Queries Select All Download
  • spring-data-jpa-derived-delete-queries
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • EmployeeRepository.java
          • resources
            • META-INF

    See Also