mybatis xml中使用in的sql长度的解决方法

原来： 入参为List<Long>

WHERE AMBIENT_TEMPERATURE IS NOT NULL
<if test=”deviceId.size()>0″ >
AND DEVICE_ID in
<foreach collection=”deviceId” item=”deviceId” open=”(” separator=”,” close=”)”>
#{deviceId}
</foreach>
</if>

效果 where xxx and DEVICE_ID in (1,2,3,4,5) 但是in后边的数据超过1000会报错

改为： 入参为List<List<Long>>

WHERE AMBIENT_TEMPERATURE IS NOT NULL
<if test=”deviceId.size()>0″ >
and
<foreach collection=”deviceId” item=”item” open=”(” separator=”or” close=”)”>
DEVICE_ID in
<foreach collection=”item” item=”item2″ open=”(” separator=”,” close=”)”>
#{item2}
</foreach>
</foreach>
</if>

效果 where xxx and (DEVICE_ID in (1,2,3,4,5 …. ,999) or DEVICE_ID in (1000,1001,….,1040))

还有一种效率低的方法： 入参为List<Long>

WHERE AMBIENT_TEMPERATURE IS NOT NULL
<if test=”deviceId.size()>0″ >
and
<foreach item=”deviceId” index=”index” collection=”deviceId” open=”(” separator=”or” close=”)”>
DEVICE_ID in #{deviceId}
</foreach>
</if>

效果 where xxx and (DEVICE_ID in 1 or DEVICE_ID in 2 or DEVICE_ID in 3 or DEVICE_ID in 4 )

将List<Long> 划分为List<List<Long>> ，调用如：List<List<Long>> new_deviceIdList = splitList(deviceIdList, 999);

/**
* 按指定大小，分隔集合，将集合按规定个数分为n个部分
* @param list
* @param len
* @return
*/
private static List<List<Long>> splitList(List<Long> list, int len) {
if (list == null || list.size() == 0 || len < 1) {
return null;
}
List<List<Long>> result = new ArrayList<List<Long>>();
int size = list.size();
int count = (size + len – 1) / len;
for (int i = 0; i < count; i++) {
List<Long> subList = list.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1)));
result.add(subList);
}
return result;
}