

Table of Contents
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 Entrydelete(ID id)
delete a record by Primary Key ID.deleteAll()
delete all recordsdelete(Iterable<? extends T> iterable)
delete multiple records byIterable
likeList
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
Logical keyword | Keyword expressions |
---|---|
AND | And |
OR | Or |
AFTER | After , IsAfter |
BEFORE | Before , IsBefore |
CONTAINING | Containing , IsContaining , Contains |
BETWEEN | Between , IsBetween |
ENDING_WITH | EndingWith , IsEndingWith , EndsWith |
EXISTS | Exists |
FALSE | False , IsFalse |
GREATER_THAN | GreaterThan , IsGreaterThan |
GREATER_THAN_EQUALS | GreaterThanEqual , IsGreaterThanEqual |
IN | In , IsIn |
IS | Is , Equals , (or no keyword) |
IS_NOT_NULL | NotNull , IsNotNull |
IS_NULL | Null , IsNull |
LESS_THAN | LessThan , IsLessThan |
LESS_THAN_EQUAL | LessThanEqual , IsLessThanEqual |
LIKE | Like , IsLike |
NEAR | Near , IsNear |
NOT | Not , IsNot |
NOT_IN | NotIn , IsNotIn |
NOT_LIKE | NotLike , IsNotLike |
REGEX | Regex , MatchesRegex , Matches |
STARTING_WITH | StartingWith , IsStartingWith , StartsWith |
TRUE | True , IsTrue |
WITHIN | Within , IsWithin |
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
7. Source Code