针对oracle中in操作超过1000个参数 会抛异常 处理实例

1.先贴一个工具类OracleSQUtils，此类目的是为了将数千个参数，按指定等分切片，最后用or进行拼接sql。

/** * 将千条参数切成等分拼sql查询，提高查询效率 * @author Fantasy */public class OracleSQUtils {    /**     * @param ids 存储多个id的List集合     * @param count 将多个id按count切成等分拼接     * @param field 数据库表对应字段     * @return 返回拼接语句 如 id in (1,2,3....1000) or id in (1001,1002,1003....2000) or ....      */    public static String getOracleSQLIn(List<?> ids, int count, String field) {        count = Math.min(count, 1000);        int len = ids.size();        int size = len % count;        if (size == 0) {            size = len / count;        } else {            size = (len / count) + 1;        }        StringBuilder builder = new StringBuilder();        for (int i = 0; i < size; i++) {            int fromIndex = i * count;            int toIndex = Math.min(fromIndex + count, len);            //System.out.println(ids.subList(fromIndex, toIndex));            String productId = StringUtils.defaultIfEmpty(StringUtils.join(ids.subList(fromIndex, toIndex), "','"), "");            if (i != 0) {                builder.append(" or ");            }            builder.append(field).append(" in ('").append(productId).append("')");        }        return StringUtils.defaultIfEmpty(builder.toString(), field + " in ('')");    }}

2.mybatis中sql的xml文件写法配置：

<select id="querySomeInfoByIds" resultType="HashMap" parameterType="HashMap">select dept_id,dept_name from dept awhere 1=1<!--优化前：基于oracle数据库做in查询时，下面的通常写法，当deptIds达到1000个以上时，oracle会报错 --><if test="deptIds!= null">    AND a.DEPT_ID IN    <foreach item="item" index="index" collection="deptIds" open="(" separator="," close=")">        #{item}       </foreach>  </if><!-- 优化后：将传入的多个id在业务代码中按适当等分拼接好传入mybatis文件中，提升查询效率和避开超过1000个id时oracle数据库抛异常 -->and ( ${deptIds} )<!-其它普通参数-><if test="param!= null and param!= ''">    AND a.param =#{param}</if>....</select>

3.业务代码处理参数如下：

//deptIdList 中存储数千个deptIdString deptIds=OracleSQUtils.getOracleSQLIn(deptIdList,1000,"a.DEPT_ID");HashMap<String,Object> params=new HashMap<String,Object>();params.put("deptIds",deptIds);List<HashMap<String,Object>> resultList=deptService.querySomeInfoByIds(params);