Skip to main content
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.

RuleDescription
@{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).
@{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, ...}: Automatically appends the SQL fragment if it generates non-null parameters (or contains ${...} injection).
  • @{ifand, test, ...}: Applies the subsequent logic only when the test expression is true.

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​

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 = ?
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.

  • @{or, ...}: Automatically appends the SQL fragment if it generates non-null parameters.
  • @{ifor, test, ...}: Applies the subsequent logic only when the test expression is true.

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 = ?
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.

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

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 (?, ?, ?)
Usage 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, ...}: Appends assignment when the parameter is not null, and automatically manages commas.
  • @{ifset, test, ...}: Effective only when the test expression is true.

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 = ?

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, no manual commas):

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 remove the comma from the previous condition
WHERE id = :id
Note

Identifies and adds commas automatically. Does not add a comma if the statement starts with set.

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.
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

CASE, WHEN, ELSE Rules​

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

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'
Usage Note
  • Mode Switching: Switch between Switch / If-Else modes based on the presence of the first parameter.
  • 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.

1. Register Macro (Java)

// Register in API Binder or Registry
registry.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 = ?
Note

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

MD5 Rules​

Calculates MD5 for parameters.

  • @{md5, expr}: Calculates the MD5 of the expr expression result and uses the result as a SQL parameter.

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)

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).

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

PAIRS Rules​

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:

  • :k: Key of Map or Index of List/Array.
  • :v: Value of Map or Element Value of List/Array.
  • :i: Current index (starts from 0).

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