1. Introduction
In this short tutorial, we’ll learn how to create update queries with the Spring Data JPA @Query annotation. We’ll achieve this by using the @Modifying annotation.
First, to refresh our memory, we can read how to make queries using Spring Data JPA. After that, we’ll deep dive into the use of the @Query and @Modifying annotations. Finally, we’ll discuss how to manage the state of our persistence context when using modifying queries.
2. Querying in Spring Data JPA
First, let’s recap the three mechanisms that Spring Data JPA provides for querying data in a database:
- Query methods
- @Query annotation
- Custom repository implementation
Let’s create a User class and a matching Spring Data JPA repository to illustrate these mechanisms:
@Entity
@Table(name = "users", schema = "users")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
private String name;
private LocalDate creationDate;
private LocalDate lastLoginDate;
private boolean active;
private String email;
}
public interface UserRepository extends JpaRepository<User, Integer> {}
The query methods mechanism allows us to manipulate the data by deriving the queries from the method names:
List<User> findAllByName(String name);
void deleteAllByCreationDateAfter(LocalDate date);
In this example, we can find a query that retrieves users by their names or a query that removes users having a creation date after a certain date.
As for the @Query annotation, it provides us with the opportunity to write a specific JPQL or SQL query in the @Query annotation:
@Query("select u from User u where u.email like '%@gmail.com'")
List<User> findUsersWithGmailAddress();
In this code snippet, we can see a query retrieving users having an @gmail.com email address.
The first mechanism enables us to retrieve or delete data. As for the second mechanism, it allows us to execute pretty much any query. However, for updating queries, we must add the @Modifying annotation. This will be the topic of this tutorial.
3. Using the @Modifying Annotation
The @Modifying annotation is used to enhance the @Query annotation so that we can execute not only SELECT queries, but also INSERT, UPDATE, DELETE, and even DDL queries.
Now let’s play with this annotation a little.
First, let’s look at an example of a @Modifying UPDATE query:
@Modifying
@Query("update User u set u.active = false where u.lastLoginDate < :date")
void deactivateUsersNotLoggedInSince(@Param("date") LocalDate date);
Here we’re deactivating the users that haven’t logged in since a given date.
Let’s try another one, where we’ll delete deactivated users:
@Modifying
@Query("delete User u where u.active = false")
int deleteDeactivatedUsers();
As we can see, this method returns an integer. It’s a feature of Spring Data JPA @Modifying queries that provides us with the number of updated entities.
We should note that executing a delete query with @Query works differently from Spring Data JPA’s deleteBy name-derived query methods. The latter first fetches the entities from the database and then deletes them one by one. This means that the life-cycle method @PreRemove will be called on those entities. However, with the former, a single query is executed against the database.
Finally, let’s add a deleted column to our USERS table with a DDL query:
@Modifying
@Query(value = "alter table USERS.USERS add column deleted int(1) not null default 0", nativeQuery = true)
void addDeletedColumn();
Unfortunately, using modifying queries leaves the underlying persistence context outdated. However, it is possible to manage this situation. That’s the subject of the next section.
3.1. Result of NOT Using the @Modifying Annotation
Let’s see what happens when we don’t put the @Modifying annotation on the delete query.
For this reason, we need to create yet another method:
@Query("delete User u where u.active = false")
int deleteDeactivatedUsersWithNoModifyingAnnotation();
Notice the missing annotation.
When we execute the above method, we get an InvalidDataAccessApiUsage exception:
org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.QueryExecutionRequestException:
Not supported for DML operations [delete com.baeldung.boot.domain.User u where u.active = false]
(...)
The error message is pretty clear; the query is Not supported for DML operations.
4. Managing the Persistence Context
If our modifying query changes entities contained in the persistence context, then this context becomes outdated. One way to manage this situation is to clear the persistence context. By doing that, we make sure that the persistence context will fetch the entities from the database next time.
However, we don’t have to explicitly call the clear() method on the EntityManager. We can just use the clearAutomatically property from the @Modifying annotation:
@Modifying(clearAutomatically = true)
This way, we make sure that the persistence context is cleared after our query execution.
However, if our persistence context contained unflushed changes, clearing it would mean dropping the unsaved changes. Fortunately, there’s another property of the annotation we can use in this case, flushAutomatically:
@Modifying(flushAutomatically = true)
Now the EntityManager is flushed before our query is executed.
5. Conclusion
That concludes this brief article about the @Modifying annotation. We learned how to use this annotation to execute updating queries, like INSERT, UPDATE, DELETE, and even DDL. After that, we discussed how to manage the state of the persistence context with the clearAutomatically and flushAutomatically properties.
As usual, the full code for this article is available over on GitHub.
