Impact of native sql queries on hibernate’s second level cache

public class NativeSQLQueryPlan implements Serializable {

 ...

 protected void coordinateSharedCacheCleanup(SessionImplementor session) {
    BulkOperationCleanupAction action = new BulkOperationCleanupAction( session, getCustomQuery().getQuerySpaces() );

   if ( session.isEventSource() ) {
     ( ( EventSource ) session ).getActionQueue().addAction( action );
   }
   else {
      action.getAfterTransactionCompletionProcess().doAfterTransactionCompletion( true, session );
   }
 }

 ...

}

The org.hibernate.action.BulkOperationCleanupAction‘s constructor assumes that all tabel spaces are affected by the native query if the query doesn’t define affected spaces itself.

public BulkOperationCleanupAction(SessionImplementor Session, Set tableSpaces) {
    LinkedHashSet<String> spacesList = new LinkedHashSet<String>();
    spacesList.addAll( tableSpaces );
    SessionFactoryImplementor factory = session.getFactory();
   for ( String entityName : factory.getAllClassMetadata().keySet() ) {
     final EntityPersister persister = factory.getEntityPersister( entityName );
     final String[] entitySpaces = (String[]) persister.getQuerySpaces();
     if ( affectedEntity( tableSpaces, entitySpaces ) ) {
        spacesList.addAll( Arrays.asList( entitySpaces ) );
       if ( persister.hasCache() ) {
          entityCleanups.add( new EntityCleanup( persister.getCacheAccessStrategy() ) );
       }
        Set<String> roles = session.getFactory().getCollectionRolesByEntityParticipant( persister.getEntityName() );
       if ( roles != null ) {
         for ( String role : roles ) {
             CollectionPersister collectionPersister = factory.getCollectionPersister( role );
              if ( collectionPersister.hasCache()) {
                 collectionCleanups.add(new CollectionCleanup( collectionPersister.getCacheAccessStrategy() ));
              }
            }
          }
        }
      }
      this.affectedTableSpaces = spacesList.toArray( new String[ spacesList.size()] );
    }

 private boolean affectedEntity(Set affectedTableSpaces, Serializable[] checkTableSpaces) {
   if ( affectedTableSpaces == null || affectedTableSpaces.isEmpty() ) {
     return true;
   }
   for ( Serializable checkTableSpace : checkTableSpaces ) {
     if ( affectedTableSpaces.contains( checkTableSpace ) ) {
       return true;
     }
   }
   return false;
 }

Hibernate must decide when it should invalidate a second level cache. Therefore it must know which sql statements that are sent to the database require a cache to be invalidated. If you let hibernate do all the sql stuff, it knows the statements it generates and therefore the tables that are affected. In this case hibernate can easily decide which caches have to be invalidated.

ALTER SESSION SET NLS_COMP = 'BINARY';
ALTER SESSION SET NLS_SORT = 'BINARY';

But you can also execute a native query that affects the data of the domain objects.

UPDATE CUSTOMER SET ... = ... WHERE ...

Hibernate can’t know what you are doing and therefore it can’t know what caches need to be invalidated. As long as hibernate doesn’t know which caches are affected it must assume that all data is invalid to ensure data consistency. This means that hibernate will invalidate all caches.

SQLQuery sqlQuery = session.createSQLQuery("UPDATE CUSTOMER SET ... WHERE ...");
sqlQuery.addSynchronizedEntityClass(Person.class);
int updatedEntities = sqlQuery.executeUpdate();

The source 5 example demonstrates how to tell hibernate which entities are affected by a native query, but the hibernate api has more methods to define synchronization of caches.

sqlQuery.addSynchronizedEntityClass(Person.class);
sqlQuery.addSynchronizedEntityName("com.link_intersystems.xhibernate.testclasses.Person");
sqlQuery.addSynchronizedQuerySpace("SOME_TABLE");

Sometimes you want to execute a native query that doesn’t change any data like the query shown in source 3. Normally hibernate will invalidate all caches if you just execute such a query.  The reason why it does this is shown in source 1 and 2.
To prevent hibernate from invalidating the second level caches you can add an empty query space synchronization.

SQLQuery sqlQuery = session.createSQLQuery("ALTER SESSION SET NLS_COMP = 'BINARY'");
sqlQuery.addSynchronizedQuerySpace("");  
/*
 * Only the empty query space "" will be invalidated.
 * So no cache will be invalidated, because no table with an empty name exists
 */
int updatedEntities = sqlQuery.executeUpdate();

You can also add synchronization to the xml definition of a named sql query.

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>

 ...

<sql-query name="setNLSCompBinary">
 <!-- an empty synchronize tag prevents hibernate from invalidating second level caches -->
 <synchronize table="" />
  ALTER SESSION SET NLS_COMP = 'BINARY'
</sql-query>

</hibernate-mapping>