Enabling second level cache for find all queries

yellek · **Posted:** Thu Aug 07, 2008 4:47 am

We have a large number of reference data tables that are used to populate drop down lists on our UI. At the moment Hibernate is executing a query against the database for every display request for every reference table. I would like to enable second level caching for these tables to improve performance.

I have enabled second level caching using EHCache as shown below but the find all queries are never hitting the second level cache. They do seem to be populating the cache but never using it. Can anyone suggest what sort of query I could use to ensure that the second level cache is checked for results?

Hibernate version:
3.2.6
Mapping documents:

Code:

      <property name="hibernate.cache.provider_class">
         net.sf.ehcache.hibernate.EhCacheProvider
      </property>

Code:

<class-cache class="package.ClassName" usage="read-only"/>

Code:

   <cache name="package.ClassName"
     maxElementsInMemory="300"
     eternal="true"
     overflowToDisk="false"
     />

Code between sessionFactory.openSession() and session.close():

Code:

    return getSession().createQuery("from " + getPersistentClass().getName()).list();

Full stack trace of any exception that occurs:
N/A
Name and version of the database you are using:
Oracle 10g
The generated SQL (show_sql=true):
N/A
Debug level Hibernate log excerpt:
N/A

nordborg · **Posted:** Thu Aug 07, 2008 5:07 am

Basically there are two things you need to do:

1. Enabled the query cache by setting

Code:

hibernate.cache.use_query_cache true

in your hibernate.properties files

2. Tell Hibernate that you want to cache the result of a query by calling

Code:

Query.setCacheable(true)

Eg.

Code:

return getSession().createQuery("from " + getPersistentClass().getName()).setCacheable(true).list(); 

For more information read
http://www.hibernate.org/hib_docs/v3/re ... querycache

yellek · **Posted:** Thu Aug 07, 2008 6:11 pm

That did the trick, thanks!

pragatiredkar · **Joined:** Tue Oct 02, 2007 1:47 am **Posts:** 6

Hi

For me the second level cache does'nt seem to work though have enabled it.
The log for p6spy shows the queries fired everytime i need the object which ideally should have been picked up from the cache and no query should be hitting the database.

Code :
1. Query query =dbSession.createSQLQuery(queryTable).addEntity(queryTable, classObj).setCacheable(true);

2. Also hibernate.properties has been enabled to activate the second level cache

<prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>

What am i missing ?

Thanks,
Pragati