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

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)

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?

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