Search code examples
spring-boothibernatespring-data-jpadb2dialect

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

I am using spring data JPA with DB2. My first set of record is coming (i.e., index 0). But when I do the next, it is giving me an 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 DataAccessException.

Pageable pageable = new PageRequest(1, 10, Direction.ASC, "messageId");

The error occurs when using PageRequest with an index > 0. This leads to 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
Solution

  • 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()