核心标签解析

MyBatis通过XML标签实现SQL的动态组装，无需在Java代码中拼接字符串。以下是常用标签的用法说明。

条件判断：<if> 与 <choose>

<if> 标签根据表达式结果决定是否包含某段SQL：

<select id="queryEmployee" resultType="Staff">
  SELECT * FROM staff_tbl
  WHERE status = 1
  <if test="deptCode != null and deptCode != ''">
    AND department = #{deptCode}
  </if>
  <if test="minSalary != null">
    AND salary >= #{minSalary}
  </if>
</select>

当需要多选一逻辑时，采用 <choose> 组合：

<select id="queryEmployee" resultType="Staff">
  SELECT * FROM staff_tbl
  WHERE status = 1
  <choose>
    <when test="empId != null">
      AND employee_id = #{empId}
    </when>
    <when test="phone != null">
      AND mobile = #{phone}
    </when>
    <otherwise>
      AND hire_date > DATE_SUB(NOW(), INTERVAL 30 DAY)
    </otherwise>
  </choose>
</select>

智能处理：<where>、<set>、<trim>

<where> 标签自动处理WHERE关键字和多余的AND/OR：

<select id="searchProduct" resultType="Goods">
  SELECT sku, title, price FROM merchandise
  <where>
    <if test="category != null">
      category_id = #{category}
    </if>
    <if test="keyword != null">
      AND title LIKE CONCAT('%', #{keyword}, '%')
    </if>
  </where>
</select>

<set> 用于动态UPDATE，自动剔除末尾逗号：

<update id="modifyStock">
  UPDATE inventory
  <set>
    <if test="quantity != null">stock_qty = #{quantity},</if>
    <if test="warehouse != null">warehouse_code = #{warehouse},</if>
    <if test="lastModified != null">update_time = #{lastModified}</if>
  </set>
  WHERE sku_id = #{skuId}
</update>

<trim> 提供更灵活的控制，可自定义前缀、后缀及覆盖字符：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

集合遍历：<foreach>

处理IN条件和批量操作时，<foreach> 不可或缺：

<!-- 批量查询 -->
<select id="fetchByIdList" resultType="Goods">
  SELECT * FROM merchandise WHERE sku_id IN
  <foreach collection="skuArray" item="sid" open="(" separator="," close=")">
    #{sid}
  </foreach>
</select>

<!-- 批量插入 -->
<insert id="batchAdd">
  INSERT INTO merchandise (sku, title, price, stock) VALUES
  <foreach collection="itemList" item="entry" separator=",">
    (#{entry.skuCode}, #{entry.name}, #{entry.amount}, #{entry.reserve})
  </foreach>
</insert>

完整实战案例

场景：订单多维度检索

Mapper接口定义

public interface OrderRepository {
    List<OrderInfo> retrieveOrders(@Param("criteria") OrderQueryRequest request);
}

XML映射配置

<resultMap id="orderResult" type="OrderInfo">
  <id property="orderNo" column="order_number"/>
  <result property="totalAmount" column="pay_amount"/>
  <result property="createTime" column="created_at"/>
</resultMap>

<select id="retrieveOrders" resultMap="orderResult">
  SELECT 
    o.order_number,
    o.pay_amount,
    o.created_at,
    c.customer_name
  FROM trade_order o
  LEFT JOIN customer_info c ON o.buyer_id = c.id
  <where>
    <if test="criteria.startDate != null">
      AND o.created_at >= #{criteria.startDate}
    </if>
    <if test="criteria.endDate != null">
      AND o.created_at <= #{criteria.endDate}
    </if>
    <if test="criteria.statusList != null and criteria.statusList.size > 0">
      AND o.order_status IN
      <foreach collection="criteria.statusList" item="st" open="(" separator="," close=")">
        #{st}
      </foreach>
    </if>
    <choose>
      <when test="criteria.minAmount != null and criteria.maxAmount != null">
        AND o.pay_amount BETWEEN #{criteria.minAmount} AND #{criteria.maxAmount}
      </when>
      <when test="criteria.minAmount != null">
        AND o.pay_amount >= #{criteria.minAmount}
      </when>
      <when test="criteria.maxAmount != null">
        AND o.pay_amount <= #{criteria.maxAmount}
      </when>
    </choose>
  </where>
  ORDER BY o.created_at DESC
</select>

业务层调用

@Component
public class OrderQueryService {
    @Autowired
    private OrderRepository orderRepo;
    
    public PageResult<OrderInfo> search(OrderQueryRequest condition, int page, int size) {
        PageHelper.startPage(page, size);
        List<OrderInfo> records = orderRepo.retrieveOrders(condition);
        return new PageResult<>(records);
    }
}

场景：动态字段更新

Mapper接口

int patchUpdate(@Param("id") Long recordId, @Param("delta") Map<String, Object> changedFields);

动态SET实现

<update id="patchUpdate">
  UPDATE product_catalog
  <set>
    <if test="delta.name != null">product_name = #{delta.name},</if>
    <if test="delta.tagPrice != null">list_price = #{delta.tagPrice},</if>
    <if test="delta.onShelf != null">is_online = #{delta.onShelf},</if>
    update_time = NOW()
  </set>
  WHERE product_id = #{id}
</update>

开发注意事项

• 空值处理：字符串类型建议同时判断 null 和空字符串，数值类型仅需判断 null
• 参数绑定：始终使用 #{var} 语法，禁止字符串拼接传入
• 调试技巧：开启 log4j.logger.java.sql=DEBUG 查看最终执行的SQL语句
• 性能考量：复杂动态SQL可能导致执行计划不稳定，关键业务建议结合数据库索引优化