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, we'll refresh our memory and see how to make queries using Spring Data JPA. After that, we'll deep dive into the use of @Query and @Modifying annotations. Finally, we'll see how to manage the state of our persistence context when using modifying queries.

2. Querying in Spring Data JPA

First, let's recap the 3 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:

@Table(name = "users", schema = "users")
public class User {

    @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 yet a query that removes users having a creation date after a certain date.

As for the @Query annotation, it provides us with the possibility 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 one, 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 to execute not only SELECT queries but also INSERT, UPDATE, DELETE, and even DDL queries.

Let's play with this annotation a little and see what it's made of.

First, let's see an example of a @Modifying UPDATE query:

@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 didn't log in since a given date.

Let's try another one where we'll delete deactivated users:

@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. Thus, this means that the lifecycle 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:

@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)

That way, we make sure that the persistence context is cleared after our query execution.

But, what if our persistence context contained unflushed changes? Therefore, clearing it would mean dropping unsaved changes. Fortunately, there's another property of the annotation we can use – flushAutomatically:

@Modifying(flushAutomatically = true)

Now, the EntityManager is flushed before our query is executed.

5. Conclusion

That concludes this short article about the @Modifying annotation. We've seen how to use this annotation to execute updating queries like INSERT, UPDATE, DELETE, and even DDL. After that, we learned 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.