Spring Boot Data JPA - Modifying update query - Refresh persistence context

Question

I'm working with Spring Boot 1.3.0.M4 and a MySQL database.

I have a problem when using modifying queries, the EntityManager contains outdated entities after the query has executed.

Original JPA Repository:

public interface EmailRepository extends JpaRepository<Email, Long> {

    @Transactional
    @Modifying
    @Query("update Email e set e.active = false where e.active = true and e.expire <= NOW()")
    Integer deactivateByExpired();

}

Suppose we have Email [id=1, active=true, expire=2015/01/01] in DB.

After executing:

emailRepository.save(email);
emailRepository.deactivateByExpired();
System.out.println(emailRepository.findOne(1L).isActive()); // prints true!! it should print false

First approach to solve the problem: add clearAutomatically = true

public interface EmailRepository extends JpaRepository<Email, Long> {

    @Transactional
    @Modifying(clearAutomatically = true)
    @Query("update Email e set e.active = false where e.active = true and e.expire <= NOW()")
    Integer deactivateByExpired();

}

This approach clears the persistence context not to have outdated values, but it drops all non-flushed changes still pending in the EntityManager. As I use only save() methods and not saveAndFlush() some changes are lost for other entities :(

Second approach to solve the problem: custom implementation for repository

public interface EmailRepository extends JpaRepository<Email, Long>, EmailRepositoryCustom {

}

public interface EmailRepositoryCustom {

    Integer deactivateByExpired();

}

public class EmailRepositoryImpl implements EmailRepositoryCustom {

    @PersistenceContext
    private EntityManager entityManager;

    @Transactional
    @Override
    public Integer deactivateByExpired() {
        String hsql = "update Email e set e.active = false where e.active = true and e.expire <= NOW()";
        Query query = entityManager.createQuery(hsql);
        entityManager.flush();
        Integer result = query.executeUpdate();
        entityManager.clear();
        return result;
    }

}

This approach works similar to @Modifying(clearAutomatically = true) but it first forces the EntityManager to flush all changes to DB before executing the update and then it clears the persistence context. This way there won't be outdated entities and all changes will be saved in DB.

I would like to know if there's a better way to execute update statements in JPA without having the issue of the outdated entities and without the manual flush to DB. Perhaps disabling the 2nd level cache? How can I do it in Spring Boot?

Update 2018

Spring Data JPA approved my PR, there's a flushAutomatically option in @Modifying() now.

@Modifying(flushAutomatically = true, clearAutomatically = true)

The second-level cache is irrelevant here. Entities are kept in the first-level cache. Flushing before is the appropriate solution. You could bring up this topic as a RFE in the Spring-data-JPA bug repository. Since you can automatically clear after the query via annotation, I would find it normal to be able to also automatically flush before the query via an additional `flushAutomatically` attribute. That said, you can also simply keep your first solution and simply flush explicitely before executing the query. — JB Nizet, Sep 26 '15 at 15:24
I created a ticket in Spring Data JIRA [DATAJPA-806: add flushAutomatically attribute to @Modifying annotation](https://jira.spring.io/browse/DATAJPA-806) — ciri-cuervo, Oct 22 '15 at 00:46
Created pull request on Spring Data JPA repository: https://github.com/spring-projects/spring-data-jpa/pull/172 — ciri-cuervo, May 20 '16 at 17:41
@Modifying(flushAutomatically = true, clearAutomatically = true) saved my day. I was about to repeat my project next year , and this answer saved me. — Udith Indrakantha, Nov 15 '19 at 14:25
It defies believe that the Sprint Data developers thought they should attempt to optimize performance over implementing correct behavior when it comes to writing to a database. Adding this annotation did not fix the problem for my tests. — Alex Worden, Nov 27 '19 at 17:06

score 12 · Accepted Answer · edited Oct 04 '19 at 15:54

I know this is not a direct answer to your question, since you already have built a fix and started a pull request on Github. Thank you for that!

But I would like to explain the JPA way you can go. So you would like to change all entities which match a specific criteria and update a value on each. The normal approach is just to load all needed entities:

@Query("SELECT * FROM Email e where e.active = true and e.expire <= NOW()")
List<Email> findExpired();

Then iterate over them and update the values:

for (Email email : findExpired()) {
  email.setActive(false);
}

Now hibernate knows all changes and will write them to the database if the transaction is done or you call EntityManager.flush() manually. I know this won't work well if you have a big amount of data entries, since you load all entities into memory. But this is the best way, to keep the hibernate entity cache, 2nd level caches and the database in sync.

Does this answer say "the `@Modifying´ annotation is useless"? No! If you ensure the modified entities are not in your local cache e.g. write-only application, this approach is just the way to go.

And just for the record: you don't need @Transactional on your repository methods.

Just for the record v2: the active column looks as it has a direct dependency to expire. So why not delete active completely and look just on expire in every query?

It's important to understand that once an Entity is loaded into a persistence context it is managed, and will not be reloaded from the database unless you call `refresh()` (calling `find()` will just return the already loaded version). Update queries will only update the database, not any entities already managed, whereas `delete()` queries will update the persistence context, so a call to `find()` will not return an entity which was deleted with a query.. — Klaus Groenbaek, Mar 18 '17 at 11:53

score 3 · Answer 2 · answered Sep 22 '17 at 07:12

As klaus-groenbaek said, you can inject EntityManager and use its refresh method :

@Inject
EntityManager entityManager;

...

emailRepository.save(email);
emailRepository.deactivateByExpired();
Email email2 = emailRepository.findOne(1L);
entityManager.refresh(email2);
System.out.println(email2.isActive()); // prints false

Spring Boot Data JPA - Modifying update query - Refresh persistence context

Original JPA Repository:

First approach to solve the problem: add clearAutomatically = true

Second approach to solve the problem: custom implementation for repository

2 Answers2

Linked