- 在oracle中,使用in方法查询记录的时候,如果in后面的参数个数超过1000个,那么会发生错误,JDBC会抛出“java.sql.SQLException:ORA-01795: 列表中的最大表达式数为 1000”这个异常。比如执行select * from table where id in (1, 2, …, 1000, 1001, …..)时就会抛出上面的异常。
下面用最简单粗暴的方式解决该问题:将超过的1000个参数进行or分割,如in (1,2,…1000) or in (1001,1002…)
import org.apache.commons.lang.StringUtils;
/**
* 创建 id 的in语句 如果ids为空则返回空字符串
* @param ids
* @param propertyStr(比较的属性)
* @return
*/
public static String createIdsInHqlAllowEmpty(String[] ids, String propertyStr) {
if(ids.length<=1000){
String idStr = createIds4Hql(ids);
StringBuffer hqlBuffer = new StringBuffer("");
if (StringUtils.isNotEmpty(idStr)) {
hqlBuffer.append(" and " + propertyStr + " in ").append(idStr);
}
return hqlBuffer.toString();
}else{
StringBuffer hqlBuffer = new StringBuffer("");
hqlBuffer.append(" and ( ");
int num = ids.length/1000;
int remain = ids.length%1000;
for(int i=0;i<=num;i++){
String[] newIds = null;
if(i!=num){
newIds = Arrays.copyOfRange(ids, i*1000, (i+1)*1000);
}else{
newIds = Arrays.copyOfRange(ids, i*1000, i*1000+remain);
}
String idStr = createIds4Hql(newIds);
if (StringUtils.isNotEmpty(idStr)) {
if(i==0){
hqlBuffer.append(propertyStr + " in ").append(idStr);
}else{
hqlBuffer.append(" or " + propertyStr + " in ").append(idStr);
}
}
}
hqlBuffer.append(")");
return hqlBuffer.toString();
}
}
/**
* 创建ID集合字符串,供HQL使用
* @param ids
* @return
*/
public static String createIds4Hql(String[] ids) {
StringBuffer idBuffer = new StringBuffer("");
String idsHql = null;
StringBuffer hqlBuffer = new StringBuffer("");
if (ids!=null&&ids.length>0) {
for (String id : ids) {
idBuffer.append("'" + id + "',");
}
idsHql = StringUtils.substringBeforeLast(idBuffer.toString(), ",");
hqlBuffer.append(" (").append(idsHql).append(")");
}
return hqlBuffer.toString();
}
调用方式如:String sql = ‘select * from A o where 1=1 ‘+createIdsInHqlAllowEmpty(ids,”o.id”);
ids为id属性的条件集合。