Close

Spring Data JPA - Understanding Modifying Queries

[Updated: Aug 9, 2018, Created: Aug 8, 2018]

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 Project

Dependencies and Technologies Used:

  • spring-data-jpa 2.0.9.RELEASE: Spring Data module for JPA repositories.
    Uses org.springframework:spring-co1ntext 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

@Modifying example Select All Download
  • spring-data-jpa-modifying-queries
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • EmployeeRepository.java
          • resources
            • META-INF

    See Also