ehcache - Spring with hibernate cache native query -

i'm using spring 3.2 hibernate 4. in dao implementation want cache results of native sql query. method results of query looks this:

public list<object[]> getbestsellers(string category) {     session session = sessionfactory.getcurrentsession();      query query = session.createsqlquery( "select i_id, i_title, a_fname, a_lname , sum(ol_qty) val " +             "from " +             "orders, order_line, item, author " +             "where " +             "order_line.ol_o_id = orders.o_id , item.i_id = order_line.ol_i_id " +             "and item.i_subject = :category , item.i_a_id = author.a_id group i_id " +             "order orders.o_date, val desc" );      query.setparameter( "category", category );     query.setmaxresults( 50 );     query.setcacheable( true );      list<object[]> res = query.list();      return res; } 

it seems doesn't work , don't know why.

i have configured hibernate in applicationcontext.xml this:

<props>     <prop key="hibernate.jdbc.batch_size">50</prop>     <prop key="hibernate.show_sql">false</prop>     <prop key="hibernate.dialect">${jdbc.hibernate.dialect}</prop>     <prop key="hibernate.max_fetch_depth">4</prop>     <prop key="hibernate.cache.use_second_level_cache">true</prop>     <prop key="hibernate.cache.use_query_cache">true</prop>     <prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.ehcacheregionfactory</prop>         <prop key="hibernate.cache.provider_configuration_file_resource_path">classpath:ehcache.xml</prop>      <prop key="hibernate.generate_statistics">true</prop> </props> 

and ehcache.xml:

<?xml version="1.0" encoding="utf-8"?> <ehcache xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xsi:nonamespaceschemalocation="http://ehcache.org/ehcache.xsd">         <diskstore path="java.io.tmpdir/cloudscale-cache"/>     <defaultcache          eternal="false"          maxelementsinmemory="1000"         overflowtodisk="false"          diskpersistent="false"          timetoidleseconds="0"         timetoliveseconds="600"          memorystoreevictionpolicy="lru"     />     <cache          name="org.hibernate.cache.spi.updatetimestampscache"         maxelementsinmemory="50"         eternal="false"         timetoidleseconds="0"         timetoliveseconds="86400"         overflowtodisk="true"/>     <cache          name="org.hibernate.cache.internal.standardquerycache"         maxelementsinmemory="50"         eternal="false"         timetoidleseconds="0"         timetoliveseconds="86400"         overflowtodisk="true"/>  </ehcache> 

if want cache native sql queries have use addscalar()
using addscalar() ;hibernate try converted result of sql query return objects individual named columns, rather entities.

modify query below

 query query = session.createsqlquery( "select i_id a, i_title b, a_fname c, a_lname d, sum(ol_qty) val " +             "from " +             "orders, order_line, item, author " +             "where " +             "order_line.ol_o_id = orders.o_id , item.i_id = order_line.ol_i_id " +             "and item.i_subject = :category , item.i_a_id = author.a_id group i_id " +             "order orders.o_date, val desc" ).addscalar("a").addscalar("b").addscalar("c").addscalar("d").addscalar("val");