JpaSystemException using Criteria, DB2zDialect and Pagination

  Kiến thức lập trình

so I have the following setup of a spring-boot application:

org.springframework.boot:spring-boot-starter:3.2.4
com.ibm.db2:jcc:11.5.8.0
java.version=21
org.hibernate.orm:hibernate-core:6.4.4.Final

connecting to a DB2 z/OS database (DSN12015) v12.015.

I have the following dialect:

import org.hibernate.dialect.DB2zDialect;
import org.hibernate.dialect.pagination.LegacyDB2LimitHandler;
import org.hibernate.dialect.pagination.LimitHandler;

public class Db2zOsDialect extends DB2zDialect {
  @Override
  public SequenceInformationExtractor getSequenceInformationExtractor() {
    return LegacyDb2zOsSequenceInformationExtractor.INSTANCE;
  }

  @Override
  public LimitHandler getLimitHandler() {
    return LegacyDB2LimitHandler.INSTANCE;
  }
}

I have the following entity:

@Table(name = ""KEY"")
public class Key {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "ID", unique = true, nullable = false)
  private Long id;

  @Column(name = ""VALUE"", nullable = false, length = 100)
  private String value;

  ...
}

And the following repository Repository:

@Repository
interface KeyRepository extends JpaRepository<Key, Long>, JpaSpecificationExecutor<Key> {}

I now want to read it using specification:

final String toSearchFor = "%foo%"
Specification<User> spec = (root, query, criteriaBuilder) -> criteriaBuilder.like(root.get("value"), toSearchFor);
final PageRequest pageRequest = PageRequest.of(0, 100, Sort.by(Direction.DESC, "id"));
repo.findAll(specification, pageRequest)

Using exactly the same code without PageRequest works perfectly. But as soon as the pagination is there it fails.
I discovered since we updated from spring-boot 2.X the query changed internally executed by JPA and hibernate:

Original-Query:

select key0_.ID                        as id1_0_,
       key0_."VALUE"                   as value_2_0_,
       ...
from MY_SCHEMA."KEY" key0_
where key0_."VALUE" like '%foo%'
order by key0_.ID desc
fetch first 100 rows only;

New-Query:

select k1_0.ID,
       k1_0."VALUE",
       ...
from MY_SCHEMA."KEY" k1_0
where k1_0."VALUE" like '%foo%'
order by k1_0.ID desc
offset 0 rows fetch first 100 rows only;

Both queries run if I execute them via SQL tool against the database

What I found out so far that it is not going to use the Db2zOsDialect and instead does something else which is including offset. This happens in the constructor of DeferredResultSetAccess:

hibernate:DeferredResultSetAccess:constructor

Exception:

org.hibernate.exception.GenericJDBCException: JDBC exception executing SQL [select k1_0.ID, k1_0."VALUE", ... from MY_SCHEMA."KEY" k1_0 where k1_0."VALUE" like '%foo%' order by k1_0.ID desc offset 0 rows fetch first 100 rows only] [DB2 SQL Error: SQLCODE=-4743, SQLSTATE=56038, SQLERRMC=null, DRIVER=4.32.28] [n/a]

Error Logs:

2024-04-04T09:36:41.769+02:00 ERROR 22710 --- [nio-9080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : DB2 SQL Error: SQLCODE=-4743, SQLSTATE=56038, SQLERRMC=null, DRIVER=4.32.28
2024-04-04T09:36:41.769+02:00 ERROR 22710 --- [nio-9080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : DB2 SQL Error: SQLCODE=-516, SQLSTATE=26501, SQLERRMC=null, DRIVER=4.32.28
2024-04-04T09:36:41.769+02:00 ERROR 22710 --- [nio-9080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLH200C1, DRIVER=4.32.28

Questions:

  • Why is “offset” used and not my LimitHandler
  • I know there were updates to “aliases” on hibernate 6, but why would it have an effect
  • Is this an internal JPA/Hibernate/JDBC problem?

LEAVE A COMMENT