In the previous article Optimizing Mybatis in-query through interceptor Interceptor In, we have resolved the case of an empty in() list in the mybatis query.

However, because the list in the foreach tag is empty, not only is an in() empty list, but another common situation occurs when data is inserted in batches, as follows:

INSERT INTO table1(
    column1
) VALUES
<foreach collection="list" item="item" index="index" separator="," >
(
    #{item.column1}
)
</foreach>

So we decided to upgrade again based on in() to support values(), which basically overrides the sql error caused by the empty list in the foreach tag

The removeInterference function is the same as before and will not be repeated.

The main modification is the isHaveEmptyList function, which is as follows:

private static Boolean isHaveEmptyList(String sql) {
    sql = removeInterference(sql);
    List<Integer> indexList = Lists.newArrayList();
    String keyWorld;
    if (StringUtils.containsIgnoreCase(sql, "insert")) {
        keyWorld="values";
        Pattern pattern = Pattern.compile("\\s(?i)"+keyWorld);
        Matcher matcher = pattern.matcher(sql);
        while (matcher.find()) {
            indexList.add(matcher.start());
        }
    } else {
        keyWorld="in";
        Pattern pattern = Pattern.compile("\\s(?i)"+keyWorld);
        Matcher matcher = pattern.matcher(sql);
        while (matcher.find()) {
            indexList.add(matcher.start());
        }
    }
    return checkHaveEmptyList(sql,keyWorld,indexList);
}

private static Boolean checkHaveEmptyList(String sql,String keyWorld, List<Integer> indexList) {
    Boolean isHaveEmptyList = Boolean.FALSE;
    Pattern p2 = Pattern.compile("(?<=\\()(.+?)(?=\\))");
    for (Integer index : indexList) {
        String subSql = sql.substring(index + keyWorld.length()+1);
        if (StringUtils.isEmpty(subSql)) {
            isHaveEmptyList = Boolean.TRUE;
            break;
        }
        Boolean flag = subSql.startsWith("(")
                || subSql.startsWith(" ")
                || subSql.startsWith("\n")
                || subSql.startsWith("\r");
        if (!flag) {
            continue;
        }
        subSql = subSql.trim();
        if (!subSql.startsWith("(")) {
            isHaveEmptyList = Boolean.TRUE;
            break;
        }
        Matcher m2 = p2.matcher(subSql);
        if (m2.find()) {
            if (StringUtils.isEmpty(m2.group())) {
                isHaveEmptyList = Boolean.TRUE;
                break;
            }
        }
    }
    return isHaveEmptyList;
}

The key world is used to identify in() or values(), and the checkHaveEmptyList method is used to determine if there is an empty list.

If there are other cases, you are welcome to add, and you are welcome to give me valuable suggestions.