7.1 Statement Generation Rules

SQL enhancement rules simplify dynamic SQL development. Compared with XML tags, rules handle empty parameters, connectors (AND/OR), and comma separators more intelligently.

Rule	Description
@{and} / @{ifand}	Smartly append AND conditions.
@{or} / @{ifor}	Smartly append OR conditions.
@{in} / @{ifin}	Expand collection parameters to IN (v1, v2).
@{set} / @{ifset}	Smartly handle commas in SET clause of UPDATE statements.
@{if} / @{iftext}	General condition check (Parse / Direct).
@{text}	Output text fragment as-is (no argument parsing).
@{case} / @{when} / @{else}	Multi-branch logic (Switch / If-Else).
@{macro} / @{ifmacro}	Include predefined SQL macro fragments.
@{md5}	Calculate parameter MD5 value.
@{uuid32} / @{uuid36}	Generate UUID.
@{pairs}	Iterate over Map/List to generate parameter templates.

---

AND, IFAND Rules

Solves the problem of dynamically concatenating AND conditions in WHERE clauses.

• @{and, sql fragment}: Automatically appends the SQL fragment when arguments in the fragment are non-null (or it contains ${...} injection).
• @{ifand, OGNL condition, sql fragment}: Appends the SQL fragment when the OGNL condition expression is true (arguments are retained even if null).

Difference between and and ifand

Feature	@{and, sql fragment}	@{ifand, OGNL condition, sql fragment}
Activation	Takes effect when at least one argument in the SQL fragment are non-null	Takes effect when the OGNL condition expression is true
Null arguments	When all arguments are null or there are no arguments, the entire fragment is discarded	When the condition is met, null arguments are retained
Typical use	Concise syntax with automatic null checking	Custom judgment logic needed

Features

1. Context-aware connectors: The rule inspects the preceding WHERE, AND, or OR to decide whether to add a connector. Write the predicate directly, for example WHERE @{and, x=:x}; do not add a leading AND inside the rule.
2. Smart Null Discard:
   • By default, if :val in @{and, col=:val} is empty (null), the entire fragment is discarded.
   • Exception: If the fragment contains ${...} dynamic injection, it is forced to be retained even if the parameter is empty (preventing accidental deletion of filter logic).

Examples

Using Rules

Example: status is required, userId is optional. No need to manually handle WHERE/AND connectors.

select * from users 
where status = :status      -- Fixed condition
      @{and, uid = :userId} -- Automatically handles AND prefix
      -- To apply an explicit condition, replace the preceding rule with:
      -- @{ifand, userId != null && userId.length() > 0, uid = :userId}

Generated SQL (status=1, userId='abc')

select * from users
where status = ? and uid = ? 

XML Comparison

<select id="queryUser">
    select * from users
    where status = #{status}
    <if test="userId != null">
        and uid = #{userId}
    </if>
</select>

Smart Features

• Auto WHERE Completion: If the rule is at the beginning of the condition, it automatically adds the WHERE keyword.
• Context-aware Connectors: Checks preceding SQL to decide whether to add AND; it does not strip a handwritten leading AND from the rule body.
• Preserve Existing Connectors: If preceding SQL ends with OR, no extra AND is added.

---

OR, IFOR Rules

Solves the problem of dynamically concatenating OR conditions in WHERE clauses. Symmetric with the AND rule.

• @{or, sql fragment}: Automatically appends the SQL fragment when arguments are non-null.
• @{ifor, OGNL condition, sql fragment}: Appends the SQL fragment when the OGNL condition is true (arguments are retained even if null).

Using Rules

Example: Match username or email. Automatically handles OR connectors.

select * from users 
where username = :username 
      @{or, email = :email} -- Automatically connects with OR
      -- To apply an explicit condition, replace the preceding rule with:
      -- @{ifor, email != null && email.contains("@"), email = :email}

Generated SQL (username='admin', email='a@b.c')

select * from users
where username = ? or email = ?

XML Comparison

<select id="queryUser">
    select * from users
    where username = #{username}
    <if test="email != null">
        or email = #{email}
    </if>
</select>

Smart Features

Like the AND rule, it adds WHERE or OR according to preceding SQL. Write the condition directly inside the rule, without a leading connector.

---

IN, IFIN Rules

Used to simplify concatenating IN clauses by automatically flattening collection parameters. Supports List, arrays (including primitive type arrays such as int[]).

• @{in, :param}: Automatically flattens collection/array parameters into (?, ?, ...).
• @{ifin, OGNL condition, :param}: Effective only when the OGNL condition is true.

Using Rules

Example: Query users whose ID is in a list.

select * from users
where status = :status
      @{in, and id in :ids} -- Automatically expands to id in (?,?,?)
      -- To apply an explicit condition, replace the preceding rule with:
      -- @{ifin, ids != null && ids.size() > 0, and id in :ids}

