Hint

This article is generated by AI translation.

Dynamic Rules

These rules handle simple dynamic Command, such as only including arguments when non-empty. Rules available:

Rule	Description
@{and, queryExpr}	AND rule: if queryExpr produces non-empty conditions, it is appended to the final SQL.
@{ifand, testExpr, queryExpr}	IFAND rule: when testExpr is true, queryExpr is processed with the AND rule.
@{or, queryExpr}	OR rule: if queryExpr produces non-empty conditions, it is appended to the final SQL.
@{ifor, testExpr, queryExpr}	IFOR rule: when testExpr is true, queryExpr is processed with the OR rule.
@{in, queryExpr}	IN rule: queryExpr may contain one argument; when non-empty it is expanded to (?,?,?,...).
@{ifin, testExpr, queryExpr}	IFIN rule: like IN, but only when testExpr is true.
@{set, queryExpr}	SET rule: simplifies conditional updates in the set clause of update statements.
@{ifset, testExpr, queryExpr}	IFSET rule: like SET, but only when testExpr is true.

AND / IFAND

AND adds conditions only when they are non-empty; IFAND adds an activation expression.

Use rule for arguments

select * from users 
where status = :status      -- status condition
      @{and, uid = :userId} -- uid condition

Equivalent: XML Mapper

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

<!-- Mapper can also use rules -->
<select id="queryUser">
  select * from users
  where status = #{status} @{and, uid = :userId}
</select>

Equivalent: Java code

// Build SQL and args
String querySQL = "select * from users where status = ?";
Object queryArg = null;
if (userId != null) {
    querySQL = querySQL + " and uid = ?";
    queryArg = new Object[]{ 1, userId};
} else {
    queryArg = new Object[]{ 1};
}

// Execute
jdbc.queryForList(querySQL, queryArg);

IFAND uses an expression to decide whether to apply the rule.

Programmatic: enable condition in rule

select * from users 
where status = :arg0 
      @{ifand, arg1 != null, uid = :arg1}

• arg1 != null decides whether to activate the rule.

XML: enable condition in rule

<select id="queryUser">
    select * from users
    where status = #{status} @{ifand, userId != null, uid = :userId}
</select>

• userId != null decides whether to activate the rule.

info

@{and} and @{ifand} adapt to these SQL shapes:

select * from users @{and, uid = :userId} -- auto-completes where
select * from users where @{and, uid = :userId} -- keeps where if empty
select * from users where status = :status @{and, uid = :userId} -- auto-completes and
select * from users where status = :status and @{and, uid = :userId} -- keeps and if empty
select * from users where status = :status or @{and, uid = :userId} -- downgrades to OR rule

OR / IFOR

OR/IFOR are analogous to AND/IFAND but join with or. Usage mirrors the AND rules.

IN / IFIN

IN simplifies building in clauses; IFIN adds an activation expression.

Generate in clause with rule

select * from users
where status = :arg0
      @{in,and id in :arg1} -- produces id in (?,?,?)

Equivalent: XML Mapper

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

<!-- Mapper can also use rules -->
<select id="queryUser">
    select * from users 
    where status = #{status} @{in,and id in :ids}
</select>

Equivalent: Java code

// Build SQL and args
StringBuilder querySQL = new StringBuilder("select * from user_info where status =?");
List<Object> queryArgs = new ArrayList<>();
queryArgs.add("1");

Object[] ids = new Object[] { 1, 2, 3 };
if (ids != null && ids.length > 0) {
    querySQL.append("and id in (");
    for (int i = 0; i < ids.length; i++) {
        if (i == 0) {
            querySQL.append("?");
        } else {
            querySQL.append(", ?");
        }
        queryArgs.add(ids[i]);
    }
    querySQL.append(")");
}

// Execute
jdbc.queryForList(querySQL.toString(), queryArgs.toArray());

IFIN uses an expression to decide whether to apply the IN rule.

Programmatic: IFIN rule

select * from users
where status = :arg0
      @{ifin, arg1.size() > 2 , and id in :arg1}

• Only when the argument count > 2 will the IN condition be added.

XML: IFIN rule

<select id="queryUser">
    select * from users 
    where status = #{status} @{in, ids.size() > 2 ,and id in :ids}
</select>

• Only when the argument count > 2 will the IN condition be added.

info

IN rule limits:

• Only one in condition per rule use.
• IN does not auto-complete missing keywords like and/or/where.
  • For example, select * from users @{in,id in :ids} will be invalid SQL.

info

IN supports arguments of type:

• Single object
• Collection/array wrapped by SqlArg
• Arrays
• Any Iterable subclass, including Collection/List/Set

SET / IFSET

SET simplifies conditional updates in the set clause of update; IFSET adds an activation expression.

Use rule to build SET

update users 
set update_time = now()    -- update update_time
    @{set, status = :arg0} -- update status when arg0 is non-empty (commas handled automatically)
where uid = :arg1

Equivalent: XML Mapper

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

<!-- Mapper can also use rules -->
<update id="updateUser">
    update users 
    set update_time = now() 
        @{set, status = :status}
    where
        uid = #{uid}
</update>

Equivalent: Java code

// Build SQL and args
StringBuilder querySQL = new StringBuilder("update users set update_time = now()");
List<Object> queryArgs = new ArrayList<>();

if (status != null) {
    querySQL.append(", status = ?");
    queryArgs.add(status);
}
querySQL.append(" where uid =?");
queryArgs.add(uid);

// Execute
jdbc.executeUpdate(querySQL.toString(), queryArgs.toArray());

IFSET uses an expression to decide whether to apply the update item.

Programmatic: IFSET rule

update users
set update_time = now() 
    @{ifset, arg0 != 2 ,status = :arg0}
where uid = :arg1

• Only when status is not equal to 2 will it be updated.

XML: IFSET rule

<update id="queryUser">
    update users
    set update_time = now()
        @{ifset, status != 2 ,status = :status}
    where
        uid = #{uid}
</update>

• Only when status is not equal to 2 will it be updated.