Hi All,

I am trying to get Query cache working for a SQL query not HQL.
No matter how many times I execute query, the query results just don't stick into the cache and for every subsequent invocation the cache lookup fails and ends up in a database roundtrip.

Let me what is missing here..

The query goes something like this
---------------------------------------------------
select value from from Xtable where id1=:id1 and id2=:id2 and id3=:id3 and type='T' and mtchCol=:txt
union
select value from from Xtable where id1=:id1 and id2=:id2 and id3=:id3 and type='HL' and hnbr>=:hnbr and lnbr<:lnbr
union
select value from from Xtable where id1=:id1 and id2=:id2 and id3=:id3 and type='H' and lnbr <:lnbr
union
select value from from Xtable where id1=:id1 and id2=:id2 and id3=:id3 and type='L' and hnbr>= :hnbr
---------------------------------------------------
SQLQuery query = (SQLQuery) session.createSQLQuery(sqlQuery).setCacheable(true).setCacheRegion("customCache");
Object result = query.addScalar("value",Hibernate.BIG_DECIMAL).uniqueResult();
---------------------------------------------------
hibernate.cfg.xml

<property name="hibernate.cache.use_second_level_cache">true</property>
<property name="hibernate.cache.use_query_cache">true</property>
<property name="cache.provider_configuration_file_resource_path">ehcache.xml</property>
<property name="hibernate.cache.provider_class">net.sf.ehcache.hibernate.EhCacheProvider</property>
-----------------------------------------------------------
ehcache.xml

<ehcache>
<diskStore path="user.dir"/>

<cache name="customCache"
maxElementsInMemory="5500"
eternal="true"
overflowToDisk="true"
/>

</ehcache>
------------------------------------------------------------
Log response


starting query cache at region: customCache
2009-03-29T13:08:41.164Z WARN net.sf.ehcache.hibernate.EhCacheProvider[89]: Could not find a specific ehcache configuration for cache named [customCache]; using defaults.
2009-03-29T13:08:41.164Z DEBUG net.sf.ehcache.store.DiskStore[182]: Deleting data file customCache.data
2009-03-29T13:08:41.164Z DEBUG net.sf.ehcache.store.MemoryStore[73]: Initialized net.sf.ehcache.store.LruMemoryStore for customCache
2009-03-29T13:08:41.164Z DEBUG net.sf.ehcache.store.LruMemoryStore[71]: customCache Cache: Using SpoolingLinkedHashMap implementation
2009-03-29T13:08:41.164Z DEBUG net.sf.ehcache.Cache[429]: Initialised cache: customCache
2009-03-29T13:08:41.164Z DEBUG net.sf.ehcache.hibernate.EhCacheProvider[92]: started EHCache region: customCache
2009-03-29T13:08:41.164Z DEBUG org.hibernate.cache.StandardQueryCache[102]: checking cached query results in region: customCache
2009-03-29T13:08:41.180Z DEBUG net.sf.ehcache.hibernate.EhCache[77]: key: sql:
2009-03-29T13:08:41.180Z DEBUG net.sf.ehcache.store.MemoryStore[138]: customCacheCache: customCacheMemoryStore miss for sql: 1 ; parameters: ; named parameters: {}
2009-03-29T13:08:41.180Z DEBUG net.sf.ehcache.Cache[661]: customCache cache - Miss
2009-03-29T13:08:41.180Z DEBUG org.hibernate.cache.StandardQueryCache[107]: query results were not found in cache