Java Oracle 异常-“列表中的最大表达式数为 1000”

Java Oracle exception - "maximum number of expressions in a list is 1000"

提问by Anand

I am passing a list of Strings to my query(SQL query written) to fetch the required data. But I am getting this exception:

我将字符串列表传递给我的查询（编写的 SQL 查询）以获取所需的数据。但我收到此异常：

ora-01795 maximum number of expressions in a list is 1000

ora-01795 列表中的最大表达式数为 1000

I checked that I have more than 1000 entries in the list passed to the query IN parameter.

我检查了传递给查询 IN 参数的列表中有 1000 多个条目。

采纳答案by digitebs

this is a oracle limitation in the number of list pass in the query.

这是查询中列表传递数量的 oracle 限制。

1. you will have to chop your query or
2. provide a subquery/join in the IN clause instead.

1. 你将不得不砍你的查询或
2. 而是在 IN 子句中提供子查询/连接。

回答by Igor Kostenko

From dba-oracle.com:

来自dba-oracle.com：

ORA-01795: maximum number of expressions in a list is 1000 tips

Oracle Error Tips by Burleson Consulting (S. Karam)

The Oracle docs note this on the ora-01795 error*: ORA-01795 maximum number of expressions in a list is 1000 Cause: More than 254 columns or expressions were specified in a list. Action: Remove some of the expressions from the list. In the Oracle MOSC Forums, an Oracle user was attempting to find a way around error code ORA-01795. His question was answered by Reem Munakash of Oracle:

The limit in Oracle8 is 1000 expressions. There is a bug 495555, filed against the error text giving the wrong number (254). However, there may be a further restriction depending on the tool you are using. The 1000 expressions is within sqlplus.

The workaround would be to use a sub-query.

The bug regarding the error message is fixed in 8.1.5.

ORA-01795: 列表中的最大表达式数为 1000 条提示

Burleson Consulting 的 Oracle 错误提示 (S. Karam)

Oracle 文档在 ora-01795 错误* 中对此进行了说明：ORA-01795 列表中的最大表达式数为 1000 原因：列表中指定的列或表达式超过 254 个。行动：从列表中删除一些表达式。在 Oracle MOSC 论坛中，一位 Oracle 用户试图找到解决错误代码 ORA-01795 的方法。Oracle 的 Reem Munakash 回答了他的问题：

Oracle8 中的限制是 1000 个表达式。有一个错误 495555，针对给出错误编号 (254) 的错误文本提交。但是，根据您使用的工具，可能会有进一步的限制。1000 个表达式在 sqlplus 中。

解决方法是使用子查询。

有关错误消息的错误已在 8.1.5 中修复。

回答by bpgergo

If you are able to convert your db-side logic from a query into a stored procedure, then you can pass longer arrays (collections) to it.

如果您能够将数据库端逻辑从查询转换为存储过程，那么您可以将更长的数组（集合）传递给它。

Hereyou can find a brief example how to do it. The link to the docs is outdated, so here's a link to the 9i docs http://docs.oracle.com/cd/B10500_01/java.920/a96654/oraarr.htm#1040124

在这里你可以找到一个简单的例子，如何做到这一点。文档链接已过时，因此这里是 9i 文档的链接http://docs.oracle.com/cd/B10500_01/java.920/a96654/oraarr.htm#1040124

import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class ArrayDemo
{
    public static void passArray() throws SQLException
    {
        Connection conn =
            new OracleDriver().defaultConnection();

        int intArray[] = { 1,2,3,4,5,6 };

        ArrayDescriptor descriptor =
            ArrayDescriptor.createDescriptor( "NUM_ARRAY", conn );

        ARRAY array_to_pass =
            new ARRAY( descriptor, conn, intArray );

        OraclePreparedStatement ps =
            (OraclePreparedStatement)conn.prepareStatement
            ( "begin give_me_an_array(:x); end;" );

        ps.setARRAY( 1, array_to_pass );

        ps.execute();

    }
}

and the SQL part

