myBATIS foreach hitting limit of 1000

Question

Here's what myBATIS has on their own documentation for foreach.

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

However, if list contains over 1000 items and you're using Oracle DB, you get this exception:

java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

What can I do to fix this so it works with more than 1000 elements?

Answer 1

I'm not sure if this is the most elegant solution or not, but here's what I did:

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <trim suffixOverrides=" OR ID IN ()">
      <foreach item="item" index="index" collection="list"
               open="(" close=")">
          <if test="index != 0">
              <choose>
                  <when test="index % 1000 == 999">) OR ID IN (</when>
                  <otherwise>,</otherwise>
              </choose>
          </if>
          #{item}
      </foreach>
  </trim>
</select>

Explanation

Lets start with the foreach. We want to surround it in ( and ). Most elements we want commas between, except every thousand elements we want to stop the list and OR with another one. That's what the choose, when, otherwise construct handles. Except we don't want either of those before the first element, thus the if that the choose is inside of. Finally, the foreach ends with actually having the #{item} inserted.

The outer trim is just so that if we have exactly 1000 elements, for example, we don't end with OR ID IN () which would be invalid ((), specifically, is the invalid part. That's a syntax error in SQL, not an empty list like I hoped it would be.)

Answer 2

We have tried delete query in clause more then 1000 records with above reference:

 <delete id="delete" parameterType="Map">

The following query working:

DELETE FROM Employee
where
 emp_id = #{empId}
  <foreach item="deptId" index= "index" collection="ids" open="AND DEPT_ID NOT IN (" close=")" >
      <if test="index != 0">
        <choose>
                    <when test="index % 1000 == 999">) AND DEPT_ID NOT IN (</when>
                    <otherwise>,</otherwise>
                </choose>
           </if>
  #{deptId}
</foreach>
</delete>

Answer 3

Mybatis plugin query and then combine partitioned params :

@Intercepts({
    @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
    @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class})}
)
public class BigSizeParamQueryPlugin implements Interceptor {

private final int singleBatchSize;
private static final HeavyParamContext NO_BIG_PARAM = new HeavyParamContext();

public BigSizeParamQueryPlugin() {
    this.singleBatchSize = 1000;
}

public BigSizeParamQueryPlugin(Integer singleBatchSize) {
    if (singleBatchSize < 500) {
        throw new IllegalArgumentException("batch size less than 500 is not recommended");
    }
    this.singleBatchSize = singleBatchSize;
}

@Override
public Object intercept(Invocation invocation) throws Throwable {

    Object[] args = invocation.getArgs();
    Object parameter = args[1];

    if (parameter instanceof MapperMethod.ParamMap && RowBounds.DEFAULT == args[2]) {
        MapperMethod.ParamMap paramMap = (MapperMethod.ParamMap) parameter;
        if (MapUtils.isNotEmpty(paramMap)) {
            try {
                HeavyParamContext context = findHeavyParam(paramMap);
                if (context.hasHeavyParam()) {
                    QueryExecutor queryExecutor = new QueryExecutor(invocation, context);
                    return queryExecutor.query();
                }
            } catch (Throwable e) {
                log.warn("BigSizeParamQueryPlugin process error", e);
                return invocation.proceed();
            }
        }
    }

    return invocation.proceed();
}

private class QueryExecutor {
    private final MappedStatement ms;
    private final Map<String, Object> paramMap;
    private final RowBounds rowBounds;
    private final ResultHandler resultHandler;
    private final Executor executor;
    private final List<Object> finalResult;
    private final Iterator<HeavyParam> heavyKeyIter;

    public QueryExecutor(Invocation invocation, HeavyParamContext context) {
        Object[] args = invocation.getArgs();
        this.ms = (MappedStatement) args[0];
        this.paramMap = context.getParameter();
        this.rowBounds = (RowBounds) args[2];
        this.resultHandler = (ResultHandler) args[3];
        this.executor = (Executor) invocation.getTarget();
        List<HeavyParam> heavyParams = context.getHeavyParams();
        this.finalResult = new ArrayList<>(heavyParams.size() * singleBatchSize);
        this.heavyKeyIter = heavyParams.iterator();
    }

    public Object query() throws SQLException {
        while (heavyKeyIter.hasNext()) {
            HeavyParam currKey = heavyKeyIter.next();
            List<List<Object>> param = partitionParam(currKey.getParam());
            doQuery(currKey, param);
        }
        return finalResult;
    }

