in的参数个数oracle,Oracle数据库中IN参数个数超过1000的有关问题

业务问题：

在oracle中，我们使用in方法查询记录的时候，如果in后面的参数个数超过1000个，那么会发生错误，JDBC会抛出“java.sql.SQLException: ORA-01795: 列表中的最大表达式数为 1000”这个异常。

解决方案：

这个问题的思想是把参数列表分段，将SQL语句拼成如下形式：

select * from spp_info where keyword in (a,b,c) union select * from spp_info where keyword in (d,e,f)

public class Test {

public static void main(String[] args) {

String sql = "select * from spp_info where keyword";

String keyword = "a,b,c,d,e,f";

int splitNum = 3;

System.out.println(setWhereInArray(sql, keyword, splitNum));

}

/**

* More than 1000 divided into multiple sql queries

*

* @param orgSql

* @param paramValue

* @param splitNum query number

* @return sql

*/

public static String setWhereInArray(String orgSql, String paramValue, int splitNum) {

String paramArray[] = paramValue.split(",");

int inArrayNum = paramArray.length % splitNum == 0 ? paramArray.length / splitNum : paramArray.length / splitNum + 1;

int m = 0;

int b = 0;

int n = splitNum;

String p[] = new String[inArrayNum];

String sql[] = new String[inArrayNum];

for (int k = 0; k < paramArray.length; k++) {

if (b < inArrayNum) {

p[b] = "";

for (; m < n; m++) {

if (m >= paramArray.length) {

break;

}

p[b] += paramArray[m] + ",";

}

p[b] = p[b].substring(0, p[b].lastIndexOf(","));

sql[b] = orgSql + " in (" + p[b] + ")";

b++;

n += splitNum;

}

}

String newSql = "";

for (int q = 0; q < sql.length; q++) {

newSql += sql[q] + " union ";

}

newSql = newSql.substring(0, newSql.lastIndexOf(" union "));

return newSql;

}

}