handle 1000 query problem in oracle select where IN clause

The problem is because oracle has a limit on

Select A.a, B.b, C.c  FROM A, B, C where A.a in (1,2,3,4,5,6,….).

The limit is 1000.  if the parameter in the (…) is over 1000, exception: java.sql.SQLException: ORA-01795

Thoughts

We can use “field in (1-1000) or field in (1001-2000)….” to solve this problem.

Implementation

   /**
     * <b>function:</b>  handle oracle sql clause : in -&gt; (where id in (1, 2, …, 1000, 1001)),
     * Due to the limit orcale has, param number in "IN" has to be less than 1000.
     * To solvethis,we use:  where id in (1, 2, …, 1000) or id in (1001, …)
     *
     * @param values the object in "IN"
     * @param count the number appear in "IN"
     * @param field the field for "IN"
     *
     * @return field in (…) or field in (…)
     */
    private String getOracleSQLForIN(List&lt;?&gt; values, int count, String field)
    {
        count = Math.min(count, 1000);
        int len = values.size();
        int size = len % count;
        if (size == 0)
        {
            size = len / count;
        }
        else
        {
            size = (len / count) + 1;
        }
        StringBuilder builder = new StringBuilder();
        for (int i = 0; i &lt; size; i++)
        {
            int fromIndex = i * count;
            int toIndex = Math.min(fromIndex + count, len);
            //System.out.println(values.subList(fromIndex, toIndex));
            String productId = StringUtils.defaultIfEmpty(StringUtils.join(values.subList(fromIndex, toIndex), "','"), "");
            if (i != 0)
            {
                builder.append(" or ");
            }
            builder.append(field).append(" in ('").append(productId).append("')");
        }
        return StringUtils.defaultIfEmpty(builder.toString(), field + " in ('')");
    }

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s