partition list for the error "maximum number of expressions in a list is 1000"

Question

I passed a list with more than 1000 elements in a HQL query and got this error

maximum number of expressions in a list is 1000

I read several posts on Stackoverflow which suggests to partition the list into a few sub lists.

  Query query = session.createQuery(
  "SELECT r.subject, COUNT(DISTINCT ss.id), COUNT(DISTINCT r.uid)\n" +
  "FROM " + R.class.getName() + " r\n" +
  "," + SS.class.getName() + " ss\n" +
  "WHERE r.id = ss.id\n" +
  "AND r.uid in (:listMoreThan1000)\n" +
  "GROUP BY r.subject\n"
);
query.setParameterList("listMoreThan1000", listMoreThan1000);
List<Object[]> rows = query.list();
// code to parse the rows

I am new to HQL query and may anyone tell me how to partition listMoreThan1000 into a few sub lists in my code?

Split the List has nothing to do with hql. Do it in java code and call the Statement many times. — Jens, Jun 05 '18 at 18:55

score 2 · Answer 1 · answered Jun 05 '18 at 19:24

2

try to use tuples or if possible a sub query.

1000 items such as IN (1, 2, 3, ..., 1000)
No limit on the number of sets: IN ((1), (2), (3), ...) * <=== NOT REMOVE! * See below
No limit in sub-queries: IN (SELECT ...)

answered Jun 05 '18 at 19:24

arce.est

344
2
8

YCF_L · Accepted Answer · 2018-06-06T02:29:49.957

The error is clear, just split your huge list to small list than pass them to your query, if you are using Java 8+ you can use this method Divide a list to lists of n size in Java 8 by Szymon Stepniak,

private static <T> Collection<List<T>> partition(List<T> list, int size) {
    final AtomicInteger counter = new AtomicInteger(0);

    return list.stream()
            .collect(Collectors.groupingBy(it -> counter.getAndIncrement() / size))
            .values();
}

Then your service can look like this :

List<String> listMoreThan1000 = ...;
int size = 1000;
Collection<List<String>> split = partition(listMoreThan1000, size);
String inClause = "", or = "";
for (int i = 0; i < split.size(); i++) {
    inClause += or + "r.uid in ?" + i;
    or = " OR ";
}
String query = String.format("SELECT r.subject, COUNT(DISTINCT ss.id), COUNT(DISTINCT r.uid)" +
        " FROM %s r, %s ss" +
        " WHERE r.id = ss.id" +
        " AND %s GROUP BY r.subject", R.class.getName(), SS.class.getName(), inClause);

Query query = session.createQuery(query);
for (int i = 0; i < split.size(); i++) {
    query.setParameter("?" + i, split.get(i));
}

List<Object[]> rows = query.list();

Your query in the end should look like this :

SELECT r.subject, COUNT(DISTINCT ss.id), COUNT(DISTINCT r.uid)
FROM R r, SS ss
WHERE r.id = ss.id
AND r.uid in ?1 OR r.uid in ?2 OR r.uid in ?3 GROUP BY r.subject

Note this part :

r.uid in ?1 OR r.uid in ?2 OR r.uid in ?3

This will check if your uid exist in the first sublist or in the 2ed sublist ... until n sublist

do you mean inClause += or + "r.uid in ?" + i; – user3369592 Jun 05 '18 at 21:47 — user3369592, Jun 05 '18 at 21:47
@user3369592 sorry yes I mean it I forgot the plus – YCF_L Jun 06 '18 at 02:29 — YCF_L, Jun 06 '18 at 02:29

partition list for the error "maximum number of expressions in a list is 1000"

2 Answers2