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

1. Creating new EntityManagers is correct, the other isn't (see below).

2. Calling EntityManager#clear() is very dangerous in all but a single-threaded system.

   ...causing all managed entities to become detached...

   Hence, if one thread works with attached entities while "your" thread clears the entity manager you have severe side effects.

3. Hhm, hard to tell. If the number of entities modified outside your application is low I'd work directly with the datasource and a JdbcTemplate for the respective operations.