Mastering MyBatis sqlFragment: Reuse SQL Snippets Efficiently

What is sqlFragment?

MyBatis sqlFragment (SQL fragment) allows reusable pieces of SQL to avoid duplication. It can be used by select, insert, update, and delete tags via the <include> element.

Example definition and usage

<sql id="studentProperties">
    select 
      stud_id as studId,
      name, email,
      dob,
      phone
    from students
</sql>

<select id="countAll" resultType="int">
    select count(1) from (
      <include refid="studentProperties"/>
    ) tmp
</select>

<select id="findAll" resultType="Student" parameterType="map">
    select * from (
      <include refid="studentProperties"/>
    ) tmp limit #{offset}, #{pagesize}
</select>

Parsing process of sqlFragment

sqlFragment definitions are stored in the MyBatis Configuration as a Map<String, XNode>:

protected final Map<String, XNode> sqlFragments = new StrictMap<XNode>("XML fragments parsed from previous mappers");

The XMLMapperBuilder parses them with:

// parse sqlFragment
sqlElement(context.evalNodes("/mapper/sql"));
// provide services for select|insert|update|delete
buildStatementFromContext(context.evalNodes("select|insert|update|delete"));

How include tags are expanded

During statement parsing, XMLIncludeTransformer processes <include> elements:

XMLIncludeTransformer includeParser = new XMLIncludeTransformer(configuration, builderAssistant);
includeParser.applyIncludes(context.getNode());

The core method applyIncludes(Node source, Properties variablesContext) recursively replaces each <include> with the referenced sqlFragment, merges variable contexts, and removes the original fragment node.

private void applyIncludes(Node source, final Properties variablesContext) {
    if (source.getNodeName().equals("include")) {
        // resolve refid, merge contexts, recurse
        // replace include node with fragment node
        // insert fragment children before the fragment node
        // remove fragment node
    } else if (source.getNodeType() == Node.ELEMENT_NODE) {
        NodeList children = source.getChildNodes();
        for (int i = 0; i < children.getLength(); i++) {
            applyIncludes(children.item(i), variablesContext);
        }
    } else if (source.getNodeType() == Node.ATTRIBUTE_NODE && !variablesContext.isEmpty()) {
        source.setNodeValue(PropertyParser.parse(source.getNodeValue(), variablesContext));
    } else if (source.getNodeType() == Node.TEXT_NODE && !variablesContext.isEmpty()) {
        source.setNodeValue(PropertyParser.parse(source.getNodeValue(), variablesContext));
    }
}

Step‑by‑step diagram

1. Parse node with <include>:

<select id="countAll" resultType="int">
    select count(1) from (
      <include refid="studentProperties"/>
    ) tmp
</select>

2. Replace <include> with the fragment:

<select id="countAll" resultType="int">
    select count(1) from (
        <sql id="studentProperties">
          select 
            stud_id as studId,
            name, email,
            dob,
            phone
          from students
        </sql>
    ) tmp
</select>

3. Insert fragment child nodes before the fragment node, then remove the fragment node, yielding:

<select id="countAll" resultType="int">
    select count(1) from (
        select 
            stud_id as studId,
            name, email,
            dob,
            phone
          from students
    ) tmp
</select>

The final SQL is assembled by concatenating the text nodes of the select element.