和 SQL 部分

create or replace type NUM_ARRAY as table of number;

create or replace
procedure give_me_an_array( p_array in num_array )
as
begin
    for i in 1 .. p_array.count
    loop
        dbms_output.put_line( p_array(i) );
    end loop;
end;

回答by dursun

you can create a temporary table, and insert the values you want use in your INstatement, and join the temporary table with your real table. more information about temporary tables.

您可以创建一个临时表，并在您的IN语句中插入您想要使用的值，并将临时表与您的真实表连接起来。有关临时表的更多信息。

回答by Vallabh Patade

I solved this by breaking the list into batch of size 1000 and joining it using OR.

我通过将列表分成大小为 1000 的批次并使用 OR 加入它来解决这个问题。

e.g. eid[] array of ids.

例如，eid[] id 数组。

If I want to execute this query,

如果我想执行这个查询，

String sql = select * from employee where some conditions and empid in(eid)

I have re-written this query by writing a small piece of code:

我通过编写一小段代码重新编写了这个查询：

String sql = select * from employee where some conditions and ( 
                             empid in(empid[0...999]) OR
                             empid in(empid[1000...1999]) OR
                             empid in(empid[2000...2999]) OR .... );

Dealing with this error while using hibernate, you have to tackle this issue by chopping the list into batch of 100 and then join the individual results (as shown in the query above).

在使用 hibernate 时处理此错误，您必须通过将列表分成 100 个批次然后加入单个结果来解决此问题（如上面的查询所示）。

I don't think that it's a limitation of hibernate for not handling this issue, because it may be the case that this issue is not a case of another DB like MySQL or DB2. Hibernate is a cross-DB ORM framework.

我不认为这是 hibernate 不处理这个问题的限制，因为这个问题可能不是另一个像 MySQL 或 DB2 这样的数据库的情况。Hibernate 是一个跨数据库的 ORM 框架。

回答by Tu??ad Karaduman

You cant have a list with more than 1000 elements in a single "where" condition if you are working with Oracle DB. So you can chop down your "where" condition in multiple "where" conditions and join them with "or" clause.

如果您使用的是 Oracle DB，则不能在单个“where”条件中拥有超过 1000 个元素的列表。因此，您可以在多个“where”条件中删除“where”条件，并使用“or”子句将它们连接起来。

If you are using hibernate Criteria, you can use below Java method to do this. Just replace your code where ever you used

如果您使用 hibernate Criteria，则可以使用以下 Java 方法来执行此操作。只需在您使用过的地方替换您的代码

criteria.add(Restrictions.in(propertyName, mainList));

with

和

addCriteriaIn(propertyName, mainList, criteria);

which the method is :

方法是：

 private void addCriteriaIn (String propertyName, List<?> list,Criteria criteria)
  {
    Disjunction or = Restrictions.disjunction();
    if(list.size()>1000)
    {        
      while(list.size()>1000)
      {
        List<?> subList = list.subList(0, 1000);
        or.add(Restrictions.in(propertyName, subList));
        list.subList(0, 1000).clear();
      }
    }
    or.add(Restrictions.in(propertyName, list));
    criteria.add(or);
  }

回答by Vasile Bors

Using Java Hibernate, to solve this problem I decided to change the Hibernate-core JAR. I madea a helper class to split an expression in more joins like: ... t.column IN (: list_1) OR t.column IN (: list_2) ..., Then I changed AbstractQueryImpl.expandParameterList method from hibernate to call my method if the collection exceeds the limit.
My hibernate-core version is 3.6.10.Final, but it work fine and for 4.x versions - I tested it.
My code is tested for next cases:

