CODEWITHSUNDEEP
javaoracle-databasehibernatespring-data-jpa
Deniss M.
Deniss M.

Reputation: 4050

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.

Upvotes: 13

Views: 11620

Answers (2)

Robert Niestroj
Robert Niestroj

Reputation: 16131

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);)
  }

Upvotes: 21

Thierry
Thierry

Reputation: 5440

You can split this operation in 2 steps :

  1. Insert your list of IDs in a table (a temporary table for example)
  2. Change your in statement with lot of values into a in statement with a subselect on this new (temporary) table.

Upvotes: 3

Related Questions