Second Level Cache For SQL Queries Under Hibernate

Second level cache in Hibernate allows to greatly speed-up your application by minimizing number of SQL queries issued and serving some results from in-memory cache (with optional disk storage or distributed cache). You have option to plug in different cache libraries (or to Bring Your Own Cache – an approach popular among my colleagues from India 😉 ). There are caching limits you must be aware of when implementing 2nd level cache.

ID-based entity lookup cache

Caching entity-based lookup by id is very straightforward:

<session-factory>
    <property name="cache.provider_class">
        com.opensymphony.oscache.hibernate.OSCacheProvider
    </property>
    (...)
</session-factory>

<class name="EntityClass" table="ENTITY_TABLE">
   <cache usage="read-only" />
   (...)
</class>

Since then selecting EntityClass by id will use cache (BTW. remember to set cache expiration policy if entity is mutable!). But querying but other entity attributes will not cache the results.

Query cache

Here so-called "query cache" jumps in:

<session-factory>
    <property name="cache.use_query_cache">true</property>
    (...)
</session-factory>

Query query = new Query(EntityClass.class);
(...)
query.setCacheable(true);
query.list();

and voila! Queries issued (with parameters) are indices for cached results. The very important note is the value stored in cache. Entity keys and types are stored. Why is it important? Because it complicates caching SQL queries.

Caching SQL queries

Query cache requires query result to be Hibernate-known entity because the reason I mentioned above. That disallow to cache the following construct:

<!-- Map the resultset on a map. -->
<class name="com.comapny.MapDTO" entity-name="EntityMap">
<id name="entityKey" type="java.math.BigDecimal" column="ID" length="38"
access="com.company.MapPropertyAccessor" />
<property name="achLimit" type="java.lang.String" length="256" access="com.company.MapPropertyAccessor" />
</class>

<!-- alias is used in query -->
<resultset name="EntityMapList">
<return alias="list" entity-name="EntityMap" />
</resultset>

<sql-query name="NamedQueryName" resultset-ref="EntityMapList">
SELECT (...) AS {list.entityKey}
(...)
FROM TABLE_NAME
WHERE
(...)
</sql-query>

We will get the error:

Error: could not load an entity: EntityMap#1028
ORA-00942: table or view does not exist

EntityMap cannot be loaded separately by Hibernate because it's a DTO (data transfer object), not the entity. How to fix this error? Just change result of named query from DTO to corresponding entity. Then entity retrieved will be cached properly by Hibernate.

This entry was posted in en and tagged , , , , , . Bookmark the permalink.