针对oracle执行select in语句超过1000条报错的解决办法

最新推荐文章于 2024-05-27 15:20:32 发布

赫双炏

最新推荐文章于 2024-05-27 15:20:32 发布

阅读量1.3k

点赞数

分类专栏： java 文章标签： sql java 数据库

本文链接：https://blog.csdn.net/heliangb46/article/details/126500095

版权

java 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

/  例：
//  List<YourResult> allList = mapperBatchExecuter.selectInExecute(queryList, k -> {
//      return yourService.listBy***(k);
//  });
//本方法用于执行oracle数据库查询语句中in的数据量超过1000的查询，
//实际为语法糖，将select in的语句分块执行
    public <U, T> List<T> selectInExecute(List<U> objectList, Function<List<U>, List<T>> function) {
        List<T> allResultList = null;
        Stack<U> queryStack = new Stack<U>();
        List<U> tempQueryList = new ArrayList<>();
        int i = 0;
        boolean lastOne = false;
        if (CollectionUtils.isNotEmpty(objectList)) {
            allResultList = new ArrayList<>();
            queryStack.addAll(objectList);
            while (true) {
                if (!queryStack.isEmpty()) {
                    i++;
                    tempQueryList.add(queryStack.pop());
                } else {
                    lastOne = true;
                }
                if (i > 998 || lastOne) {
                    List<T> tempResultList = function.apply(tempQueryList);
                    if (CollectionUtils.isNotEmpty(tempResultList)) {
                        allResultList.addAll(tempResultList);
                    }
                    tempQueryList.clear();
                    i = 0;
                }
                if (lastOne) {
                    lastOne = false;
                    break;
                }
            }
        }
        return allResultList;
    }

//   传递一个带对象，里面带有一个inList参数的批量查询
    public <U, T> List<T> selectInWithParamExecute(U paramObject, String inListParamName, Function<U, List<T>> function)
        throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchMethodException, SecurityException {
        List<T> allResultList = null;
        Stack<String> queryStack = new Stack<String>();
        List<String> tempQueryList = new ArrayList<>();
        Class<? extends Object> objClazz = paramObject.getClass();
        String upperName = CommonUtils.firstCharToUpperCase(inListParamName);
        String setterName = "set" + upperName;
        String getterName = CommonUtils.firstCharSToG(setterName);
        @SuppressWarnings("unchecked")
        List<String> objectList = (List<String>)objClazz.getMethod(getterName).invoke(paramObject);
        Method setterMethod = objClazz.getMethod(setterName, List.class);
        int i = 0;
        boolean lastOne = false;
        if (CollectionUtils.isNotEmpty(objectList)) {
            allResultList = new ArrayList<>();
            queryStack.addAll(objectList);
            while (true) {
                if (!queryStack.isEmpty()) {
                    i++;
                    tempQueryList.add(queryStack.pop());
                } else {
                    lastOne = true;
                }
                if (i > 998 || lastOne) {
                    setterMethod.invoke(paramObject, tempQueryList);
                    List<T> tempResultList = function.apply(paramObject);
                    if (CollectionUtils.isNotEmpty(tempResultList)) {
                        allResultList.addAll(tempResultList);
                    }
                    tempQueryList.clear();
                    i = 0;
                }
                if (lastOne) {
                    lastOne = false;
                    break;
                }
            }
        }
        return allResultList;
    }