Generated SQL (ids=[1,2,3])

select * from users
where status = ? and id in (?, ?, ?)

XML Comparison

<select id="queryUser">
    select * from users
    where status = #{status}
    <if test="ids != null and ids.size() > 0">
        and id in 
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </if>
</select>

Note

This rule does not automatically complete AND/OR prefixes; you need to write them manually within the rule (as and id in ... in the example). An empty or null collection emits no content. If an empty collection should match no rows, return an empty result before querying or explicitly append 1=0; do not let an omitted condition broaden a query or modification.

---

SET, IFSET Rules

Dedicated to UPDATE statements to solve the problem of comma concatenation during dynamic column updates.

• @{set, sql fragment}: Appends an assignment and automatically manages commas. Unlike and/or, set allows argument values to be null.
• @{ifset, OGNL condition, sql fragment}: Effective only when the OGNL condition is true.

Using Rules

Example: Update a user; a null status is also written as NULL.

update users 
set update_time = now()      -- Fixed column
    @{set, status = :status} -- Automatically handles comma
    -- To update conditionally, replace the preceding rule with:
    -- @{ifset, status != null && status != 'disabled', status = :status}
where uid = :uid

Generated SQL (status='Active')

update users 
set update_time = now(), status = ?
where uid = ?

XML Comparison

<update id="updateUser">
    update users 
    set update_time = now()
    , status = #{status}
    where uid = #{uid}
</update>

Tips

When mixing manual SQL (e.g., fixed_col = 1) between @{set} rules, no manual comma is needed. The rule engine automatically detects preceding content and intelligently supplements commas. Manually adding commas may lead to syntax errors in some dynamic scenarios.

Recommended style (rules at the end)

update users set
  fixed_col = 1        -- No comma at the end
  @{set, name = :name} -- Rule automatically handles preceding comma (generates ", name = ?")
  @{set, age = :age}
where id = :id

Problematic style (when rules do not match)

UPDATE tb_user SET
    @{set, name = :name},  -- ❌ Rule cannot remove the trailing comma
    fixed_col = 123,
    @{set, email = :email} -- ❌ Rule won't add a new comma but also won't remove the one from the previous condition
WHERE id = :id

---

IF, IFTEXT Rules

General conditional judgment rules. When the test expression is true, include content in the final SQL.

Although similar functionality, they handle content very differently:

• @{if, test, content}: Smart Parsing. Fully parses content, supporting nested dynamic rules (like @{in}) and parameter placeholders. This is the most common way.
• @{iftext, test, content}: Native Raw Output. Performs NO parsing on content, appending it directly to SQL as-is. Used for injecting special keywords or syntax fragments that do not support parameterization.

Using Rules

select * from users where 1=1
    -- 1. Standard @{if}: Supports parsing internal @{in} and parameter :name
    @{if, hasName, and name = :name}
    @{if, idList != null, and id in @{in, :idList}}

    -- 2. Raw @{iftext}: Injects SQL fragment as-is (no parameter parsing)
    @{iftext, status > 2, and age = 36 }

Generated SQL (hasName=true, idList=[1,2], status=3)

select * from users where 1=1
    and name = ?          -- @{if} parses parameters normally
    and id in (?, ?)      -- @{if} allows nested @{in}
    and age = 36          -- @{iftext} performs raw concatenation

XML Comparison

<select id="queryUser">
    select * from users where 1=1
    <!-- @{if} corresponds to standard <if> -->
    <if test="hasName">
        and name = #{name}
    </if>
    <if test="idList != null">
        and id in ...
    </if>

    <!-- @{iftext} corresponds to pure text concatenation -->
    <if test="status > 2">
        and age = 36
    </if>
</select>

---

TEXT Rule

Outputs a text fragment as-is, without parsing argument placeholders or nested rules.

• @{text, content}: Appends content directly to the SQL as-is.

Example

select * from users where 1=1
    @{text, and status = 'active'}

Generated SQL

select * from users where 1=1
    and status = 'active'

Difference from iftext

• @{text, content} outputs unconditionally, equivalent to @{iftext, , content}.
• @{iftext, test, content} only outputs when the test condition is true.

---

CASE, WHEN, ELSE Rules

Provides branch logic during SQL generation stage, supporting both Switch (value matching) and If-Else (condition matching) modes.

Switch Mode

Value Match: @{case} first parameter is a variable.

select * from users where @{case, userType, 
    @{when, 'admin',   role = 'administrator'}, 
    @{when, 'manager', role = 'manager'}, 
    @{else,            role = 'visitor'} 
}

Generated SQL (userType='admin')

select * from users
where role = 'administrator'

If-Else Mode

Expression Match: @{case} first parameter is empty.

