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

The problem that you are running into is that your OracleDialect adds a column to its selected ResultSet. It wraps the query that you are running as discussed in SternK's answer.

If you were using the Hibernate SessionFactory and the Session interfaces, then the function that you would be looking for would be the "addScalar" method. Unfortunately, there doesn't seem to be an implementation in pure JPA (see the question asked here: Does JPA have an equivalent to Hibernate SQLQuery.addScalar()?).

I would expect your current implementation to work just fine in DB2, H2, HSQL, Postgres, MySQL (and a few other DB engines). However, in Oracle, it adds a row-number column to the ResultSet which means that Hibernate gets 2 columns from the ResultSet. Hibernate does not implement any query parsing in this case, which means that it simply parses the ResultSet into your List. Since it gets 2 values, it converts them into an Object[] rather than a BigDecimal.

As a caveat, relying on the JDBC driver to provide the expected-data-type is a bit dangerous, since Hibernate will ask the JDBC driver which data-type it suggests. In this case, it suggests a BigDecimal, but under certain conditions and certain implementations would be allowed to return a Double or some other type.

You have a couple options then.

1. You can modify your oracle-dialect (as SternK) suggests. This will take advantage of an alternate oracle-paging implementation.

2. If you are not opposed to having hibnerate-specific aspects in your JPA implementation, then you can take advantage of additional hibernate functions that are not offered in the JPA standard. (See the following code...)

   List<BigDecimal> results = entitymanager.createNativeQuery("select distinct id from ... group by ... having ...")
           .unwrap(org.hibernate.query.NativeQuery.class)
           .addScalar("id", BigDecimalType.INSTANCE)
           .getResultList();
   System.out.println(results);
   

This does have the advantage of explicitly telling hibnerate, that you are only interested in the "id" column of your ResultSet, and that hibernate needs to explicitly convert to the returned object to a BigDecimal, should the JDBC-driver decide that a different type would be more appropriate as a default.

The root cause of your problem in the way how the pagination implemented in your hibernate oracle dialect.

There are two cases:

1. When we have setFirstResult(0) the following sql will be generated:

-- setMaxResults(5).setFirstResult(0)
select * from (
  select test_id from TST_MY_TEST -- this is your initial query
) 
where rownum <= 5;

As you can see, this query returns exactly the same columns list as your initial query, and therefore you do not have problem with this case.

2. When we set setFirstResult in not 0 value the following sql will be generated:

-- setMaxResults(5).setFirstResult(2)
select * from (
   select row_.*, rownum rownum_ 
   from (
      select test_id from TST_MY_TEST -- this is your initial query
   ) row_ 
   where rownum <= 5
) 
where rownum_ > 2

As you can see, this query returns the columns list with additional rownum_ column, and therefore you do have the problem with casting this result set to the BigDecimal.

Solution

If you use Oracle 12c R1 (12.1) or higher you can override this behavior in your dialect using new row limiting clause in this way:

import org.hibernate.dialect.Oracle12cDialect;
import org.hibernate.dialect.pagination.AbstractLimitHandler;
import org.hibernate.dialect.pagination.LimitHandler;
import org.hibernate.dialect.pagination.LimitHelper;
import org.hibernate.engine.spi.RowSelection;


public class MyOracleDialect extends Oracle12cDialect
{
   private static final AbstractLimitHandler LIMIT_HANDLER = new AbstractLimitHandler() {
      @Override
      public String processSql(String sql, RowSelection selection) {
         final boolean hasOffset = LimitHelper.hasFirstRow(selection);
         final StringBuilder pagingSelect = new StringBuilder(sql.length() + 50);
         pagingSelect.append(sql);
         
         /*
            see the documentation https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#BABHFGAA
            (Restrictions on the row_limiting_clause)
            You cannot specify this clause with the for_update_clause.
          */
         if (hasOffset) {
            pagingSelect.append(" OFFSET ? ROWS");
         }
         pagingSelect.append(" FETCH NEXT ? ROWS ONLY");
         return pagingSelect.toString();
      }

      @Override
      public boolean supportsLimit() {
         return true;
      }
   };

   public MyOracleDialect()
   {
   }
   
   @Override
   public LimitHandler getLimitHandler() {
      return LIMIT_HANDLER;
   }
}

and then use it.

<property name="hibernate.dialect">com.me.MyOracleDialect</property>

For my test data set for the following query:

NativeQuery query = session.createNativeQuery(
   "select test_id from TST_MY_TEST"
).setMaxResults(5).setFirstResult(2);

List<BigDecimal> results = query.getResultList();

I got:

Hibernate: 
/* dynamic native SQL query */
select test_id  from TST_MY_TEST
OFFSET ? ROWS FETCH NEXT ? ROWS ONLY

val = 3
val = 4
val = 5
val = 6
val = 7

P.S. See also HHH-12087

P.P.S I simplified my implementation of the AbstractLimitHandler by removing checking presents FOR UPDATE clause. I think we will not have nothing good in this case and with this checking.

For example for the following case:

NativeQuery query = session.createNativeQuery(
   "select test_id from TST_MY_TEST FOR UPDATE OF test_id"
).setMaxResults(5).setFirstResult(2);

hibernate (with Oracle12cDialect) will generate the following sql:

/* dynamic native SQL query */
select * from (
  select
     row_.*,
     rownum rownum_ 
  from (
     select test_id from TST_MY_TEST -- initial sql without FOR UPDATE clause
  ) row_ 
  where rownum <= 5
) 
where rownum_ > 2
FOR UPDATE OF test_id -- moved for_update_clause

As you can see, hibernate tries to fix query by moving FOR UPDATE to the end of the query. But anyway, we will get:

ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

I've simulated your consult and everything works fine. I've used DataJpaTest to instance entityManager for me, h2 memory database and JUnit 5 to run the test. See below:

@Test
public void shouldGetListOfSalaryPaginated() {
    // given
    Person alex = new Person("alex");
    alex.setSalary(BigDecimal.valueOf(3305.33));
    Person john = new Person("john");
    john.setSalary(BigDecimal.valueOf(33054.10));
    Person ana = new Person("ana");
    ana.setSalary(BigDecimal.valueOf(1223));
    
    entityManager.persist(alex);
    entityManager.persist(john);
    entityManager.persist(ana);
    entityManager.flush();
    entityManager.clear();

    // when
    List<BigDecimal> found = entityManager.createNativeQuery("SELECT salary FROM person").setMaxResults(2).setFirstResult(2*1).getResultList();

    // then
    Assertions.assertEquals(found.size(), 1);
    Assertions.assertEquals(found.get(0).longValue(), 1223L);
}

I suggest that you review your native query. It's preferable that you use Criteria API instead and let native queries for extreme cases like complex consults.

Update

After the author posted the project, I could reproduce the problem and it was related to the oracle dialect. For unknown reason the query which is running for the second call is: select * from ( select row_.*, rownum rownum_ from ( SELECT c.SHOP_ID FROM CUSTOMER c ) row_ where rownum <= ?) where rownum_ > ?, and that's why this is generating a bug, because it's querying 2 columns instead of only one. The undesired one is this rownum. For other dialects there is no such problem.

I suggest you try other oracle dialect version and whether none of them work, my final tip is try to do the pagination yourself.