    private void doQuery(HeavyParam currKey, List<List<Object>> param) throws SQLException {
        if (!heavyKeyIter.hasNext()) {
            for (List<Object> currentParam : param) {
                updateParamMap(currKey, currentParam);
                List<Object> oneBatchResult = executor.query(ms, paramMap, rowBounds, resultHandler);
                finalResult.addAll(oneBatchResult);
            }
            return;
        } else {
            HeavyParam nextKey = heavyKeyIter.next();
            log.warn("get mutil heavy key [{}], batchSize[{}]", nextKey.shadowHeavyKeys, nextKey.getParam().size());
            List<List<Object>> nextParam = partitionParam(nextKey.getParam());
            for (List<Object> currParam : param) {
                updateParamMap(currKey, currParam);
                doQuery(nextKey, nextParam);
            }
        }
    }

    private void updateParamMap(HeavyParam currKey, List<Object> param) {
        for (String shadowKey : currKey.getShadowHeavyKeys()) {
            paramMap.put(shadowKey, param);
        }
    }
}

private HeavyParamContext findHeavyParam(Map<String, Object> parameterMap) {
    List<Map.Entry<String, Object>> heavyKeys = doFindHeavyParam(parameterMap);
    if (heavyKeys == null) {
        return BigSizeParamQueryPlugin.NO_BIG_PARAM;
    } else {
        HeavyParamContext result = new HeavyParamContext();
        List<HeavyParam> heavyParams;
        if (heavyKeys.size() == 1) {
            heavyParams = buildSingleHeavyParam(heavyKeys);
        } else {
            heavyParams = buildMultiHeavyParam(heavyKeys);
        }
        result.setHeavyParams(heavyParams);
        result.setParameter(new HashMap<>(parameterMap));
        return result;
    }
}

private List<HeavyParam> buildSingleHeavyParam(List<Map.Entry<String, Object>> heavyKeys) {
    Map.Entry<String, Object> single = heavyKeys.get(0);
    return Collections.singletonList(new HeavyParam((Collection) single.getValue(), Collections.singletonList(single.getKey())));
}

private List<List<Object>> partitionParam(Object o) {
    Collection c = (Collection) o;
    List res;
    if (c instanceof List) {
        res = (List) c.stream().distinct().collect(Collectors.toList());
    } else {
        res = new ArrayList(c);
    }
    return Lists.partition(res, singleBatchSize);
}

private List<HeavyParam> buildMultiHeavyParam(List<Map.Entry<String, Object>> heavyKeys) {
    //when heavy keys used multi time in xml, its name will be different.
    TreeMap<Collection, List<String>> params = new TreeMap<>(new Comparator<Collection>() {
        @Override
        public int compare(Collection o1, Collection o2) {
            //fixme workable but have corner case. 
            return CollectionUtils.isEqualCollection(o1, o2) == true ? 0 : o1.hashCode() - o2.hashCode();
        }
    });

    for (Map.Entry<String, Object> keyEntry : heavyKeys) {
        String key = keyEntry.getKey();
        List<String> keys = params.computeIfAbsent((Collection) keyEntry.getValue(), k -> new ArrayList<>(1));
        keys.add(key);
    }

    List<HeavyParam> hps = new ArrayList<>(params.size());
    for (Map.Entry<Collection, List<String>> heavyEntry : params.entrySet()) {
        List<String> shadowKeys = heavyEntry.getValue();
        hps.add(new HeavyParam(heavyEntry.getKey(), shadowKeys));
    }
    return hps;
}

private List<Map.Entry<String, Object>> doFindHeavyParam(Map<String, Object> parameterMap) {
    List<Map.Entry<String, Object>> result = null;
    for (Map.Entry<String, Object> p : parameterMap.entrySet()) {
        if (p != null) {
            Object value = p.getValue();
            if (value != null && value instanceof Collection) {
                int size = CollectionUtils.size(value);
                if (size > singleBatchSize) {
                    if (result == null) {
                        result = new ArrayList<>(1);
                    }
                    result.add(p);
                }
            }
        }
    }
    return result;
}


@Getter
@Setter
private static class HeavyParamContext {
    private Boolean hasHeavyParam;
    private List<HeavyParam> heavyParams;
    private Map<String, Object> parameter;

    public Boolean hasHeavyParam() {
        return heavyParams != null;
    }
}


@Data
@AllArgsConstructor
@NoArgsConstructor
private class HeavyParam {
    private Collection param;
    private List<String> shadowHeavyKeys;
}

@Override
public Object plugin(Object o) {
    return Plugin.wrap(o, this);
}

@Override
public void setProperties(Properties properties) {
}

}