spring-data-jpa：ORA-01795：列表中的最大表达式数为1000 [英] spring-data-jpa: ORA-01795: maximum number of expressions in a list is 1000

问题描述

我正在使用Spring Data JPA。我想从 List< String>获取client.id的事务。 clientIdList 。问题是我传递了一个非常大的列表，我收到了一个ORA-01795错误。

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;

我的客户端列表来自另一个数据库来自oracle的另一个表，我想不出一个解决方法这个问题......

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

编辑：列表是动态的，因此它可以返回不同数量的id。我也无法在这些数据库中创建任何其他表。我没有这样的特权。

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.

推荐答案

您可以将clientID列表分成999个元素列表，并对DB进行多次调用。您可以使用 Apache Commons ListUtils进行分区：

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

这篇关于spring-data-jpa：ORA-01795：列表中的最大表达式数为1000的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！