Avoid stale data from DB when using JPA?

Question

I am facing a problem similar to the one described in Invalidating JPA EntityManager session :

Problem: Getting stale data

We are running JPQL queries on an SQL database that is also concurrently changed by a different application. We are using JSF+Spring+EclipseLink running under Tomcat.

The class doing the JPQL queries is a singleton Spring bean, and uses an injected EntityManager:

@Component
public class DataRepository{
    @PersistenceContext
    private EntityManager entityManager;
    public List<MyDTO> getStuff(long id) {
        String jpqlQuery ="SELECT new MyDTO([...])";
        TypedQuery<MyDTO> query = entityManager.createQuery(jpqlQuery,MyDTO.class);
        return query.getResultList();
    }
[...]

(code paraphrased).

The problem is that this code does not see changes performed directly on the database. These changes only become visible if the Tomcat instance is restarted.

What we have tried

We assume that this behaviour is caused by the first level cache associated to the EntityManager, as described in the linked question. We found two solutions:

• call entityManager.clear() before invoking createQuery (this is suggested in the linked question)
• inject an EntityManagerFactor (using @PersistenceUnit), then create and close a new EntityManager for each query

Both solutions do what we want - we get fresh data.

Questions:

• Are these two solutions correct? Which one is better?
• In particular, can we safely call entityManager.clear() on the injected EntityManager, or will this somehow impact other code that also uses an injected EntityManager (in the same or in a different class)?
• Is there a different, better approach? Can we somehow declare that we want to refresh the cache, or that we want a fresh EntityManager?

I assume this must be a fairly common problem (as it occurs whenever multiple apps share a database), so I thought there must be a simple solution...

Answer 1

It looks like we have resolved the problem.

There were actually two problems:

1. While debugging the problem, in some cases we did not use a fresh EntityManager for every query. When using and re-using the same EntityManager, entities will apparently never be refreshed once they have been retrieved (unless refreshed explicitly using EntityManager.refresh()). This is apparently because once an entity has been loaded, it is stored in the persistence context (a.k.a. first-level cache) of the EntityManager. Doing this is necessary because the JPA spec requires that subsequent queries for the same entity return the same object instance. So in other words: as long as you use the same EntityManager, you will get stale data unless you refresh explicitly.

2. When we did use a fresh EntityManager for each query, things usually worked. However, we ran into a bug in EclipseLink, where stale data is returned for certain JPQL queries (involving constructor expressions and JOIN FETCH).

Short version

If you want to always get fresh data from a database that is modified from outside your program, then

• use a fresh EntityManager for each query where you want fresh data
• disable the second-level cache (<shared-cache-mode>NONE</shared-cache-mode> in persistence.xml)