In Spring Data the annotation @Modifying must be used on repository DML query methods. Without this annotation Spring cannot decide to execute JPA's Query#executeUpdate() instead of Query#getQueryResult() .
Also we have to use @Transactional on the modifying query method declaration as all user defined repository methods are read only by default.
Quick example:
public interface EmployeeRepository extends CrudRepository<Employee, Long> {
@Transactional
@Modifying
@Query("UPDATE Employee e SET e.salary = e.salary + :increaseBy")
int increaseSalaries(@Param("increaseBy") int increaseBy);
}
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.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;
public interface EmployeeRepository extends CrudRepository<Employee, Long> {
@Transactional
@Modifying
@Query("UPDATE Employee e SET e.salary = e.salary + e.salary * :byPercent/100 WHERE e.dept = :dept")
int updateDeptSalaries(@Param("dept") String dept, @Param("byPercent") int byPercent);
}
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(" -- increasing salaries by 20% for IT dept --");
int updateCount = repo.updateDeptSalaries("IT", 20);
System.out.println("Update count: " + updateCount);
System.out.println("-- all employees after update--");
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("Sara", "Sales", 3000),
Employee.create("Charlie", "IT", 2500)
);
}
}
Main method
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='Sara', dept='Sales', salary=3000} Employee{id=4, name='Charlie', dept='IT', salary=2500} -- increasing salaries by 20% for IT dept -- Update count: 2 -- all employees after update-- Employee{id=1, name='Diana', dept='Sales', salary=2000} Employee{id=2, name='Rose', dept='IT', salary=4800} Employee{id=3, name='Sara', dept='Sales', salary=3000} Employee{id=4, name='Charlie', dept='IT', salary=3000}
Without @Modifying annotation following exception will be thrown:
Caused by: org.hibernate.hql.internal.QueryExecutionRequestException: Not supported for DML operations [UPDATE com.logicbig.example.Employee e SET e.salary = e.salary + e.salary * :byPercent/100 WHERE e.dept = :dept]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.errorIfDML(QueryTranslatorImpl.java:311)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:362)
Example ProjectDependencies 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.2.13.Final: The core O/RM functionality as provided by Hibernate.
Implements javax.persistence:javax.persistence-api version 2.1 - h2 1.4.196: H2 Database Engine.
- JDK 1.8
- Maven 3.5.4
|