使用Java Hibernate，为了解决这个问题，我决定改变Hibernate-core JAR。我创建了一个辅助类来拆分更多连接中的表达式，例如：... t.column IN (: list_1) OR t.column IN (: list_2) ...，然后我将 AbstractQueryImpl.expandParameterList 方法从休眠更改为如果集合超过限制则调用我的方法。
我的 hibernate-core 版本是 3.6.10.Final，但它工作正常，并且适用于 4.x 版本 - 我测试了它。
我的代码针对以下情况进行了测试：

  where t.id in (:idList)
  where (t.id in (:idList))
  where ((t.id) in (:idList))
  where 1=1 and t.id in (:idList)
  where 1=1 and (t.id in (:idList))
  where 1=1 and(t.id) in (:idList)
  where 1=1 and((t.id) in (:idList))
  where 1=1 and(t.id in (:idList))

  where t.id not in (:idList)
  where (t.id not in (:idList))
  where ((t.id) not in (:idList))

AbstractQueryImpl.expandParameterList :

AbstractQueryImpl.expandParameterList ：

private String expandParameterList(String query, String name, TypedValue typedList, Map namedParamsCopy) {
    Collection vals = (Collection) typedList.getValue();
    Type type = typedList.getType();

    boolean isJpaPositionalParam = parameterMetadata.getNamedParameterDescriptor( name ).isJpaStyle();
    String paramPrefix = isJpaPositionalParam ? "?" : ParserHelper.HQL_VARIABLE_PREFIX;
    String placeholder =
            new StringBuffer( paramPrefix.length() + name.length() )
                    .append( paramPrefix ).append(  name )
                    .toString();

    if ( query == null ) {
        return query;
    }
    int loc = query.indexOf( placeholder );

    if ( loc < 0 ) {
        return query;
    }

    String beforePlaceholder = query.substring( 0, loc );
    String afterPlaceholder =  query.substring( loc + placeholder.length() );

    // check if placeholder is already immediately enclosed in parentheses
    // (ignoring whitespace)
    boolean isEnclosedInParens =
            StringHelper.getLastNonWhitespaceCharacter( beforePlaceholder ) == '(' &&
                    StringHelper.getFirstNonWhitespaceCharacter( afterPlaceholder ) == ')';

    if ( vals.size() == 1  && isEnclosedInParens ) {
        // short-circuit for performance when only 1 value and the
        // placeholder is already enclosed in parentheses...
        namedParamsCopy.put( name, new TypedValue( type, vals.iterator().next(), session.getEntityMode() ) );
        return query;
    }

    // *** changes by Vasile Bors for HHH-1123 ***
    // case vals.size() > 1000
    if ((vals.size() >= InExpressionExpander.MAX_ALLOWED_PER_INEXPR) && isEnclosedInParens) {

        InExpressionExpander inExpressionExpander = new InExpressionExpander(beforePlaceholder, afterPlaceholder);
        if(inExpressionExpander.isValidInOrNotInExpression()){

            List<String> list = new ArrayList<String>( vals.size() );
            Iterator iter = vals.iterator();
            int i = 0;
            String alias;
            while ( iter.hasNext() ) {
                alias = ( isJpaPositionalParam ? 'x' + name : name ) + i++ + '_';
                namedParamsCopy.put( alias, new TypedValue( type, iter.next(), session.getEntityMode() ) );
                list.add(ParserHelper.HQL_VARIABLE_PREFIX + alias );
            }

            String expandedExpression = inExpressionExpander.expandExpression(list);
            if(expandedExpression != null){
                return expandedExpression;
            }
        }
    }
    // *** end changes by Vasile Bors for HHH-1123 ***

    StringBuffer list = new StringBuffer(16);
    Iterator iter = vals.iterator();
    int i = 0;
    while (iter.hasNext()) {
        String alias = (isJpaPositionalParam ? 'x' + name : name) + i++ + '_';
        namedParamsCopy.put(alias, new TypedValue(type, iter.next(), session.getEntityMode()));
        list.append(ParserHelper.HQL_VARIABLE_PREFIX).append(alias);
        if (iter.hasNext()) {
            list.append(", ");
        }
    }

    return StringHelper.replace(
            beforePlaceholder,
            afterPlaceholder,
            placeholder.toString(),
            list.toString(),
            true,
            true
    );
}

My helper class InExpressionExpander:

