Hint

This article is generated by AI translation.

Statement Generation Rules

This chapter introduces how to use SQL enhancement rules to simplify dynamic SQL development. Compared to XML tags, they can more intelligently handle empty parameters, connectors (AND/OR), and comma separators.

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 all arguments in the SQL fragment are non-null	Takes effect when the OGNL condition expression is true
Null arguments	When an argument is null, 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. Auto Prefix Removal: The rule automatically handles the WHERE keyword and redundant leading AND. For example, WHERE @{and, AND x=1} will be optimized to WHERE x=1.
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
      -- Only effective when userId is not null and length > 0
      @{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.
• Auto Prefix Handling: Intelligently identifies context to automatically remove or add necessary AND.
• Auto Downgrade: If the preceding connector is OR, it automatically adapts.

---

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
      -- Only effective when email contains '@'
      @{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

Consistent with AND rules, capable of auto-completing WHERE and auto-correcting connectors (OR).

---

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 (?,?,?)
      -- Only effective when ids collection is not empty
      @{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).

---

SET, IFSET Rules

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

• @{set, sql fragment}: Appends assignment when the argument is not null, 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 user, status is optional.

update users 
set update_time = now()      -- Fixed column
    @{set, status = :status} -- Automatically handles comma
    -- Only update status when it is not 'disabled'
    @{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()
    <if test="status != null">
        , status = #{status}
    </if>
    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 user
  fixed_col = 1        -- No comma at the end
  @{set, name = :name} -- Rule automatically handles preceding comma (generates ", name = ?")
  @{set, age = :age}

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>
    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
    @{ifmacro, status > 2, includeSeq} -- Conditional Reference

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"/>
    <if test="status > 2">
         <include refid="includeSeq"/>
    </if>
</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 parameter must be referenced with the : prefix (e.g., :loginPassword); otherwise, it will not be recognized as a parameter reference.

Using Rules

Example: MD5 password matching.

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>