Last week, adding just a single discount_rate column to the products table meant fixing six Mapper XML files. The cause was simple: the same WHERE condition deleted = 0 AND status = 'ACTIVE' and the same SELECT column list had been copy-pasted for search, count aggregation, and listing display, over and over. Forgetting to fix even one file leads to an annoying bug where the search results and the count display numbers don’t match.
MyBatis has sql and include tags to eliminate this duplication. This article walks through working XML examples covering fragment definitions, reuse across different Mappers, and combining them with dynamic SQL tags. The dynamic SQL tag syntax assumes you’ve read the previous article. If you haven’t set up the basic Mapper configuration yet, please check the Mapper implementation guide first.
Defining SQL Fragments with the sql Tag
The sql tag lets you give a name to and store a fragment of SQL. You define it with an id inside the same Mapper XML as your select statements. You can share not just WHERE conditions but also SELECT column lists.
<sql id="activeCondition">
deleted = 0 AND status = 'ACTIVE'
</sql>
<sql id="productColumns">
id, name, category, price, status, updated_at
</sql>Even if the table gains new columns, the only place you’ll need to fix is productColumns.
Inserting Fragments Within the Same Mapper with the include Tag
You can insert a fragment defined with <include refid="id"/>.
<select id="search" resultType="Product">
SELECT <include refid="productColumns"/>
FROM products
WHERE <include refid="activeCondition"/>
</select>
<select id="countByCategory" resultType="int">
SELECT COUNT(*) FROM products
WHERE <include refid="activeCondition"/>
AND category = #{category}
</select>This resolution happens as static SQL when the Mapper XML is loaded at application startup, not each time the SQL executes. Since the string isn’t rebuilt on every execution, there’s no performance impact.
Referencing Fragments from a Different Mapper XML
Fragments can also be referenced from a different Mapper XML, by specifying refid in the form namespace.id. There’s something to watch out for here: if you use a fragment with unqualified column names, like activeCondition, in a JOIN across multiple tables, you’ll get an ambiguous column reference error if the joined table has a column with the same name. It’s safer to design common fragments that are referenced from other Mappers so they can accept a table alias.
<!-- CommonSqlMapper.xml -->
<mapper namespace="com.example.shop.mapper.CommonSqlMapper">
<sql id="aliasedActiveCondition">
${alias}.deleted = 0 AND ${alias}.status = 'ACTIVE'
</sql>
</mapper><!-- Referenced from OrderMapper.xml -->
<select id="searchOrders" resultType="Order">
SELECT o.* FROM orders o
JOIN products p ON o.product_id = p.id
WHERE <include refid="com.example.shop.mapper.CommonSqlMapper.aliasedActiveCondition">
<property name="alias" value="o"/>
</include>
</select>The namespace must match the fully qualified name of the Mapper interface exactly; a typo will cause resolution to fail.
Passing Parameters with property
By passing property as a child element of include, the caller can vary the contents of the fragment. The alias you pass is expanded inside the fragment as ${alias}, which is a simple string substitution. Limit the values you pass to things that can be hardcoded, like table aliases. Passing user input directly opens the door to SQL injection. The scope of a property is confined to that particular include call.
Combining with Dynamic SQL Tags
A common column list fragment naturally combines with an IN clause built using foreach.
<select id="searchByIds" resultType="Product">
SELECT <include refid="productColumns"/>
FROM products
WHERE <include refid="activeCondition"/>
AND id IN
<foreach item="id" collection="ids" open="(" separator="," close=")">
#{id}
</foreach>
</select>Even in cases where you switch conditions with a choose clause, you can delegate common conditions to include and build only the individual conditions with dynamic SQL tags. Detailed behavior for where, set, trim, and choose is covered in the dynamic SQL article.
Consolidating into a Dedicated Common Fragment Mapper
As a project grows, having a mix of Mappers that hold fragments and Mappers that don’t makes things confusing. A recommended structure is to consolidate common conditions and column lists into a dedicated CommonSqlMapper.xml.
mapper/
CommonSqlMapper.xml # Defines only common fragments
ProductMapper.xml # References CommonSqlMapper.xxx
OrderMapper.xml # References CommonSqlMapper.xxxRemove the activeCondition that was written directly in the existing ProductMapper.xml, and rewrite it to reference only the definition on the CommonSqlMapper.xml side. No corresponding Java interface is needed — an XML with just a mapper namespace is sufficient.
Errors from Namespace Misconfiguration
A namespace misconfiguration is the point where people trip up the most. If you make a typo in the package name, the refid can’t be resolved when the Mapper XML is loaded at startup, resulting in an IncompleteElementException.
Could not find SQL statement to include with refid
'com.example.shop.mapper.CommonSqlMapper.aliasedActiveCondition'Compare the namespace spelling against the mapper namespace in CommonSqlMapper.xml. Another cause is a missing mybatis.mapper-locations configuration, meaning CommonSqlMapper.xml itself isn’t being loaded at all.
Summary
The basic pattern for eliminating SQL duplication is to define fragments with sql and insert them with include, either within the same Mapper or from a different Mapper XML. property lets you absorb variable parts like table aliases, and it combines well with foreach and choose. Once your fragments grow in number, consolidating them into CommonSqlMapper.xml removes the guesswork.
For result mapping with resultMap, see the association/collection article, and for processing that spans transaction boundaries, see the @Transactional article as well.