我的助手类 InExpressionExpander：

package org.hibernate.util;

包 org.hibernate.util;

import org.hibernate.QueryException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.Iterator;
import java.util.List;
import java.util.Stack;

/**
 * Utility class for expand Hql and Sql IN expressions with a parameter with more than IN expression limit size (HHH-1123).
 * <br/>
 * It work for expression with formats:
 * <pre>
 *
 * where t.id in (:idList)
 * where (t.id in (:idList))
 * where ((t.id) in (:idList))
 * where 1=1 and t.id in (:idList)
 * where 1=1 and (t.id in (:idList))
 * where 1=1 and(t.id) in (:idList)
 * where 1=1 and((t.id) in (:idList))
 * where 1=1 and(t.id in (:idList))
 *
 * where t.id not in (:idList)
 * where (t.id not in (:idList))
 * where ((t.id) not in (:idList))
 * </pre>
 * <p/>
 * Example:
 * <pre>
 * select t.id from tableOrEntity t where t.id IN (:idList)
 * </pre
 *
 * @author Vasile Bors
 * @since 13/12/2015.
 */
public class InExpressionExpander {
    private static final Logger log = LoggerFactory.getLogger(InExpressionExpander.class);

    public static final  int MAX_ALLOWED_PER_INEXPR = 1000;
    private static final int MAX_PARAMS_PER_INEXPR  = 500;

    private Stack<String> stackExpr = new Stack<String>();
    private StringBuilder toWalkQuery;

    private final String beforePlaceholder;
    private final String afterPlaceholder;
    private boolean wasChecked         = false;
    private boolean isEnclosedInParens = false;
    private boolean isInExpr           = false;
    private boolean isNotInExpr        = false;

    public InExpressionExpander(String beforePlaceholder, String afterPlaceholder) {
        this.toWalkQuery = new StringBuilder(beforePlaceholder);

        this.beforePlaceholder = beforePlaceholder;
        this.afterPlaceholder = afterPlaceholder;
    }

    public boolean isValidInOrNotInExpression() {
        if (!wasChecked) {
            String lastExpr = extractLastExpression();
            if ("(".equals(lastExpr)) {
                isEnclosedInParens = true;
                lastExpr = extractLastExpression();
            }
            isInExpr = "in".equalsIgnoreCase(lastExpr);
        }

        wasChecked = true;
        return isInExpr;
    }

    public String expandExpression(List paramList) {
        if (isValidInOrNotInExpression()) {

            final String lastExpr = extractLastExpression(false);

            if ("not".equalsIgnoreCase(lastExpr)) {
                isNotInExpr = true;
                extractLastExpression(); //extract "not" and consume it
            }

            extractColumnForInExpression();

            StringBuilder exprPrefixBuilder = new StringBuilder();
            for (int i = stackExpr.size() - 1; i > -1; i--) {
                exprPrefixBuilder.append(stackExpr.get(i)).append(' ');
            }
            if (!isEnclosedInParens) {
                exprPrefixBuilder.append('(');
            }

            String expandedExpression = expandInExpression(exprPrefixBuilder, paramList);
            String beforeExpression = getBeforeExpression();
            String afterExpression = getAfterExpression();

            String expandedQuery = new StringBuilder(beforeExpression).append(expandedExpression)
                    .append(afterExpression)
                    .toString();

            if (log.isDebugEnabled()) {
                log.debug(
                        "Query was changed to prevent exception for maximum number of expression in a list. Expanded IN expression query:\n {}",
                        expandedExpression);

                log.debug("Expanded query:\n {}", expandedQuery);
            }

            return expandedQuery;
        }

        log.error("Illegal call of InExpressionExpander.expandExpression() without IN expression.");
        return null;
    }

