What object type does Spring Hibernate Template execute method return for a counting query on Oracle?

Question

When run against and Oracle database, what is the runtime type of the object that the following Spring Hibernate Template (Spring 2.5 and Hibernate 3.3.2GA) code returns where the SQL query is a counting query like select count(*) from table?

 String sql = "select count(*) from table";
 BigDecimal count = (BigDecimal) hibernateTemplate.execute(
   new HibernateCallback() { 
    public Object doInHibernate(Session session) throws HibernateException {
     SQLQuery query = session.createSQLQuery(sql);
     return (BigDecimal) query.uniqueResult();
    }});
 return count;

This code throws the following exception:

javax.ejb.EJBException: EJB Exception: : java.lang.ClassCastException: java.math.BigDecimal cannot be cast to [Ljava.lang.Object;
    at org.hibernate.cache.StandardQueryCache.put(StandardQueryCache.java:83)
    at org.hibernate.loader.Loader.putResultInQueryCache(Loader.java:2185)
    at org.hibernate.loader.Loader.listUsingQueryCache(Loader.java:2129)
    at org.hibernate.loader.Loader.list(Loader.java:2087)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
    at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
    at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
    at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:804)
    at com.db.abstrack.dao.hibernate.RfqCdoUsDaoHibernate$1.doInHibernate(RfqCdoUsDaoHibernate.java:124)

Answer 1

How about

long value = ((Number)query.uniqueResult()).longValue();
return Long.valueOf(value);

This would work for all subclasses of Number like Long, Double, Biginteger or BigDecimal.

Answer 2

Turns out that the ClassCastException may be due to a bug in the Hibernate standard query cache.

Solution is to add a scalar to the query:

String sql = "select count(*) as result from table";
BigDecimal count = (BigDecimal) ht.execute(new HibernateCallback() {
    public Object doInHibernate(Session session)
            throws HibernateException {
        SQLQuery query = session.createSQLQuery(sql);
        // Add scalar to avoid bug in Hibernate query cache.
        query.addScalar("result", Hibernate.BIG_DECIMAL);
        return query.uniqueResult();
    }
});

References:

• ClassCastException with SQLQuery and setCacheable(true)
• Martin Schaaf's Blog: ClassCastException with SQLQuery and setCacheable(true)
• Caching a raw sql count with Hibernate and EhCache
• HHH-5163 Bug when applying a ResultTransformer on a cacheable projection based criteria
• ClassCastException when Hibernate tries to cache results using ResultTransformer