java.sql.SQLException: Prepared or callable statement has more than 2000 parameter markers.

This one is pretty simple...

We use MSSQL 2005. We had a sql query as given below,

----------------------------------------------------------------------------------------------

select f from FooData f 

         where f.id = :id and f.date <= :today 

         and f.accountID in (:list)

----------------------------------------------------------------------------------------------

For one particular scenario, list was of size 2069

This crashed the sql query in run time with this error

-----------------------------------------------------------------------------------------------------------
"java.sql.SQLException: Prepared or callable statement has more than 2000 parameter markers."
-----------------------------------------------------------------------------------------------------------

If you guys don't know what this error means... and trying to find an answer on the google and various blogs...then please read on..


Interpretation
 
What this error means is that I cannot have the list of input parameters to be more than 2000. Some how you need to either split the large list into smaller ones.. or optimize your sql query. Both of these solutions are given below.

Solution 1

I had to fix this issue by partitioning the list in to small lists of size 1990 and then run the query for each of those smaller lists. Then combine the results into one list again. Now you can partition the list the way you want.. I used this implementation like below.. this uses a private class to do the partition and returns a List<List<T>>. the you can iterate through this list to run your query.

 public static <T> List<List<T>> partition(List<T> list, int size) {

        if (list == null)
            throw new NullPointerException(
                    "'list' must not be null");
        if (!(size > 0))
            throw new IllegalArgumentException(
                    "'size' must be greater than 0");

        return new Partition<T>(list, size);
    }

    private static class Partition<T> extends AbstractList<List<T>> {

        final List<T> list;
        final int size;

        Partition(List<T> list, int size) {
            this.list = list;
            this.size = size;
        }

        @Override
        public List<T> get(int index) {
            int listSize = size();
            if (listSize < 0)
                throw new IllegalArgumentException("negative size: " + listSize);
            if (index < 0)
                throw new IndexOutOfBoundsException(
                        "index " + index + " must not be negative");
            if (index >= listSize)
                throw new IndexOutOfBoundsException(
                        "index " + index + " must be less than size " + listSize);
            int start = index * size;
            int end = Math.min(start + size, list.size());
            return list.subList(start, end);
        }

        @Override
        public int size() {
            return (list.size() + size - 1) / size;
        }

        @Override
        public boolean isEmpty() {
            return list.isEmpty();
        }
    }

But this solution is slow and could take more time to load the screen results.. Therefore, you can also consider the following solution

Solution 2

The second solution is as follows. My list is actually another sql query to get account IDs. So, What I did is to use "inner joins" in my sql query to combine these two queries. This solution is much faster and cleaner. Its done as follows:


------------------------------------------------------------------------------

select f from FooData f 

         inner join FooAccount fa on f.accountID = fa.accountID

         where f.id = :id and f.date <= :today 

-------------------------------------------------------------------------------

I hope this helps you and saves your time.

Thanks for reading this post!