Native query count does not return the same type in SQL Server and H2

Question

Hello,

I have a native consultation that makes a simple count of records from a table:

public Long contar() {
    String sql "SELECT count(*) FROM Order";
    Query query = query.createNativeQuery(sql);
    // return...
}

In the system, we use SQL Server in production and for integration tests we use the H2 database.

However, it appears that Hibernate does not have a return data type pattern count native between these two banks. In H2 is returned one BigInteger and in Sql Server is returned a Integer. In queries using JPQL, the type returned is always Long, independent of the database.

So if I try H2:

return ((Integer) query.getSingleResult()).longValue();

I get:

java.lang.Classcastexception: java.math.Biginteger cannot be cast to java.lang.Integer

If I try to use the BigInteger, H2 accepts and tests pass. But when trying this on Sql Server:

return ((BigInteger) query.getSingleResult()).longValue();

I get the error:

java.lang.Classcastexception: java.lang.Integer cannot be cast to java.math.Biginteger

I tried to use Long:

return (Long) query.getSingleResult();

But I get the bug (as expected):

java.lang.Classcastexception: java.lang.Integer cannot be cast to java.lang.Long

So what would be the correct way to convert the value of the counter to work in the two databases?

Answer 1

I remembered that I had this problem in a software I have worked on, that instead of H2 I used Hsqldb, and I consulted my former colleagues to remember the solution.

It is possible to use Number and convert the result to Long:

return ((Number) query.getSingleResult()).longValue();