When i am using it with db2 for pagination, my next page is giving error

Question

I am using spring data jpa with db2. My first set of record is coming. But when i do the next, it is giving me error. I guess it may be because of db2. When I use index 0 it is working fine. But when i use index 1 then it is giving data access exception.

On second page I am getting below error my pageable object

Pageable pageable = new PageRequest(2, 10, Direction.ASC, "messageId"), because of this 2(As it is mentioning that index should be 0), it is throwing the below error

com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=OF;??( [ DESC ASC NULLS RANGE CONCAT || / MICROSECONDS MICROSECOND, DRIVER=3.69.24

I am using Pageable pageable = new PageRequest(offsetValue - 1, 10, Direction.ASC, "messageId") and repository.findById(id, pageable). but when i debug it, it is giving me error. i am using spring data jpa — chetan agrawal, Nov 10 '16 at 08:18

score 10 · Answer 1 · answered Jan 03 '18 at 19:13

Following Abdul Gaffar Khan's advice worked for me. Here's what I had to change:

Added my exteded DB2 Dialect to my application.properties file:

spring.jpa.properties.hibernate.dialect=com.my.package.to.MyDB2Dialect
spring.jpa.database=db2

Extended the DB2Dialect:

import org.hibernate.dialect.DB2Dialect;
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 MyDB2Dialect extends DB2Dialect {

    private static final AbstractLimitHandler MY_LIMIT_HANDLER = new AbstractLimitHandler() {
        @Override
        public String processSql(String sql, RowSelection selection) {
            if (LimitHelper.hasFirstRow( selection )) {
                //nest the main query in an outer select
                return "select * from ( select inner2_.*, rownumber() over() as rownumber_ from ( "
                        + sql + " fetch first " + getMaxOrLimit( selection ) + " rows only ) as inner2_ ) as inner1_ where rownumber_ > "
                        + selection.getFirstRow() + " order by rownumber_";
            }
            return sql + " fetch first " + getMaxOrLimit( selection ) +  " rows only";
        }

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

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

        @Override
        public boolean supportsVariableLimit() {
            return false;
        }
    };

    @Override
    public boolean supportsSequences() {
        return false;
    }

    @Override
    public LimitHandler getLimitHandler() {
        return MY_LIMIT_HANDLER;
    }
}

Does anyone has any information to share, when this fix will be available for the already created issue? https://hibernate.atlassian.net/browse/HHH-13519 — Philipp Wirth, Oct 29 '19 at 11:58

score 2 · Accepted Answer · answered Dec 29 '16 at 07:31

2

Actually org.hibernate.dialect.DB2Dialect class generates invalid query that why its failing you have to correct query by overiding its method.

replace over( order by order of inner2_)  with over()

answered Dec 29 '16 at 07:31

Abdul Gaffar Khan

224
4
16

When i am using it with db2 for pagination, my next page is giving error

2 Answers2

Linked