Lesson 3: @Modifying Queries

1. Goals

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


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 learned how to create custom queries using the @Query annotation with JPQL or native SQL to access a single entity or collection of entities.

However, 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 that. @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 added the @Modifying annotation, and denoted 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 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’re capturing the returned value and logging the result.

However, before we can execute this, we need to do two important things. First, 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’re using the ordinal value 3 corresponding to the DONE enum constant in the TaskStatus enum.

Now that we’re set to run the app, 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 let’s have a look at what would happen if we didn’t specify the @Modifying annotation.

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

Let’s 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’re essentially bypassing the underlying data access APIs, and 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 won’t 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 don’t require this annotation at all.

Now 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, and as such, we’ve set the attribute nativeQuery = true.

Let’s see this DDL query in action.

We’ll 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)

Note 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 these advanced concepts.

As good as it is, the @Modifying annotation isn’t without its caveats.

In order to understand these, we 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’re 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, delete all completed tasks, and then attempt to retrieve the same completed task, all within the same transaction.

We’ll open ModifyingQueriesApp and go to the run method.

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

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

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

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

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

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 the H2 console, and execute the query to see all the 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 isn’t cleared immediately after we execute the modifying operation, and the task we just deleted still exists in the 1st level cache, so Spring Data JPA happily retrieves it from there without hitting the database.

However, this isn’t what we want, so let’s fix it.

We’ll open TaskRepository and go to deleteCompletedTasks():

Now let’s see the output; we’ll 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 can also see another flag: flushAutomatically in the @Modifying annotation:

This too is set to false by default.

If our persistent context contains 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 it here, but you can check the Resources section for more info.

Note that we don't have to use these two flags if we’re not meddling with the modified entities within the same transaction; this is the reason that 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