oracle in 超过1000解决方案
1.with as table建立临时表子查询
with t as (
select '1' as id from dual
union all
select '2' as id from dual
·····
)
select i.*
from table1 i
where i.id in (
select t.id from t
)
2.使用or
方式1:
select * from table where id in (1, 2, ..., 1000) or id in(1001, ....., 1999)
后台使用
public String createSql(String col ,List<Integer> umIds) {
StringBuffer sb = new StringBuffer();
sb.append(col + " in(");
int size = umIds.size();
int index = 0;
for (int i = 0; i < size; i++) {
index++;
if (index == 999) {
index = 0;
sb.append(")");
sb.append(" or " + col + " in ( ");
}
if (index + 1 == 999 || size - 1 == i) {
sb.append("" + umIds.get(i) + "");
} else {
sb.append("" + umIds.get(i) + ",");
}
}
sb.append(")");
return sb.toString();
}
方式2:
select * from table where id =1 or id =2 or·····
超过1000报错:
<isNotEmpty property="ids" prepend="and">
t.id in
<iterate open="(" close=")" conjunction="," property="ids">
#ids[]#
</iterate>
</isNotEmpty>
更正:
<isNotEmpty property="ids" prepend="and">
<iterate open="(" close=")" conjunction=" or " property="ids">
t.cltno = #ids[]#
</iterate>
</isNotEmpty>
前面是比较老的ibatis写法,在mybatis3里面你可以这样做
<if test="scope.ids!= null and scope.ids.size() > 0">
and (D.PAY_CLT_NO in
<foreach collection="scope.ids" open="(" close=")" separator="," item="id" index="idx">
<if test="idx != 0 and idx % 999 == 0 and idx != scope.ids.size() - 1">
#{id}) or D.PAY_CLT_NO in (#{id}
</if>
<if test="idx == 0 or (idx % 999 != 0 or idx == scope.ids.size() - 1)">
#{id}
</if>
</foreach>
)
</if>
3.后台拼接查询,返回后重组list
传给后台参数LIST数组大小超过1000,截取查询两次,最后addAll();