I got burned by Hibernate the other day. This code throws a ClassCastException; can you see what the trouble might be?

return (Integer) session.createSQLQuery(
  "select count(*) " +
  "from trades " +
  "where date >= :from and date < :to")     
  .setParameter("from", converter.toString(from))     
  .setParameter("to", converter.toString(to))     
  .uniqueResult();

Under the covers Hibernate infers the result of the query as type BigInteger, thus the ClassCastException. It’s fixed by declaring the type of the returned value using addScalar():

return (Integer) session.createSQLQuery(
  "select count(*) c " +
  "from trades " +
  "where date >= :from and date < :to")     
  .addScalar("c", new IntegerType())     
  .setParameter("from", converter.toString(from))         
  .setParameter("to", converter.toString(to))     
  .uniqueResult();

I suppose a Hibernate guru would tell me to use HQL or some other sub-API (not sure if it would help in this case), but for such a simple query I would like to have the library be error-proof.

Plug: My friend Brian’s jDBI (an alternative to Hibernate) avoids problems like the above by providing type-safe return values.