Lesson 3: @Modifying Queries

1. Goals

Welcome to a new lesson out of Learn Spring Data course where we’ll be looking at the @Modifying annotation out of Spring Data JPA.

2. Lesson Notes

The relevant module you need to import when you're starting with this lesson is: modifying-queries-lesson-start

If you want to have a look at the fully implemented lesson, as a reference, feel free to import: modifying-queries-lesson-end

2.1. @Modifying Annotation

We previously saw how we can create custom queries using the @Query annotation with JPQL or native SQL to access a single entity or a collection of entities.

But, if we need to create queries that modify entities or perform DDL operations on our database then these require special handling by the underlying framework and we need a way to indicate this.

The @Modifying annotation does exactly this. @Modifying is a method-level annotation that we need to apply on methods annotated with the @Query annotation that insert, update or delete entities.

It should also be applied to DDL statements that change the structure of the underlying database schema.

Before we go any further let’s see an example.

We'll add a custom query method to delete tasks that are completed:

Since the operation requires a delete query, we’ve added the @Modifying annotation and we denote Task completion with the DONE Task Status.

An interesting thing to note here is that the method returns an int value. By default, the annotated queries return the number of affected entities based on the operation carried out.

We don’t always have to return a value though; modifying queries can return either void, int, or even Integer. However, returning anything other than these would result in an IllegalArgumentException. 

Now that we have the modifying method, let’s see how we can call this method from our main class.

Let’s inject the Task Repository into our main class first:

Now, in the run method, let’s invoke our custom query to delete all completed tasks:

Here we are capturing the returned value and logging the result.

However, before we can execute this we need to do two important things: we need to add the @Transactional annotation to our run method:

Modifying operations should be executed within a transaction to ensure data integrity so we need to use this annotation.

We also need to edit the script that loads initial data into our application.

Let's open data.sql in the resources folder and insert a completed task into our Task table:

Note that we are using the ordinal value 3 corresponding to the DONE enum constant in the TaskStatus enum.

We are all set to run the app now, let's run the ModifyingQueriesApp and focus on the console:

As expected, the method returns that one record has been deleted by the custom query.

Now what would happen if we do not specify the @Modifying annotation -- let’s have a look at this.

Let's open TaskRepository and remove the @Modifying annotation from the deleteCompletedTasks() method.

Let’s now run the app again. This time, we’ll get an InvalidDataAccessApiUsageException:

Notice the descriptive error message indicating that the query is not supported for DML operations.

One might wonder why we need to use this annotation at all and why Spring can’t figure this out on its own. Basically, when we write custom modifying queries we are essentially bypassing the underlying data access APIs, and when we do Spring needs to control the persistence context accordingly.

More specifically, Spring needs to know that this query will be executed as an updating query instead of a selecting query.

For a modifying query, Spring will not clear the persistence context by default as this would drop any non-flushed changes.

This is also the reason why derived query methods that modify entities and custom methods in repositories that have control over the underlying data access API do not require this annotation at all.

Let’s add the annotation back and continue.

We’ll look into custom methods in repositories and transactions more in a future lesson.

2.2 Support for Native Queries

The @Modifying annotation also works with native queries, meaning queries that use native SQL.

For example, we can add a column to track if a user is active or not.

Let's open UserRepository and add:

Here we have an alter query that adds the column active to the user table and sets its default value to 1.

Notice that this is a native query as such we have set the attribute nativeQuery = true.

Let’s see this DDL query in action.

Open ModifyingQueriesApp and inject the UserRepository:

Next, let's call our method addActiveColumn from the UserRepository:

Now let’s explore the state of the user table at this point.

Let's run the application, browse the H2 console, and query all entries in the USER table:

We can see that the user table has an additional active column and the value is set to 1.

2.3. Automatic Flushing and Automatic Clearing of Persistence Context (advanced)

Notice that this section is marked as advanced, which means you can skip it if you’re just starting out with Spring Data JPA, as it’s not required to understand the advanced concepts.

As good as it is, the @Modifying annotation is not without its caveats.

In order to understand these, we’d need to have a fair grasp of the following concepts:

  • Persistence Context
  • EntityManager
  • Persistence Context Flush Modes

Therefore, we highly encourage you to read the following resources:

All right, back to the @Modifying annotation and its caveats.

Suppose we are meddling with the modified entities within the same transaction. In such a situation using this annotation could potentially leave the persistent context with outdated entities.

Let’s have a look at the following scenario.

Let's see what happens when we complete a task that’s already in the first level cache and delete all completed tasks, followed by an attempt to retrieve the same completed task all within the same transaction.

Open ModifyingQueriesApp and go to the run method.

Let’s first retrieve the task by id 1; by doing this we are essentially loading the entity into the first level cache:

Now, it’s good practice to check if the task exists before accessing it:

Now inside the if condition, let’s then set the status to DONE:

Next, delete all completed tasks and let’s then try to retrieve the task we just deleted:

Now to see what happens let’s run the app and focus on the console:

Although the log indicates that a task was just deleted, we see that our query still returns the completed task.

Let’s have a look at our database, open H2 console and execute the query to see all records from the Task table:

By looking at the query results, we can see that our modifying query has already deleted the task.

So, how is it that we can still access the task we just deleted? This is because the persistence context is not cleared immediately after we execute the modifying operation, and the task we just deleted still exists in the 1st level cache and Spring Data JPA happily retrieves it from there without hitting the database.

But, that’s not what we want, so let’s fix this.

Open TaskRepository and go to deleteCompletedTasks():

Let’s see the output now; switch back to ModifyingQueriesApp, relaunch the app and focus on the console:

We see this time, as shown in the log that we can no longer retrieve the task once it's deleted.

This is because we indicated to Spring that we want to clear the persistent context after we execute our modifying query.

We also notice another flag: flushAutomatically in the @Modifying annotation:

This too is set to false by default.

If our persistent context contained unflushed changes before we get to our modifying query, setting this to true flushes the changes before we get to that point.

This is a rare scenario so we won’t focus on this here you can check the Resources section for more info.

Note that we don't have to use these two flags if we are not meddling with the modified entities within the same transaction; that is the reason why Spring sets these two flags to false by default.

3. Resources

- Spring Data JPA @Modifying Annotation

- @Modifying Annotation in the Spring Reference Documentation

- Custom Implementations for Spring Data Repositories

- Spring Data JPA Query

- Hibernate Flushing