Resolution of ORA-01795 exception (where in more than 1000)

This article from Li Mingzi csdn Blog (http://blog.csdn.net/free1985), commercial reprint please contact the owner to obtain authorization, non-commercial reprint please indicate the source.
Absrtact: This paper briefly introduces the solution of Oracle Database ORA-01795 Anomaly, and provides the source code which can be used directly for readers ' reference. 1 Problem Description

Dynamic SQL is used in many management systems that are based on relational database development. That is, in the Java code, according to the business semantics dynamically splicing SQL statements, the implementation of the corresponding result set or to implement the operation of the table.
For systems using Oracle databases, if we are stitching where in, the number of items is more than 1000, and ORA-01795 exceptions are thrown when SQL executes. The description of the exception is as follows:

If the business system is open to user modeling, business configuration and other functions, the phenomenon is likely to occur after some "incredible" configuration. Of course, this problem is more likely to occur for bulk operations and inspections. 2 basic way to solve the problem

The fundamental way to resolve this problem is to convert the original SQL statement to an equivalent SQL statement that is valid for Oracle. For statements such as where column in (A,b,c,d ...), where column in (A,b ...) or column in (C,d ...). is completely equivalent. All we need to do is to group the item in groups of 1000 each.
The implementation code is given below.

/**
     * Get where in statement
     *
     @param column      field name
     * @param values      Collection
     * @return where in statement
     * *
    private String Getwhereinvaluessql (String column, list<string> values) {
        //SQL statement
        String sql = "(; The number of c11/>//values
        int valuesize = Values.size ();
        Number of batches
        int batchsize = valuesize/1000 + (valuesize% 1000 = = 0? 0:1);
        for (int i = 0; i < batchsize; i++) {
            if (i > 0) {
                sql = ") or";
            }
            SQL + + column+ "in";
            for (int j = i * 1000; (J < (i + 1) * 1000) && J < Valuesize; J + +) {
                if (J > I * 1000) {
                    sql = ",";
                }
                SQL + + "" + Values.get (j) + "'";
            }
        }
        sql = ")";
        return SQL;
    }

3 advanced ways to solve problems

If the number of the item in the where in statement is large, you should use the bulk commit further. For example, in Tieaf, we added a batch fetch and batch update method for the database access class that is encapsulated on JDBC basis. The idea is to generate SQL based on the number of lookups per batch, the data section uses placeholders, uses two-dimensional arrays to group the entire set of data (divisible parts) for the item you want to find, acts as a binding parameter, executes a batch query, writes a separate SQL statement for the remainder of the modulo operation, and uses a placeholder for the data part; Organize the remaining item data into a parameter array; execute a single query; merge, return results;

In view of the secrecy problem, this part cannot provide the source code, invites the reader to forgive. 4 Expansion

4.1 where not in
For statements such as where column ont in (a,b,c,d ...), it is equivalent to where column not in (A,b ...) and column not in (C,d ...).
4.2 Package Form
In the actual code, the number of item per batch, "where in, or where not in" should be used as method parameters to maximize the reuse of code. In the actual code, we are packaged in this way.