jpa hibernate rendered SQL in DB2 wrong?

Question

i think hibernate rendered wrong SQL in DB2? I am making a mistake certainly

my jpa code is:

public List<Gc1glcostctrmap> findByCriteria(
        Map<String, SortDirectionEnum> sortFields,
        Integer start,
        Integer length) throws Exception {

  CriteriaBuilder builder = entityManager.getCriteriaBuilder();
  CriteriaQuery<Gc1glcostctrmap> criteriaQuery = builder.createQuery(Gc1glcostctrmap.class);
 Root<Gc1glcostctrmap> root = criteriaQuery.from(Gc1glcostctrmap.class);

  int noDeleted = 0;
  criteriaQuery.select( root )
        .where( builder.equal(root.get(Gc1glcostctrmap_.g1011logdel),  noDeleted) );

  if ( Validator.isNotNull(sortFields) ) {
    criteriaQuery.orderBy(_orderBy(sortFields, builder, root));
  }

    // create query
  Query query = entityManager.createQuery(criteriaQuery);
  query.setFirstResult(start);
  query.setMaxResults(length);

    // execute query
  List<Gc1glcostctrmap> gc1glcostctrmaps = query.getResultList();
  return gc1glcostctrmaps;
}

and sqlshow is True, and for first page rendered:

select fields from BNGL.GC1GLCOSTCTRMAP gc1glcostc0_ where gc1glcostc0_.g1011logdel=0 order by gc1glcostc0_.g1136id asc fetch first 10 rows only

but for second page rendered:

select fields from ( select inner2_.*, rownumber() over( order by order of inner2_) as rownumber_ from 
( select fields from BNGL.GC1GLCOSTCTRMAP gc1glcostc0_  where gc1glcostc0_.g1011logdel=0 order by gc1glcostc0_.g1136id asc fetch first 20 rows only ) as inner2_ ) as inner1_  where rownumber_ > 10 
order by rownumber_

and Eventually error is:

2018-07-29 09:13:22.218  WARN 6684 --- [nio-8082-exec-9] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: -199, SQLState: 42601
2018-07-29 09:13:22.219 ERROR 6684 --- [nio-8082-exec-9] o.h.engine.jdbc.spi.SqlExceptionHelper   : DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=OF;( . AT MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE, DRIVER=3.68.61
2018-07-29 09:13:22.219  WARN 6684 --- [nio-8082-exec-9] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: -516, SQLState: 26501
2018-07-29 09:13:22.219 ERROR 6684 --- [nio-8082-exec-9] o.h.engine.jdbc.spi.SqlExceptionHelper   : DB2 SQL Error: SQLCODE=-516, SQLSTATE=26501, SQLERRMC=null, DRIVER=3.68.61
2018-07-29 09:13:22.219  WARN 6684 --- [nio-8082-exec-9] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: -514, SQLState: 26501
2018-07-29 09:13:22.219 ERROR 6684 --- [nio-8082-exec-9] o.h.engine.jdbc.spi.SqlExceptionHelper   : DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLH200C1, DRIVER=3.68.61
2018-07-29 09:13:22.228 ERROR 6684 --- [nio-8082-exec-9] ir.bmi.service.impl.MapTstServiceImpl    : Error occurred while calling findByCriteria().

while i commented `setFirstResult` and `setMaxResults` then it is Ok — , Jul 29 '18 at 07:28
Why is a simple question so hard for you to answer? So you invoke the query multiple times? 1,10 then 11, 20, etc? JPA Criteria does not do "paging". It simply allows you to generate a query to set first/max and thats it. The user defines "paging" — , Jul 31 '18 at 06:18
So you invoke the query multiple times? yes, 1,10 then 11, 20, etc? yes — , Aug 01 '18 at 06:55
but this code Works in **Oracle DB** and **MySql** ! I hoped the code was clear. in Other method I get count of records. — , Aug 01 '18 at 06:58
And Oracle and MySQL use totally different SQL syntax for limit/offset handling. As per https://stackoverflow.com/questions/21256121/db2-using-limit-and-offset#21257843 so if the generated SQL is invalid for your database then you report that as a bug on your chosen JPA provider — , Aug 01 '18 at 07:30

score 0 · Answer 1 · answered Nov 16 '22 at 10:07

0

You can use a custom Dialect, as the generated query in Hibernate DB2Dialect is not correct. Please refer this answer: db2-with-pagination

answered Nov 16 '22 at 10:07

mrinal singh rajput

1
1

jpa hibernate rendered SQL in DB2 wrong?

1 Answers1