how to make native sql update visible to jpa query

1. Background
Native update then query using jpa (spring data jpa, hibernate as the persistence provider) cannot see native sql's updates in a same transaction.

	JPA (native) update	jdbc native update
jpa query	1 not visible	2 not visible
jdbc query	3 visible	4 visible

The objective is to solve 1 and 2 to make sure JPA update (via native sql) and jdbc native sql update result are visible to following JAP query.

2. Solution
2.1 case by case
If you are going to solve the issue temporarily, and you can access the entityManager, then the em.refresh(entity) may solve your problem. For instance, We have a DemoRepository where:

@Modifying()
@Query(value = "update demo set name=:newName where id=:id", nativeQuery = true)
void updateNameNative(@Param("newName") String newName, @Param("id")int id);

// you have saved an demo instance with name of "name3", then do an update as below

demoRepository.updateNameNative("name3-1", demo1.getId());

Optional<Demo> demoFromJpa1 = demoRepository.findById(demo1.getId());
em.refresh(demoFromJpa1.get());
if (demoFromJpa1.isPresent())
    System.out.println("1. jpa native update name to 'name3-1', then jpa query: " + demoFromJpa1.get());

If you don't refresh, the result will be "name3", if you do, you will see "name3-1"

2.2 Using Spring data annotation
For the above update method, if you do not want to call low level em.refresh method, you can add below attributes into @Modify annotation, to let spring data help to clear the persistence context thus changes can be seen after native sql updates.

@Modifying(flushAutomatically = true, clearAutomatically=true)
@Query(value = "update demo set name=:newName where id=:id", nativeQuery = true)
void updateNameNative(@Param("newName") String newName, @Param("id")int id);

2.3 AOP way

Create an Aspect to intercept all ( or part of ) Spring data jpa (or whatever you need) methods to flush changes to db before query, and clear persistence context after query, then you will get latest updates. Actually this is what the above @Modifying annotation's two attributes works.

@Aspect@Component@Configurablepublic class EntityMangerAspect {
    @PersistenceContext    private EntityManager em;

    @Pointcut("execution(* org.springframework.data.jpa.repository.JpaRepository+.*(*))")
    public void allMethodInJpaRepository() {
    }

    @Around("allMethodInJpaRepository()")
    public Object beforeAllMethodInJpaRepository(ProceedingJoinPoint pjp) {
        Object result = null;
        try {
            em.flush();
            result = pjp.proceed();
            em.clear();
        } catch (Throwable throwable) {
            throwable.printStackTrace();
        }
        return result;
    }
}

FYI: JPA query in Spring data jpa support the above two attributes

this.em = em;
this.flush = method.getFlushAutomatically();
this.clear = method.getClearAutomatically();

   @Override   protected Object doExecute(AbstractJpaQuery query, Object[] values) {

      if (flush) {
         em.flush();
      }

      int result = query.createQuery(values).executeUpdate();

      if (clear) {
         em.clear();
      }

      return result;
   }
}