Spring Data JPA delete query

1. Overview

This article is about to delete query in Spring Data JPA or we can say how to delete records using spring JPA in SQL as well as No-SQL database. There are multiple to ways the query to delete records from the database, We have explained here delete using Derivation Mechanism, @Query annotation, @Query with nativeQuery as well and dynamically generate a query to delete the records.

2. Delete using Query Derivation Mechanism and Default Method

Spring JPA CrudRepository provides a default method to delete the records. Here are methods which are provided by CrudRepository.

• delete(T entity) deleting a record by Entry
• delete(ID id) delete a record by Primary Key ID.
• deleteAll() delete all records
• delete(Iterable<? extends T> iterable) delete multiple records by Iterable like List or any other.

For the default methods no need to mark @Query or @Modifying annotation at the method level.

package com.javadeveloperzone.dao;
import com.javadeveloperzone.model.Employee;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

/**
 * Created by Java Developer Zone on 03-08-2017.
 */
@Repository
@Transactional
public interface EmployeeRepository extends CrudRepository<Employee,Long>{
    @Override
    void delete(Long empId);
    @Override
    void delete(Iterable<? extends Employee> iterable);    // delete multiple records by List or any other Iterable 
    @Override
    void deleteAll();       // to Delete all records
    int deleteEmployeeByEmployeeName(String name);  // delete by employee name, Query Derivation Mechanism 
    int deleteEmployeeByEmployeeNameEndingWith(String nameEndsWith); // Query Derivation Mechanism, for name ends with
     int deleteByEmployeeIdGreaterThan(int id);      // delete employee which are greater then defined id
}

Here is a list of Keywords which are supported by Spring JPA Query Derivation Mechanism. Reference from Spring Data JPA documentation

3. Delete Operation using Custom Query

We can also write our custom query to delete the records. @Query is annotation using that we can write our custom query to execute the operation in the database but when we want to perform delete or update operation at the time @Modifying must be required otherwise it will generate exception call “org.hibernate.hql.internal.QueryExecutionRequestException: Not supported for DML operations“. Means that for Data Manipulation Operations we need to mark  @Modifying at the method level. Let’s see some examples so that we get more idea:

package com.javadeveloperzone.dao;
import com.javadeveloperzone.model.Employee;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

/**
 * Created by Java Developer Zone on 03-08-2017.
 */
@Repository
@Transactional
public interface EmployeeRepository extends CrudRepository<Employee,Long>{

    @Modifying      // to mark delete or update query
    @Query(value = "DELETE FROM Employee e WHERE e.employeeName = :name")       // it will delete all the record with specific name
    int deleteByName(@Param("name") String name);
    @Modifying 
    @Query(value = "DELETE FROM Employee WHERE employeeName = :name",nativeQuery = true) // if want to write nativequery then mask nativeQuery  as true
    int deleteByNameNative(@Param("name") String name); 
}

4. Generate Dynamic Delete query

Here we have used a dynamic query for fetch the records dynamically and after that delete those records. I like to inform that this is not preferable to delete records dynamically, But as of now, Spring JPA does not support generating a dyamic query for delete operation.

3.1EmployeeRepository.java

Here need to extend JpaSpecificationExecutor interface which provides functionality to generate a dynamic query in spring JPA. If you are new for generating run time query in spring JPA then here is an article for how to generate a run-time query in spring JPA.

@Repository
@Transactional
public interface EmployeeRepository extends CrudRepository<Employee,Long>, JpaSpecificationExecutor<Employee> {
}

3.2ApplicationRunner

Here we have fetched all the records with specified criteria and delete those records using delete(Iterable<? extends T> iterable) method.

@Component
class Init implements ApplicationRunner{
    @Autowired
    EmployeeRepository employeeRepository;
    @Override
    public void run(ApplicationArguments applicationArguments) {
        
        // employeeRepository.deleteAll(); // To delete all the records
        String employeeName = "Harry";
        java.util.List<Employee> employeeList = employeeRepository.findAll(new Specification<Employee>(){
            @Override
            public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();
                if(employeeName!=null) {
                    predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("employeeName"), employeeName)));
                }
                return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        });
        employeeRepository.delete(employeeList);
    }
}

5. Conclusion

In this article, We learned about how0 to perform delete operation using spring data JPA. Here we have used spring boot application with MySQL for an explanation, we can use the same mechanism for NoSQL database as well like MongoDB. If you are new with spring JPA then here is a tutorial for spring JPA as well which will guide you learn more concepts about Spring JPA.

6. References

• Spring JPA documentation

7. Source Code

• Git : spring-boot-jpa-delete-query