The information provided in this blog is validated against hibernate-core 3.6.7.Final and 3.5.6-Final.

Hibernate’s second level cache is used to cache domain objects and queries.
In this blog I want to focus on the query cache of hibernate and show how native queries affect the second level cache and how you can configure which caches are affected by a native query.

Background

Query caches are useful if you want to cache the result of a HQL or criteria query. But be careful if you are using query caches and native queries. Native queries can invalidate the second level caches. The reason is found in this class org.hibernate.engine.query.NativeSQLQueryPlan.

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.

A native query can execute any sql you want, so only the creator of that query knows what the query does and which tabels are affected, if any.
Sometime you might only want to execute a native query to only change the connections properties, e.g. when using a oracle database.

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.

Configure cache synchronization

Fortunatly the hibernate API let you specify the entities or query spaces that are affected by your query.
Tell hibernate which tables are affected by your query and hibernate will only invalidate caches that are based on that data.

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>