java.sql.SQL语法错误异常 : ORA-01795: maximum number of expressions in a list is 1000

Question

我们都知道，当尝试用超过 1000 个值来轰炸查询时，我们会看到这个异常。列限制的最大值是 1000.. 最好的解决方案是将查询分成两部分。伙计们，你能建议一些可能的代码重构方法来解决我的问题吗？我们将不胜感激。

当 audTypeFieldIdList 大于 1000 个值时，我们会看到异常。

try {
        String query = "select AUDIT_TYPE_FIELD_ID, FIELD_NAME from AUDIT_TYPE_FIELD where AUDIT_TYPE_FIELD_ID in (";
        int x = 0;
        for (int y = 1; y <= audTypeFieldIdList.size(); y++) {
            query += audTypeFieldIdList.get(x);
            if (y != audTypeFieldIdList.size()) {
                query += ", ";
            }
            x++;
        }
        query += ")";    



List<Long> audTypeFieldIdList, Connection connection) {          

    ResultSet rs = null;
    Statement stmt = null;
    List<AuditTypeField> audTypeFieldList = new ArrayList<AuditTypeField>();
    try {
        String query = "select AUDIT_TYPE_FIELD_ID, FIELD_NAME from AUDIT_TYPE_FIELD where AUDIT_TYPE_FIELD_ID in (";
        int x = 0;
        for (int y = 1; y <= audTypeFieldIdList.size(); y++) {
            query += audTypeFieldIdList.get(x);
            if (y != audTypeFieldIdList.size()) {
                query += ", ";
            }
            x++;
        }
        query += ")";



        stmt = connection.createStatement();
        rs = stmt.executeQuery(query);
        while (rs != null && rs.next()) {
            AuditTypeField audTypeField = PluginSystem.INSTANCE
                    .getPluginInjector().getInstance(AuditTypeField.class);
            audTypeField.setId(rs.getLong("AUDIT_TYPE_FIELD_ID"));
            audTypeField.setName(rs.getString("FIELD_NAME"));
            audTypeFieldList.add(audTypeField);
        }
        return audTypeFieldList;
        return audTypeFieldList;

Answer 1

您不能在 IN 子句中使用超过 1000 个条目。有两种解决方案，如下所述:

1. 使用内部查询解决这个问题。您可以创建一个临时表并在 IN 子句中使用它。

2. 使用 OR 子句分隔的多个 IN 子句将其分成 1000 个条目的批处理。

示例查询:

select * from table_name
  where
      column_name in (V1,V2,V3,...V1000)
  or
      column_name in (V1001,V1002,V1003,...V2000)
  ...

Read more..并查看 Oracle FAQ