Simplify MyBatis IN Clauses: Custom Extension Replaces foreach Template and Doubles SQL Writing Efficiency

Problem

MyBatis requires a verbose <foreach> block to implement an IN clause, e.g. selecting users by a list of IDs. The mapper XML becomes long and error‑prone.

Solution comparison

Option 1: MyBatis plugin

Intercepting StatementHandler.prepare can obtain the BoundSql, but at that stage the SQL already contains only ? placeholders; the original in #{idList} text is unavailable, so replacement is impossible.

Option 2: Extend SqlNode

Creating a custom XML tag (e.g. <in collection="idList"/>) and a corresponding InNodeHandler / InSqlNode could parse the XML into a SqlNode. MyBatis does not expose a direct configuration point for new NodeHandler, so registration would have to be done via a custom LanguageDriver, which still leaves a small amount of boilerplate.

Option 3: Extend LanguageDriver (chosen)

The LanguageDriver interface converts XML or annotation strings into SqlSource. The default implementation XMLLanguageDriver builds an XMLScriptBuilder that parses the mapper XML. By subclassing XMLLanguageDriver and overriding both createSqlSource methods, the raw XML/SQL can be inspected, a pattern \s+in\s+#{([^}]+)}\s* (case‑insensitive) can be detected, and it can be replaced with a generated <foreach> element.

Core implementation

1. Override annotation‑based createSqlSource

private static final Pattern IN_PATTERN = Pattern.compile("\\s+in\\s+#{([^}]+)}\\s*", Pattern.CASE_INSENSITIVE);
@Override
public SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType) {
    Matcher matcher = IN_PATTERN.matcher(script);
    if (matcher.find()) {
        script = matcher.replaceAll(" in <foreach collection=\"$1\" item=\"item\" open=\"(\" separator=\",\" close=\")\">#{item}</foreach>");
        if (!script.startsWith("<script>")) {
            script = "<script>" + script + "</script>";
        }
    }
    return super.createSqlSource(configuration, script, parameterType);
}

2. Override XML‑based createSqlSource

@Override
public SqlSource createSqlSource(Configuration configuration, XNode script, Class<?> parameterType) {
    // 1. Get the original DOM node
    Node originalNode = script.getNode();
    processNode(originalNode); // recursive processing
    // 2. Build a new XNode from the modified DOM
    XNode processedNode = script.newXNode(originalNode);
    XMLScriptBuilder builder = new XMLScriptBuilder(configuration, processedNode, parameterType);
    return builder.parseScriptNode();
}

private void processNode(Node node) {
    if (node == null) return;
    if (node.getNodeType() == Node.TEXT_NODE) {
        processTextNode((Text) node);
        return;
    }
    if (node.getNodeType() == Node.ELEMENT_NODE) {
        NodeList childNodes = node.getChildNodes();
        List<Node> children = new ArrayList<>(childNodes.getLength());
        for (int i = 0; i < childNodes.getLength(); i++) {
            children.add(childNodes.item(i));
        }
        for (Node child : children) {
            processNode(child);
        }
    }
}

private void processTextNode(Text textNode) {
    String originalText = textNode.getTextContent();
    Matcher matcher = IN_PATTERN.matcher(originalText);
    List<Object> segments = new ArrayList<>();
    int lastEnd = 0;
    while (matcher.find()) {
        if (matcher.start() > lastEnd) {
            segments.add(originalText.substring(lastEnd, matcher.start()));
        }
        segments.add(matcher.toMatchResult());
        lastEnd = matcher.end();
    }
    if (lastEnd < originalText.length()) {
        segments.add(originalText.substring(lastEnd));
    }
    if (segments.stream().noneMatch(s -> s instanceof MatchResult)) {
        return; // no match
    }
    Document doc = textNode.getOwnerDocument();
    Node parent = textNode.getParentNode();
    Node nextSibling = textNode.getNextSibling();
    parent.removeChild(textNode);
    for (Object segment : segments) {
        if (segment instanceof String) {
            String txt = (String) segment;
            if (!txt.isEmpty()) {
                Text newText = doc.createTextNode(txt);
                parent.insertBefore(newText, nextSibling);
            }
        } else if (segment instanceof MatchResult) {
            MatchResult match = (MatchResult) segment;
            String param = match.group(1).trim();
            Element foreach = createForeachElement(doc, param);
            parent.insertBefore(foreach, nextSibling);
        }
    }
}

private Element createForeachElement(Document doc, String parameter) {
    Element foreach = doc.createElement("foreach");
    foreach.setAttribute("collection", parameter);
    foreach.setAttribute("item", "item");
    foreach.setAttribute("open", "(");
    foreach.setAttribute("close", ")");
    foreach.setAttribute("separator", ",");
    Text itemText = doc.createTextNode("#{item}");
    foreach.appendChild(itemText);
    return foreach;
}

3. Configuration

Register the driver globally:

<settings>
    <setting name="defaultScriptingLanguage" value="com.yourpkg.SimplifiedInLanguageDriver"/>
</settings>

Or locally on a mapper interface or method:

@Lang(SimplifiedInLanguageDriver.class)
public interface UserMapper {
    List<User> selectUsersByIds(@Param("userIds") List<Integer> userIds, @Param("query") QueryParam query);
}

Effect demonstration

1. Simplified mapper XML

<!-- No foreach needed – write directly -->
<select id="selectUsersByIds" resultType="User">
    SELECT id, username, age
    FROM user
    WHERE id IN #{userIds}
    AND role_id IN #{query.roleIds}
</select>

2. Java call site

// Prepare parameters
List<Integer> userIds = Arrays.asList(101, 102, 103);
QueryParam query = new QueryParam();
query.setRoleIds(Arrays.asList(2, 3));

// Invoke mapper (no code change needed)
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.selectUsersByIds(userIds, query);

3. Generated SQL

SELECT id, username, age
FROM user
WHERE id IN ( ?, ?, ? )
AND role_id IN ( ?, ? )

Conclusion

Extending LanguageDriver replaces the repetitive <foreach> boilerplate with a concise in #{parameter} syntax. This reduces XML size, improves readability, and lowers the risk of syntax errors while preserving full MyBatis functionality.