JPA and “ORA-01795: maximum number of expressions in a list is 1000 error” workaround

Recently I worked on a defect which is caused by than 1000 parameter in IN sql.

Some data mapping frameworks provides solution for above by default, but unfortunately, Hibernate (most popular JPA provider) does not yet.

One way to resolve it is to split data in batch. Suppose you are trying to access 3000 records. Instead of giving 3000 parameter, give parameters in 1000 chunk.

If the number of parameterized values for the IN/NOT IN clause exceeds 1000, the subquery should be divided into sets according to the maximum limit of 1000, joined by the OR/AND operator.

error snapshot

How it was written before

List<Document> documentList = documentRepository.getDocument(dcnList);

After refectoring:-

int maxSqlSize = 1000;
List<List<Document>> documentLists = new ArrayList<>();
for (int i = 0; i < dcnList.size(); i += maxSqlSize ) {
  log.info(("chunk " + i));
  List<String> nextList = dcnList.subList(i, Math.min(dcnList.size(), i + maxSqlSize));
  documentLists.add(documentRepository.getDocumentByDocCtlNbrIn(nextList));
}
List<DocumentPO> documentPOList = documentLists.stream().flatMap(Collection::stream).collect(Collectors.toList());