JPA getResultList() returns BigInteger for MySQL but Integer for Microsoft SQL server

Question

I have following method:

Query q = getEntityManager().createNativeQuery("SELECT COUNT(1) FROM table1 WHERE column = :column_id " + "UNION " + "SELECT COUNT(1) FROM table2 WHERE column = :column_id");
q.setParameter("column_id", column_id);

When I want to get the list of counts (which will be 2 rows), I perform this action:

List<BigInteger> counts = (List<BigInteger>) q.getResultList();

This is working fine in MySQL. But as soon as I connect to MS SQL server, I'm getting a List of Integer objects:

List<Integer>

Any idea why there is a difference?

Solution

JPA defines the return types for JPQL queries, but for native SQL queries you get whatever the database returns. That is kind of the point with native SQL queries.

Change your code to Number,

List<Number> counts = (List<Number>) q.getResultList();
long count = counts.get(0).longValue();

Comments

[Jochen Hebbrecht]

Hmmm ... but isn't JPA just all about separating database layers from processing layers? How can I get a uniform result? I just want to use BigInteger OR Integer. I don't care which one, just 1 :-)

[tagtraeumer]

as AhamedMusatafaM said, did you try Query q = getEntityManager().createNativeQuery("SELECT COUNT(1) FROM table1 WHERE column = :column_id " + "UNION " + "SELECT COUNT(1) FROM table2 WHERE column = :column_id",Integer.class);

[Jochen Hebbrecht]

tagtraeumer: if I try that, I'm getting: javax.persistence.PersistenceException: org.hibernate.MappingException: Unknown entity: java.lang.Integer

[Jochen Hebbrecht]

Thanks James, your solution is the fix! :-)