Using Both Query Api And Criteria Api in Hibernate Leads Problems

Question

When I update a row with query API and then retrieve the data with criteria API in the same transaction, I get old value, not the updated value. Why is it like that and how can I solve the problem? I need to get the updated value.

@Service
@Transactional
public class ExampleServiceImpl implements ExampleService {
    @Autowired
    ExampleRepository exampleRepository;

    @Transactional
    public void example() {
        ExampleEntity entity = (ExampleEntity) sessionFactory.getCurrentSession().createCriteria(ExampleEntity.class).add(Restrictions.eq("id", 190001L)).uniqueResult();

        exampleRepository.updateState(190001L, State.CLOSED);

        ExampleEntity updatedEntity = (ExampleEntity)sessionFactory.getCurrentSession().createCriteria(ExampleEntity.class).add(Restrictions.eq("id", 190001L)).uniqueResult();

        assertEquals(State.CLOSED, updatedEntity.getState());
    }

}

@Repository
public class ExampleRepositoryImpl implements ExampleRepository {
    public void updateState(Long id, State state) {
        String updateScript = "update exampleEntity set state= '%s', " +
                "VERSION = VERSION + 1 " +
                "where ID = %s;";

        updateScript = String.format(updateScript, state, id);

        Query sqlQuery = sessionFactory.getCurrentSession().createSQLQuery(updateScript);

        sqlQuery.executeUpdate();
    }
}

Note: If I delete the first line and don't retrieve entity at the beginning everything works as I expected.

Answer 1

You are mixing native SQL and hibernate. Basically, when you first retrieve the entity, it gets stored in your session EntityManager. You then use plain SQL to update the row in the database, but as far as hibernate is concerned, the entity has not been dirtied because it isn't clever enough to understand how plain SQL relates to the object model. When you retrieve it the second time, it simply gives you the original entity it already has cached in the EntityManager rather than querying the database.

The solution is to simply manually force evict the entity from the EntityManager after the update as follows: sessionFactory.getCurrentSession().evict(entity);

Or you could simply update the entity you fetched and persist it (best solution IMHO, no superfluous DAO method, and best abstraction away from the database):

ExampleEntity entity = (ExampleEntity) sessionFactory.getCurrentSession().createCriteria(ExampleEntity.class).add(Restrictions.eq("id", 190001L)).uniqueResult();

entity.setState(State.CLOSED);
entity.setVersion(e.getVersion() + 1);

sessionFactory.getCurrentSession().update(entity);

Basically... whichever option you choose, don't mix plain SQL and hibernate queries in the same transaction. Once hibernate has an object loaded, it will return that same entity from its cache until it knows for a fact that it is dirty. It is not clever enough to know that an entity is dirty when plain SQL was used to dirty it. If you have no choice and must use SQL (and this should never be the case in a well designed hibernate model), then call evict to tell hibernate the entity is dirty.