Search code examples
javaoracle-databasehibernatepaginationentitymanager

EntityManager.createNativeQuery returning list of objects instead of list of BigDecimal when using Pagination

I am trying to use Pagination with EntityManager.createNativeQuery(). Below is the skeleton code that I am using:

var query = em.createNativeQuery("select distinct id from ... group by ... having ...");
List<BigDecimal> results = query
        .setMaxResults(pageSize)
        .setFirstResult(pageNumber * pageSize)
        .getResultList();

When pageNumber is 0 (first page), I get the expected List of BigDecimals:

But as soon as pageNumber > 0 (example, second page), I get a List of Objects, and each object in this list seems to contain two BigDecimals, the first of which contains the value from the db, and the second BigDecimal seems to be the position of this row.

and obviously I get this exception

java.lang.ClassCastException: class [Ljava.lang.Object; cannot be cast to class java.math.BigDecimal

Can someone please explain this discrepancy, and how this can be fixed to always return a List of BigDecimals? Thank you.

Update-1 : I have created a sample project to reproduce this issue. I was able to reproduce this issue only with an Oracle database. With H2 database, it worked fine, and I consistently got a list of BigDecimals irrelevant of the page number.

Update-2 : I have also created a sample project with H2 where it works without this issue.

Solution

  • After a lot of trails with different versions of different spring libraries, I was finally able to figure out the issue. In one of my attempts, the issue seems to have disappeared, as soon as I updated the spring-data-commons library from v2.1.5.RELEASE to v2.1.6.RELEASE. I looked up the changelog of this release, and this bug, which is related to this bug in spring-data-commons, is the root cause of this issue. I was able to fix the issue after upgrading the spring-data-commons library.