Deniss M. Deniss M. - 28 days ago 8
Java Question

spring-data-jpa: ORA-01795: maximum number of expressions in a list is 1000

I'm using Spring Data JPA. I want to get the transactions of client.id's from a

List<String> clientIdList
. The problem is that I am passing a very big list and I get back an ORA-01795 error.

@Query(value = "SELECT TransactRepViewModel FROM TransactRepViewModel a WHERE a.clientId IN (?1) AND a.clDate BETWEEN ?2 and ?3", nativeQuery = true)
List<TransactRepViewModel> findByClientIdList(List<String> clientIdList, Date startDate, Date endDate) throws DataAccessException;


My client list comes from another table from another database via oracle and I cannot think of a way to solve this problem...

Edit: the list is dynamic, so it can return different amounts of id's. I also cannot create any additional tables in those databases. I have no such priviledges.

Answer

You can partition your list of clientIDs inot list of 999 elements and make multiple calls to the DB. You can use Apache Commons ListUtils to do the partitioning:

  List<TransactRepViewModel> result = new ArrayList<TransactRepViewModel>();
  final List<List<String>> partitions = ListUtils.partition(clientIdList, 999);
  for (List<String> partition : partitions) {
     result.addAll(yourRepo.findByClientIdList(partition, startDate, endDate);)
  }
Comments