title: In Spring Data, How can I hint the hibernate affected table for native update sql
body: <p>In spring data</p>
  
  <pre><code>@Modifying
  @Query(value = "update user set name='abc'", nativeQuery = true)
  int changeThemAll();
  </code></pre>
  
  <p>Due to <code>Modifying</code>, this query will invalid all cache, because hibernate can not find out the affected entities.
  in <code>org.hibernate.action.internal.BulkOperationCleanupAction#affectedEntity</code>, if there is no tablespace, will consider the table is affected.</p>
  
  <p>This becomes problem</p>
  
  <ol>
  <li>if the region is readonly, the hazelcast hibernate second level cache implemention will throw exception.</li>
  <li>when there is a lot entries in cache, invalidate all table will affect the system</li>
  </ol>
  
  <p>Also, I found this <a href="https://www.link-intersystems.com/blog/2011/10/08/impact-of-native-sql-queries-on-hibernates-second-level-cache/" rel="nofollow noreferrer">article</a> described the solution for hibernate xml config.</p>
  
  <p>The workaround I can think of is remove the <code>@Modifying</code>, and manual invalidate cache or use <code>JdbcTemplate</code> run the native sql and manual invalidate the cache.</p>
  
  <p>Full stacktrace</p>
  
  <pre><code>at com.hazelcast.hibernate.access.ReadOnlyAccessDelegate.lockRegion(ReadOnlyAccessDelegate.java:72)
  at com.hazelcast.hibernate.region.EntityRegionAccessStrategyAdapter.lockRegion(EntityRegionAccessStrategyAdapter.java:99)
  at org.hibernate.action.internal.BulkOperationCleanupAction$EntityCleanup.&lt;init&gt;(BulkOperationCleanupAction.java:209)
  at org.hibernate.action.internal.BulkOperationCleanupAction$EntityCleanup.&lt;init&gt;(BulkOperationCleanupAction.java:203)
  at org.hibernate.action.internal.BulkOperationCleanupAction.&lt;init&gt;(BulkOperationCleanupAction.java:110)
  at org.hibernate.engine.query.spi.NativeSQLQueryPlan.coordinateSharedCacheCleanup(NativeSQLQueryPlan.java:152)
  at org.hibernate.engine.query.spi.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:176)
  at org.hibernate.internal.SessionImpl.executeNativeUpdate(SessionImpl.java:1373)
  at org.hibernate.internal.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:373)
  at org.hibernate.jpa.internal.QueryImpl.internalExecuteUpdate(QueryImpl.java:405)
  at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:61)
  at org.springframework.data.jpa.repository.query.JpaQueryExecution$ModifyingExecution.doExecute(JpaQueryExecution.java:238)
  at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:85)
  at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:116)
  at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106)
  at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:483)
  at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:461)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
  at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
  at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
  at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
  at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
  at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
  at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
  at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
  at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
  at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
  </code></pre>
  
  <p>Hazelcast <code>ReadOnlyAccessDelegate#lockRegion</code> implemention</p>
  
  <pre><code>@Override
  public SoftLock lockRegion() throws CacheException {
      throw new UnsupportedOperationException("Attempting to lock a read-only cache region: "
              + getHazelcastRegion().getName());
  }
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p>The question is how can I hint the hibernate affected table for native update sql.</p>
  
  <p>e.g.</p>
  
  <pre><code>@org.springframework.data.jpa.repository.QueryHints({
      @QueryHint(name = QueryHints.HINT_CACHE_REGION, value = "UserEntity"),
      // Something like this @QueryHint(name = QueryHints.HINT_AFFECTED_ENTITIES, value = "UserEntity"),
  })
  </code></pre>
  
  <p>I can hint the cache region used for result, but how to hint the table spaces or affected entities ?</p>
  
  <p>Relate methods </p>
  
  <ul>
  <li>org.hibernate.loader.custom.CustomQuery#getQuerySpaces</li>
  <li>org.hibernate.SQLQuery#addSynchronizedQuerySpace</li>
  <li>org.hibernate.SQLQuery#addSynchronizedEntityName</li>
  <li>org.hibernate.SQLQuery#addSynchronizedEntityClass</li>
  </ul>