Oracle数据库中IN参数个数超过1000的问题

    在oracle中，我们使用in方法查询记录的时候，如果in后面的参数个数超过1000个，那么会发生错误，JDBC会抛出“java.sql.SQLException: ORA-01795: 列表中的最大表达式数为 1000”这个异常。初步解决这个问题的思想是把参数列表分段，将SQL语句拼成“or XX in(.....) or XX in(.....)”形式。我写的QueryUtil类分别给出了SQL、HQL和Hibernate中解决这个问题的方法：

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Restrictions;

public class QueryUtil {
	/**
	 * 解决Oracle中in的参数列表长度超过1000的问题
	 * 
	 * @param length
	 *            每个分段的长度，
	 * @param paramsList
	 *            待拆分的参数列表
	 * @return 拆分后的分段列表
	 */
	public static <T> List<List<T>> splitInParams(int length, List<T> paramsList) {
		if (length<1||paramsList == null || paramsList.size() == 0)
			return null;
		int size = paramsList.size();
		List<List<T>> list = new ArrayList<List<T>>();
		int d = (int) Math.ceil(size / (length+0.0));
		for (int i = 0; i < d; i++) {
			int fromIndex = length * i;
			int toIndex = Math.min(fromIndex + length, size);
			list.add(paramsList.subList(fromIndex, toIndex));
		}
		return list;
	}
	
	
	/**
	 * 解决Oracle中in的参数列表长度超过1000的问题
	 * 返回拼接的SQL语句
	 * eg:
	 * 返回的SQL语句片段：XX IN (.......) OR XX IN (......)这样的形式
	 * @param paramName  需要进行in查询的查询参数的名称
	 * @param length     每个分段的长度，对于Oracle，一般设置为800-1000
	 * @param paramsList 待拆分的参数列表
	 * @return 拼接的SQL语句片段
	 */
	public static <T> String getSQLInParamsSplit(String paramName,int length,List<T> paramsList){
		if(length<1||paramName==null||paramsList==null||paramsList.size()==0)
			return null;
		List<List<T>> list = splitInParams(length, paramsList);
		StringBuilder sb = new StringBuilder();
		String temp = list.get(0).toString();
		//由于List的toString方法返回的是[....]形式，需要去掉开头和结尾的中括号
		sb.append(paramName).append(" IN ("+temp.subSequence(1,	temp.length()-1)+") ");
		int size = list.size();
		for(int i=1;i<size;i++){
			temp = list.get(i).toString();
			sb.append(" OR "+paramName+" IN ("+temp.subSequence(1,	temp.length()-1)+") ");
		}
		return sb.toString();
	}
	
	
	
	/**
	 * 解决Oracle中in的参数列表长度超过1000的问题
	 * 返回命名参数查询的HQL语句片段和对应的参数名和值的Map
	 * eg:
	 * 返回的HQL语句片段：id IN (:id0) OR id IN (:id1) OR id IN (:id2) OR id IN (:id3)，命名参数的名称是以paramName加上序号
	 * @param paramName  需要进行in查询的查询参数的名称
	 * @param length     每个分段的长度，对于Oracle，一般设置为800-1000
	 * @param paramsList 待拆分的参数列表
	 * @return 返回一个长度为2的Object数组，
	 * 第一个元素是还有in的HQL语句片段，
	 * 第二个元素是Map<String,Object>，其中String是参数名，Object是参数值的列表
	 */
	public static <T> Object[] getHQLInParamsSplit(String paramName,int length,List<T> paramsList){
		if(length<1||paramName==null||paramsList==null||paramsList.size()==0)
			return null;
		List<List<T>> list = splitInParams(length, paramsList);
	    Map<String, Object> tempParamsValues = new HashMap<String, Object>();
		StringBuilder sb = new StringBuilder();
		String tempParamName = paramName+0;
		sb.append(paramName).append(" IN (:").append(tempParamName).append(")");
		tempParamsValues.put(tempParamName, list.get(0));
		int size = list.size();
		for(int i=1;i<size;i++){
			tempParamName = paramName+i; 
			sb.append(" OR ").append(paramName).append(" IN (:").append(tempParamName).append(")");
			tempParamsValues.put(tempParamName, list.get(i));	
		}
		Object[] hqlAndParamsMap = new Object[2];
		hqlAndParamsMap[0]=sb.toString();
		hqlAndParamsMap[1]=tempParamsValues;
		return hqlAndParamsMap;
	}
	
	

	
	/**
	 * 解决Oracle中in的参数列表长度超过1000的问题
	 * 获取拆分in参数列表后的Criterion
	 * @param paramName  需要进行in查询的查询参数的名称
	 * @param length     每个分段的长度，对于Oracle，一般设置为800-1000
	 * @param paramsList 待拆分的参数列表
	 * @return 含有嵌套or的Criterion
	 */
	public static <T> Criterion getCriterionInParamsSplit(String paramName,int length,List<T> paramsList){
		if(length<1||paramName==null||paramsList==null||paramsList.size()==0)
			return null;
		List<List<T>> list = splitInParams(length, paramsList);
		Criterion criterion = Restrictions.in(paramName, list.get(0));
		int size = list.size();
		for(int i=1;i<size;i++)
			criterion=Restrictions.or(criterion, Restrictions.in(paramName, list.get(i)));
		return criterion;
	}
	
}

    上面可以初步解决问题，为什么只是初步呢？因为in后面的参数个数很多时会导致数据库执行时间过长，而导致连接超时，Hibernate中会抛出“java.sql.SQLException: Io 异常: Connection reset by peer: socket write error"异常，而直接用JDBC写会抛出“java.sql.SQLRecoverableException: 无法从套接字读取更多的数据”。下面列出一条简单的SQL语句“SELECT count(*)from JBPM4_EXT_TASK_ENGINE_ where 1=1”后面拼接“ or 1=1 ”时JDBC执行时间：

 

	SQL语句长度（字符数）	JDBC执行时间（毫秒）	最后取到的记录数（条）
第一组数据	1653	660	51192
第二组数据	24053	12391	51192
第三组数据	64053	161382	51192

   由此可见SQL语句过长导致JDBC执行时间的增长率是很快的，当SQL语句中的字符数达到某一个数量时肯定会导致连接超时，当然我没有去测这个阈值。不过，产生这个现象的原因是什么，我还不是很清楚（可能是字符数，也可能是查询条件过多而导致表达式树过于庞大而超出数据库的处理能力）。

   那么如何解决这个问题呢？方法一般有两种：1.用表关联代替IN；2.在where条件中使用子查询，如“select  * from b where c in (select  d from e ......)”

这样的形式。

   总而言之，在SQL语句中要慎用IN，IN一般只用于参数个数较少的情况。