This blog article is part of a “series of blog articles” about common pitfalls using JPA and ways to avoid them. In this article, we describe unexpected results of JPQL queries with respect of the query flush mode.
JPA supports two flush modes when executing a query within a transaction:
- Flush mode
AUTOmeans all updates of the current transaction are visible to the query execution. This is usually done by flushing the updates prior to the execution of the query. As a consequence, query execution might result in a optimistic lock exception with flush mode
- According to the JPA spec the effect of updates with respect to the query result is unspecified, when using mode
COMMIT. Usually the persistence provider simply does not flush updates on query execution. This may be used as a performance optimization, since flushing might be an expensive operation. However, it might lead to unexpected query results:
Employee e = ... // Employee with weekyhours 40 e.setWeeklyhours(41); q = em.createQuery( "SELECT e FROM Employee e WHERE e.weeklyhours > 40"); q.setFlushMode(FlushModeType.COMMIT); List<Employee> result = q.getResultList(); // result.contains(e) -> false
When executing the query, the employee in the database still has the old value
weeklyhours = 40, so the employee is not part of the query result.
It is also possible that the query result includes an entity that does not match the where clause of the JPQL query:
Employee e = ... // Employee with weekyhours 40 e.setWeeklyhours(41); q = em.createQuery( "SELECT e FROM Employee e WHERE e.weeklyhours = 40"); q.setFlushMode(FlushModeType.COMMIT); List<Employee> result = q.getResultList(); // result.contains(e) -> true, // but e does not match the where clause
Again, the employee in the database still has the old value
weeklyhours = 40, so this time it is included in the query result. Since the entity is already loaded in the current transaction the query result will include the current version of the employee. But the current version has updated the weeklyhours to
41 which does not match the where clause of the JPQL query.
Recommendation: Use the query optimization flush mode
COMMIT only if the modifications in the current transaction do not affect the query result.
You can find an example of this pitfall and its solution in the class QueryFlushModeExperiment in this project: https://github.com/akquinet/jpapitfalls.git.
You must log in to post a comment.