The root cause of your problem in the way how the pagination implemented in your hibernate oracle dialect.
There are two cases:
- 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.
- 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.
