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