    private String expandInExpression(StringBuilder exprPrefixBuilder, List values) {

        String joinExpr = isNotInExpr ? ") and " : ") or ";
        StringBuilder expr = new StringBuilder(16);
        Iterator iter = values.iterator();
        int i = 0;
        boolean firstExpr = true;
        while (iter.hasNext()) {
            if (firstExpr || i % MAX_PARAMS_PER_INEXPR == 0) {
                //close previous expression and start new expression
                if (!firstExpr) {
                    expr.append(joinExpr);
                } else {
                    firstExpr = false;
                }
                expr.append(exprPrefixBuilder);
            } else {
                expr.append(", ");
            }
            expr.append(iter.next());
            i++;
        }

        expr.append(')');// close for last in expression

        return expr.toString();
    }

    /**
     * Method extract last expression parsed by space from toWalkQuery and remove it from toWalkQuery;<br/>
     * If expression has brackets it will return al content between brackets and it will add additional space to adjust splitting by space.
     *
     * @return last expression from toWalkQuery
     */
    private String extractLastExpression() {
        return extractLastExpression(true);
    }

    /**
     * Method extract last expression parsed by space from toWalkQuery, remove it from toWalkQuery if is consume = true;<br/>
     * If expression has brackets it will return al content between brackets and it will add additional space to adjust splitting by space.
     *
     * @param consum if true  the method will extract and remove last expression from toWalkQuery
     * @return last expression from toWalkQuery
     */
    private String extractLastExpression(final boolean consum) {
        int lastIndex = this.toWalkQuery.length() - 1;
        String lastExpr;
        int exprSeparatorIndex = this.toWalkQuery.lastIndexOf(" ");
        if (lastIndex == exprSeparatorIndex) { //remove last space from the end
            this.toWalkQuery.delete(exprSeparatorIndex, this.toWalkQuery.length());
            return extractLastExpression(consum);
        } else {
            lastExpr = this.toWalkQuery.substring(exprSeparatorIndex + 1, this.toWalkQuery.length());

            if (lastExpr.length() > 1) {
                if (lastExpr.endsWith(")")) {
                    //if parens are closed at the end we need to find where it is open
                    int opensParens = 0;
                    int closedParens = 0;
                    int startExprIndex = -1;

                    char c;
                    for (int i = lastExpr.length() - 1; i > -1; i--) {
                        c = lastExpr.charAt(i);
                        if (c == ')') {
                            closedParens++;
                        } else if (c == '(') {
                            opensParens++;
                        }

                        if (closedParens == opensParens) {
                            startExprIndex = i;
                            break;
                        }
                    }

                    if (startExprIndex > -1) {
                        lastExpr = lastExpr.substring(startExprIndex, lastExpr.length());
                        exprSeparatorIndex = exprSeparatorIndex + startExprIndex
                                + 1; // +1 because separator is not space and don't must be deleted
                    }
                } else if (lastExpr.contains("(")) {
                    int parentsIndex = exprSeparatorIndex + lastExpr.indexOf('(') + 1;
                    this.toWalkQuery.replace(parentsIndex, parentsIndex + 1, " ( ");
                    return extractLastExpression(consum);
                }
            }

            if (consum) {
                this.toWalkQuery.delete(exprSeparatorIndex, this.toWalkQuery.length());
            }
        }

        if (consum) {
            stackExpr.push(lastExpr);
        }

        return lastExpr;
    }

    private String extractColumnForInExpression() {
        String column = extractLastExpression();

        String beforeColumn = extractLastExpression(false);
        long pointIndx = beforeColumn.lastIndexOf('.');
        if (pointIndx > -1) {
            if (pointIndx == (beforeColumn.length() - 1)) {
                throw new QueryException(
                        "Invalid column format: " + beforeColumn + ' ' + column
                                + " . Remove space from column!");
            }
        }
        return column;
    }

    private String getBeforeExpression() {
        return this.toWalkQuery + " (";
    }

    private String getAfterExpression() {
        if (StringHelper.getFirstNonWhitespaceCharacter(afterPlaceholder) == ')') {
            return afterPlaceholder;
        }
        return afterPlaceholder + ") ";
    }
}

I am happy to receive any suggestions for improving this solution.

我很高兴收到有关改进此解决方案的任何建议。