Java Oracle 예외 - "목록의 최대 식 수는 1000개입니다."

Java Oracle 예외 - "목록의 최대 식 수는 1000개입니다."

필요한 데이터를 가져오기 위해 문자열 목록을 쿼리(SQL 쿼리 작성)에 전달하고 있습니다.하지만 저는 이 예외를 받고 있습니다.

ora-01795 목록의 최대 식 수는 1000입니다.

쿼리 IN 파라미터로 전달된 목록에 1000개 이상의 항목이 있는 것을 확인했습니다.

쿼리에서 목록 패스 수에 대한 오라클 제한입니다.

1. 당신은 당신의 질문을 삭제해야 할 것입니다.
2. 대신 IN 절에 하위 쿼리/가입을 제공합니다.

Oracle DB로 작업하는 경우 단일 "위치" 조건에 1000개 이상의 요소가 포함된 목록을 가질 수 없습니다.따라서 여러 "where" 조건에서 "where" 조건을 잘라내고 "or" 절로 결합할 수 있습니다.

최대 절전 모드 기준을 사용하는 경우 아래의 Java 방법을 사용하여 이를 수행할 수 있습니다.사용한 곳마다 코드를 바꾸기만 하면 됩니다.

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

와 함께

addCriteriaIn(propertyName, mainList, criteria);

방법은 다음과 같습니다.

 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);
  }

저는 목록을 1000사이즈로 나누어 OR을 사용하여 가입함으로써 이 문제를 해결했습니다.

예: ID 배열

이 쿼리를 실행하려면,

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

작은 코드를 작성하여 이 쿼리를 다시 작성했습니다.

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 .... );

최대 절전 모드를 사용하는 동안 이 오류를 처리하려면 목록을 100개의 배치로 자른 다음 개별 결과에 참여하여 이 문제를 해결해야 합니다(위 쿼리 참조).

이 문제는 MySQL이나 DB2와 같은 다른 DB의 경우가 아닐 수 있기 때문에 이 문제를 처리하지 않는 것이 최대 절전 모드의 제한이라고 생각하지 않습니다.최대 절전 모드는 DB ORM 간 프레임워크입니다.

테이블을 을 임시테이사값삽수있다습니입할을용에 수 .IN문을 열고 임시 테이블을 실제 테이블과 결합합니다. 임시 테이블에 대한 자세한 정보.

dba-oracle.com 에서:

ORA-01795: 목록의 최대 식 수는 팁 1000개입니다.

Burleson Consulting(S. Karam)의 Oracle 오류 팁

Oracle 문서에서는 ora-01795 오류*: ORA-01795 목록의 최대 식 수가 1000개입니다. 원인: 목록에 254개 이상의 열 또는 식을 지정했습니다.작업:목록에서 일부 식을 제거합니다.Oracle MOSC 포럼에서 Oracle 사용자가 오류 코드 ORA-01795를 해결하는 방법을 찾고 있었습니다.그의 질문에 오라클의 렘 무나카시가 대답했습니다.

Oracle8의 제한식은 1000개입니다.잘못된 번호(254)를 제공하는 오류 텍스트에 대해 제기된 버그 495555가 있습니다.그러나 사용 중인 도구에 따라 추가적인 제한이 있을 수 있습니다.1000 식은 sqlplus 내에 있습니다.

해결 방법은 하위 쿼리를 사용하는 것입니다.

오류 메시지와 관련된 버그는 8.1.5에서 수정되었습니다.

쿼리에서 저장 프로시저로 DB 측 논리를 변환할 수 있는 경우 더 긴 배열(수집)을 쿼리로 전달할 수 있습니다.

여기서 방법에 대한 간단한 예를 찾을 수 있습니다.문서에 대한 링크가 오래되었으므로 여기 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();

    }
}

및 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;

Java Hibernate를 사용하여 이 문제를 해결하기 위해 Hibernate-core JAR을 변경하기로 결정했습니다.다음과 같은 추가 조인으로 표현식을 분할하기 위해 도우미 클래스를 만들었습니다.... t.column IN (: list_1) OR t.column IN (: list_2) ...그런 다음 AbstractQueryImpl.expandParameterList 메서드를 최대 절전 모드에서 컬렉션이 제한을 초과하면 메서드를 호출하도록 변경했습니다.
내 최대 절전 모드 코어 버전은 3.6.10입니다.최종적이지만 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.expand 매개 변수 목록:

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
    );
}

내 도우미 클래스 InExpressionExpander:

패키지 org.certificate.submit;

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 + ") ";
    }
}

이 솔루션을 개선하기 위한 제안을 받게 되어 기쁩니다.

언급URL : https://stackoverflow.com/questions/9767920/java-oracle-exception-maximum-number-of-expressions-in-a-list-is-1000