欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Mybatis解决In查询条件过长的问题

程序员文章站 2024-03-06 21:29:50
...

方法1:分次查询,将参数且分割成多个短的查询后合并

代码:

			   int splitNum =(int) Math.ceil( (float) ids.length/999); //切片数量
			   List<String> itemIdList = new ArrayList<>(Arrays.asList(ids));
			   List<List<String>> splitList = averageAssign(itemIdList, splitNum);
			   for (List<String> list : splitList) {
				   param.put("itemIds",list);
				   List<Map<Object, Object>> itemStatisticsList = iProcessExtMapper.getItemStatisticsList(param);
				   result.addAll(itemStatisticsList);
			   }

将list分成N等分方法方法:

	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:xml文件中编写sql

i.id in			  
 <foreach collection="itemIds" index="index" item="item" open="(" close=")">
	<if test="index != 0">
		 <choose>
			<when test="index % 1000 == 999"> ) OR ID IN( </when>
			              <otherwise>,</otherwise>
		 </choose>
	</if>
   #{item}
 </foreach>	

sql逻辑:

ID IN(ids[0],ids[1]+...+ids[998])OR ID IN (ids[999],ids[1000],...ids[max])
相关标签: java mybatis