select * from users where @{case, , 
    @{when, userType == 'admin',   role = 'administrator'}, 
    @{when, userType == 'manager', role = 'manager'}, 
    @{else,                        role = 'visitor'} 
}

Generated SQL (userType='admin')

select * from users
where role = 'administrator'

XML Comparison

<select id="queryUser">
    select * from users where
    <choose>
        <when test="userType == 'admin'">role = 'administrator'</when>
        <when test="userType == 'manager'">role = 'manager'</when>
        <otherwise>role = 'visitor'</otherwise>
    </choose>
</select>

Usage Note

• Mode Switching: Switch between Switch / If-Else modes based on whether the first parameter of @{case} is present.
• Switch Value Matching: The val in @{when, val} is evaluated via OGNL, then compared with the evaluated result of @{case, expr} using equals(). Mismatched types (e.g., Integer(1) vs "1") will fall back to String.valueOf() comparison.
• Else: @{else} must be written at the end.
• Default: If no match and no else, outputs an empty string.

---

MACRO, IFMACRO Rules

Used to include predefined SQL fragments (macros) into the final SQL, similar to the <include> tag in XML mapping files.

• @{macro, name}: references the SQL macro named name.
• @{ifmacro, test_expr, name}: references the SQL macro named name when test_expr is true.

Using Rules

1. Register Macro (Java)

// Register via Configuration
Configuration config = new Configuration();
config.addMacro("includeSeq", "and seq = :seq");

2. Reference Macro (SQL)

select * from users where
    status = :status
    @{macro, includeSeq} -- Reference Macro
    -- For conditional inclusion, replace the preceding rule with:
    -- @{ifmacro, status > 2, includeSeq}

Generated SQL (status=3)

select * from users
where status = ? and seq = ?

XML Comparison

<!-- Definition -->
<sql id="includeSeq">
    and seq = #{seq}
</sql>

<!-- Reference -->
<select id="queryUser">
    select * from users where status = #{status}
    <include refid="includeSeq"/>
    <!-- For conditional inclusion, wrap the preceding include in if; do not include it twice. -->
</select>

Note

Referencing a non-existent SQL macro will cause an execution error.

Defining Macros in XML

SQL fragments defined with <sql id="xxx"> in Mapper XML files are automatically registered as macros, named as namespace.id. Therefore, you can also reference XML-defined SQL fragments in rules via @{macro, namespace.sqlId}.

---

MD5 Rule

Calculates the MD5 hash of a parameter value and binds the result as a SQL argument.

• @{md5, :param}: Takes the value of :param, computes its MD5, and binds it as a ? placeholder.

Note

The content must parse into exactly one bound parameter, such as :loginPassword, #{loginPassword}, or ?. A bare name is not a bound parameter. MD5 is hashing, not encryption, and should not be used for new password storage; this example only illustrates compatibility with an existing MD5 field.

Using Rules

Example: Hash matching for an existing MD5 password field.

select * from users 
where account = :loginName 
  and password = @{md5, :loginPassword}

Generated SQL

select * from users
where account = ? and password = ? -- Parameter value is MD5(loginPassword)

Traditional Way

Usually requires calculating MD5 in the Java layer before passing into SQL.

String md5Pwd = DigestUtils.md5Hex(password);
// Then pass md5Pwd into SQL parameters

---

UUID Rules

Automatically generates UUID and uses it as a SQL parameter.

• @{uuid32}: Generates 32-length UUID (no - separator).
• @{uuid36}: Generates 36-length UUID (with - separator).

Using Rules

Example: Automatically generate ID on insert.

insert into users (id, uid, name, time) 
values (:id, @{uuid32}, :name, now());

Generated SQL

insert into users (id, uid, name, time) 
values (?, ?, ?, now()); -- Second parameter is the generated UUID

Traditional Way

Usually requires generating UUID in the Java layer.

String uid = UUID.randomUUID().toString().replace("-", "");
// Then pass uid into SQL parameters

---

PAIRS Rule

Used to iterate collections (Map/List/Array) and generate SQL fragments based on a template.

• @{pairs, :collection, template}: Iterates :collection, applying template to each element.

Template Variables (fixed names, not configurable):

Variable	Map scenario	List/Array scenario
:k	Map Key	Element index (as string, e.g., "0", "1")
:v	Map Value	Element value
:i	Iteration index (starts from 0)	Iteration index (starts from 0)

Using Rules

Example: Write Map data to Redis HASH structure.

-- Assume parameter arg0 is a Map: {"field1": "val1", "field2": "val2"}
HSET myKey1 @{pairs, :arg0, :k :v}

Generated Command

HSET myKey1 field1 val1 field2 val2

XML Comparison

Similar to MyBatis <foreach> tag:

HSET myKey1
<foreach collection="arg0" index="k" item="v" separator=" ">
    #{k} #{v}
</foreach>