15 July 2012

Code to group each 1000 entry in one IN Cluase

Here's an implmenetaion in Java for http://stackoverflow.com/questions/2401066/oracle-sql-how-to-use-more-than-1000-items-inside-an-in-clause#2401090

public static <T> String createSQLINClause(String colName, List<T> arr) {
        final int MAX_IN_COUNT = 1000;

        if (colName != null && arr != null && arr.size() > 0) {

            String ret = "";

            int to = (int) Math.ceil((double) arr.size() / MAX_IN_COUNT);

            for (int i = 0; i < to; i++) {

                int fromIndex = i * MAX_IN_COUNT;
                int toIndex = (i + 1) * MAX_IN_COUNT;

                List<T> subList = arr.subList(fromIndex, toIndex > arr.size() ? arr.size() : toIndex);
                ret += colName + " IN (" + join(subList, ", ") + " ) ";

                if (i < to - 1)
                    ret += " OR ";

            }
            return " ( " + ret + " ) ";
        } else
            return "";
    }
Post a Comment