title: How best to retrieve result of SELECT COUNT(*) from SQL query in Java/JDBC - Long? BigInteger?
body: <p>I'm using Hibernate but doing a simple SQLQuery, so I think this boils down to a basic JDBC question.  My production app runs on MySQL but my test cases use an in memory HSQLDB.  I find that a SELECT COUNT operation returns BigInteger from MySQL but Long from HSQLDB.</p>
  
  <p>MySQL 5.5.22
  HSQLDB 2.2.5</p>
  
  <p>The code I've come up with is:</p>
  
  <pre><code>SQLQuery tq = session.createSQLQuery(
      "SELECT COUNT(*) AS count FROM calendar_month WHERE date = :date");
  tq.setDate("date", eachDate);
  Object countobj = tq.list().get(0);
  int count = (countobj instanceof BigInteger) ?
      ((BigInteger)countobj).intValue() : ((Long)countobj).intValue();
  </code></pre>
  
  <p>This problem of the return type negates answers to other SO questions such as <a href="https://stackoverflow.com/questions/2559786/getting-count-using-createsqlquery-in-hibernate">getting count(*) using createSQLQuery in hibernate?</a> where the advice is to use <code>setResultTransformer</code> to map the return value into a bean.  The bean must have a type of either BigInteger or Long, and fails if the type is not correct.</p>
  
  <p>I'm reluctant to use a cast operator on the 'COUNT(*) AS count' portion of my SQL for fear of database interoperability.  I realise I'm already using <code>createSQLQuery</code> so I'm already stepping outside the bounds of Hibernates attempts at database neutrality, but having had trouble before with the differences between MySQL and HSQLDB in terms of database constraints</p>
  
  <p>Any advice?</p>