oracle解决in999以及list大数据量问题

电玩女神 2022-10-29 05:16 10阅读 0赞

List<Map<String, Object>> list = SQLUtil.queryByIdsPlus(session, excleVins, “ST\_VIN”, sqlManager.getSql(“queryVins”));

--------------------

public class SQLUtil \{

public final static int ORACLE_SQL_IN_ERROR_LIMIT = 999;
    public final static int SQL_SPLIT_COUNT = 30000;

public static List<Map<String, Object>> queryByIdsPlus(Session session, List ids, String colName, String mainSql) \{  
List<Map<String, Object>> resultList = new ArrayList<>();  
if(ids.size() > SQL\_SPLIT\_COUNT) \{  
List<List> splitList = splitSetByFixedSize(ids, SQL\_SPLIT\_COUNT);  
for (List idsList : splitList) \{  
resultList.addAll(queryByIds(session, idsList, colName, mainSql));  
\}  
\}else\{  
resultList = queryByIds(session, ids, colName, mainSql);  
\}

return resultList;
    }

--------------------

public static List<List> splitSetByFixedSize(List list, int size)\{  
String\[\] array = list.toArray(new String\[\]\{\});  
int length = array.length;  
int splitNum = length % size == 0? length / size : length / size + 1;

List<List<String>> resultList = new ArrayList<List<String>>();
        for(int i=1; i<=splitNum; i++){
            List<String> newList = new ArrayList<>();
            int end = (length - (i-1)*size) > size? size : (length - (i-1)*size);
            for(int j=0; j<end; j++){
                newList.add(array[j+size*(i-1)]);
            }
            resultList.add(newList);
        }
    
        return resultList;
    }

--------------------

public static List<Map<String, Object>> queryByIds(Session session, List ids, String colName, String mainSql) \{  
List<Map<String, Object>> ret = new ArrayList<>();

if(CollectionUtils.isEmpty(ids)){
            return ret;
        }
    
        Set<String> idsSet=new HashSet<>();
        idsSet.addAll(ids);
        StringBuilder condition = new StringBuilder();
        if(idsSet.size() > ORACLE_SQL_IN_ERROR_LIMIT){
            List<Set<String>> splitIdsList = SQLUtil.splitSetByFixedSize(idsSet, ORACLE_SQL_IN_ERROR_LIMIT);
            for(Set<String> splitIds : splitIdsList){
                if(condition.length() > 0){
                    condition.append(" OR").append(" "+colName+" IN ('"+ StringUtils.join(splitIds.toArray(), "','")+"')");
                }else{
                    condition.append(" "+colName+" IN ('"+StringUtils.join(splitIds.toArray(), "','")+"')");
                }
            }
            condition.insert(0, " AND (").append(")");
    
        }else{
            condition.append(" AND "+colName+" IN ('"+StringUtils.join(idsSet.toArray(), "','")+"')");
        }
    
        StringBuilder sb = new StringBuilder();
        sb.append(mainSql);
    
        if(condition.length() > 0){
            sb.append(condition);
            ret = session.getJdbcTemplate().queryForList(sb.toString());
        }
    
        return ret;
    }
    }

--------------------