2nd level cache invalidation with native queries

Question

I have an application that uses 2nd level cache on a JBoss AS 7 installation (Infinispan 2nd level cache provider).
We have some update JPQL Queries that invalidate the cache- I wonder what will be the effect if we include some native SQL queries in our application. Will the Query cache be invalidated ?
Also I remember using the sqlQuery.addSynchronizedQuerySpace("") instruction on Hibernate to prevent cache invalidation for some native SQL queries. Is it possible to do it also with JPA ?
Thanks!

Answer 1

I came across this question when dealing with the same problem today, so I thought I'd post my findings here.

Using JPA native UPDATE/INSERT/DELETE queries does cause Hibernate to invalidate the entire 2nd level entity cache. As you mentioned in your question, Hibernate has a workaround for this, but it seems to not be possible to do the equivalent of Hibernate's addSynchronizedQuerySpace(), addSynchronizedEntityClass() and addSynchronizedEntityName() using pure JPA.

What JPA however does allow you to do is to unwrap a JPA Query object to gain access to the JPA provider's API. If you're using Hibernate as a JPA provider, this will then allow you to use Hibernate's addSynchronizedXxx methods as follows:

Query query = entityManager.createNativeQuery("UPDATE user SET ...");
query.unwrap(org.hibernate.SQLQuery.class)
        .addSynchronizedEntityClass(User.class);

It's not an ideal solution, but it will effectively allow you to prevent the entire second level cache from being invalidated.