I'm getting a class cast exception when trying to cache a sql query that returns a count. The query works just fine if I don't try and cache it.

Here's the code that causes it.

Session hSession = HibernateSessionFactory.getSession();
java.math.BigDecimal rc = (java.math.BigDecimal)hSession.createSQLQuery(
"SELECT COUNT(DISTINCT ac_cc_id)"+
" FROM attorney_case ac "+
" JOIN consumer_case_category_xref cccx ON ac.ac_cc_id=cccx.cccx_cc_id "+
" JOIN category_to_users catmap ON cccx.cccx_cat_id=catmap.catmap_cat_id AND catmap.catmap_catsup_id=? "+
" where ac.ac_usr_id=? and ac.ac_hide=0")
.setLong( 0, superCat.getId() )
.setLong( 1, this.getId() )
.setCacheable(true)
.uniqueResult();
return rc.intValue();




Hibernate version:
3.1 and 3.2rc1

Full stack trace of any exception that occurs:
java.lang.ClassCastException
at org.hibernate.cache.StandardQueryCache.put(StandardQueryCache.java:77)
at org.hibernate.loader.Loader.putResultInQueryCache(Loader.java:2119)
at org.hibernate.loader.Loader.listUsingQueryCache(Loader.java:2063)
at org.hibernate.loader.Loader.list(Loader.java:2021)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:118)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1658)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:756)
at com.legalmatch.hibernate.objects.Attorney.getCaseCountBySuperCategory(Attorney.java:713)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.apache.velocity.util.introspection.UberspectImpl$VelMethodImpl.invoke(UberspectImpl.java:267)
at org.apache.velocity.runtime.parser.node.ASTMethod.execute(ASTMethod.java:197)
at org.apache.velocity.runtime.parser.node.ASTReference.execute(ASTReference.java:175)
at org.apache.velocity.runtime.parser.node.ASTReference.value(ASTReference.java:327)
at org.apache.velocity.runtime.parser.node.ASTExpression.value(ASTExpression.java:51)
at org.apache.velocity.runtime.parser.node.ASTSetDirective.render(ASTSetDirective.java:95)
at org.apache.velocity.runtime.parser.node.ASTBlock.render(ASTBlock.java:55)
at org.apache.velocity.runtime.directive.Foreach.render(Foreach.java:166)
at org.apache.velocity.runtime.parser.node.ASTDirective.render(ASTDirective.java:114)
at org.apache.velocity.runtime.parser.node.SimpleNode.render(SimpleNode.java:230)
at org.apache.velocity.Template.merge(Template.java:256)
at org.apache.velocity.tools.view.servlet.VelocityViewServlet.mergeTemplate(Unknown Source)
at org.apache.velocity.tools.view.servlet.VelocityViewServlet.doRequest(Unknown Source)
at org.apache.velocity.tools.view.servlet.VelocityViewServlet.doGet(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:115)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:92)
at com.caucho.server.dispatch.ServletFilterChain.doFilter(ServletFilterChain.java:99)
at com.caucho.server.webapp.DispatchFilterChain.doFilter(DispatchFilterChain.java:115)
at com.caucho.server.dispatch.ServletInvocation.service(ServletInvocation.java:208)
at com.caucho.server.webapp.RequestDispatcherImpl.forward(RequestDispatcherImpl.java:268)
at com.caucho.server.webapp.RequestDispatcherImpl.forward(RequestDispatcherImpl.java:106)
at org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1056)
at org.apache.struts.tiles.TilesRequestProcessor.doForward(TilesRequestProcessor.java:261)
at org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:388)
at org.apache.struts.tiles.TilesRequestProcessor.processForwardConfig(TilesRequestProcessor.java:316)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:231)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1164)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:397)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:115)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:92)
at com.caucho.server.dispatch.ServletFilterChain.doFilter(ServletFilterChain.java:99)
at com.legalmatch.hibernate.HibernateThreadFilter.doFilter(HibernateThreadFilter.java:29)
at com.caucho.server.dispatch.FilterFilterChain.doFilter(FilterFilterChain.java:70)
at com.caucho.server.webapp.WebAppFilterChain.doFilter(WebAppFilterChain.java:163)
at com.caucho.server.dispatch.ServletInvocation.service(ServletInvocation.java:208)
at com.caucho.server.hmux.HmuxRequest.handleRequest(HmuxRequest.java:396)
at com.caucho.server.port.TcpConnection.run(TcpConnection.java:363)
at com.caucho.util.ThreadPool.runTasks(ThreadPool.java:490)
at com.caucho.util.ThreadPool.run(ThreadPool.java:423)
at java.lang.Thread.run(Thread.java:534)

Try:
java.math.BigInteger rc = (java.math.BigInteger)hSession.createSQLQuery(
"SELECT COUNT(DISTINCT ac_cc_id) as cnt"+
" FROM attorney_case ac "+
" JOIN consumer_case_category_xref cccx ON ac.ac_cc_id=cccx.cccx_cc_id "+
" JOIN category_to_users catmap ON cccx.cccx_cat_id=catmap.catmap_cat_id AND catmap.catmap_catsup_id=? "+
" where ac.ac_usr_id=? and ac.ac_hide=0")
.addScalar("cnt",Hibernate.BIG_INTEGER)
.setLong( 0, superCat.getId() )
.setLong( 1, this.getId() )
.setCacheable(true)
.uniqueResult();

Excellent! That works. I also chagned it to a normal Integer. I was only using a BigDecimal since that what it was returning. Thank you!