0

Im using below query to fetch the data using hibernate IN clause,

select emp from Employee emp where emp.employeeParentCompanyDetails in (:empParentCompDetails)

TypedQuery<Employee> query = entityManager.createQuery(fetchQuery,Employee.class);
                query.setParameter("empParentCompDetails", <more than 650 params>);
                List<Employee> employeeDetailss = query.getResultList();

and method is annotation with

@Transactional(value = "JpaTxn", readOnly = true, timeout = 900)

Exception:

16:44:30.462 [main] DEBUG org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl

  • JDBC transaction marked for rollback-only (exception provided for stack trace)

java.lang.Exception: exception just for purpose of providing stack trace at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.markRollbackOnly(JdbcResourceLocalTransactionCoordinatorImpl.java:271) at org.hibernate.engine.transaction.internal.TransactionImpl.setRollbackOnly(TransactionImpl.java:143) at org.springframework.orm.jpa.JpaTransactionManager$JpaTransactionObject.setRollbackOnly(JpaTransactionManager.java:684)

Its working if I pass upto 550 ids in IN clause. So its due to transaction timeout?

Suggest me to fix this scenario.

Alien
  • 15,141
  • 6
  • 37
  • 57
Sooriya
  • 73
  • 1
  • 8
  • Underlying DB may also have limitation for IN([1000](https://stackoverflow.com/questions/4722220/sql-in-clause-1000-item-limit)/[2100 - num of params](https://stackoverflow.com/a/45082468/5070879) depending of vendor). Which RDBMS are you using? – Lukasz Szozda Aug 09 '20 at 11:40
  • its oracle.. but here if go more than 600 itself its throwing this error – Sooriya Aug 09 '20 at 12:13
  • You are getting such a big data from database with any pagination ? That whose design I think. A way is create a temporary table and do subquery https://stackoverflow.com/a/35309127/4207306 – Eklavya Aug 09 '20 at 20:04

0 Answers0