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

Question

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 1

You can partition your list of clientIDs into 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);)
  }