mybatis in查詢條件過長的解決
方法1：分次查詢，將參數且分割成多個短的查詢后合并
代碼：
|
1
2
3
4
5
6
7
8
|
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等分方法方法：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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
|
1
2
3
4
5
6
7
8
9
10
|
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邏輯：
|
1
|
ID IN（ids[0],ids[1]+...+ids[998]）OR ID IN (ids[999],ids[1000],...ids[max]) |
mybatis大于1000的in查詢的解決
之前公司一位同事寫的方法：
|
1
2
3
4
5
6
7
8
9
|
<select id="getByDirIds" parameterType="string" resultMap="dirDocLinkMap"> SELECT <include refid="columns"/> FROM KM_DIR_DOC_LINK T WHERE T.DIR_ID IN <foreach collection="array" index="index" open="(" close=")" item="item" separator=","> <if test="(index % 1000) == 999">NULL) OR T.DIR_ID IN (</if>#{item} </foreach> </select> |
但是隨著數據量增加，發現大于2000這種方法會報錯；
論證如下
解決辦法
|
1
2
3
|
<foreach collection="array" item="item" index="index" open="(" close=")" separator=","> <if test="(index % 999) == 998"> NULL ) OR DOC.ID IN (</if>#{item} </foreach> |
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持服務器之家。
原文鏈接：https://blog.csdn.net/Mr_ye931/article/details/106102695
