Caused by: java.sql.SQLException: ORA-01795: 列表中的最大表达式数为 1000解决方案

Caused by: java.sql.SQLException: ORA-01795: 列表中的最大表达式数为 1000解决方案

Oracle查询语句中in list集合中最大不能超过1000，这是其语法限制，我们可以通过以下方法解决：

1.将List均分成n份

主要使用averageAssign函数将routeTypeList分成n等份，用lists集合装

public ResultVO list(){
        //1. 查询所有routeStep
        List<RouteStep> routeStepList = routeStepService.findAll();
        List<String> routeTypeList = routeStepList.stream()
                .map(e -> e.getRoute())
                .collect(Collectors.toList());
        int n = (routeTypeList.size() / 1000) + 1 ;
        System.out.println(n);
        List<List<String>> lists = averageAssign(routeTypeList, n);
        List<Route1> route1List = routeService.findByRouteIn(lists);
        route1List = route1List.stream().distinct().collect(Collectors.toList());
        ...
        ...
        }

/**
     * 将一个list均分成n个list,主要通过偏移量来实现的
     *
     * @param source
     * @return
     */

public static <T> List<List<T>> averageAssign(List<T> source, int n) {
        List<List<T>> result = new ArrayList<List<T>>();
        int remaider = source.size() % n;  //(先计算出余数)
        int number = source.size() / n;  //然后是商
        int offset = 0;//偏移量
        for (int i = 0; i < n; i++) {
            List<T> value = null;
            if (remaider > 0) {
                value = source.subList(i * number + offset, (i + 1) * number + offset + 1);
                remaider--;
                offset++;
            } else {
                value = source.subList(i * number + offset, (i + 1) * number + offset);
            }
            result.add(value);
        }
        return result;
    }

2.使用google guava对List进行分割

//使用guava对list进行分割
List<User> users = userService.findAll();
//按每50个一组分割
List<List<User>> parts = Lists.partition(users, 50);
parts.stream().forEach(list -> {
    process(list);
});

3.使用apache common collection

List<Integer> intList = Lists.newArrayList(1, 2, 3, 4, 5, 6, 7, 8);
List<List<Integer>> subs = ListUtils.partition(intList, 3);

4.java8 Stream 大数据量List分批处理

//按每3个一组分割
private static final Integer MAX_NUMBER = 3;

/**
* 计算切分次数
*/
private static Integer countStep(Integer size) {
	return (size + MAX_NUMBER - 1) / MAX_NUMBER;
}

public static void main(String[] args) {
      List<Integer> list = Arrays.asList(1, 2, 3, 4, 5, 6, 7);
      int limit = countStep(list.size());
      //方法一：使用流遍历操作
      List<List<Integer>> mglist = new ArrayList<>();
      Stream.iterate(0, n -> n + 1).limit(limit).forEach(i -> {
          mglist.add(list.stream().skip(i * MAX_NUMBER).limit(MAX_NUMBER).collect(Collectors.toList()));
      });

      System.out.println(mglist);

      //方法二：获取分割后的集合
      List<List<Integer>> splitList = Stream.iterate(0, n -> n + 1).limit(limit).parallel().map(a -> list.stream().skip(a * MAX_NUMBER).limit(MAX_NUMBER).parallel().collect(Collectors.toList())).collect(Collectors.toList());
      
      System.out.println(splitList);
}