Mybatis 动态 SQL 写法全攻略：2025 最新案例解析与常见问题解答

<select id="findUser" resultType="User">
  SELECT * FROM users
  <where>
    <if test="username != null">
      AND username = #{username}
    if>
    <if test="age != null">
      AND age = #{age}
    if>
  where>
select>

<select id="searchProducts" resultType="Product">
  SELECT * FROM products
  <where>
    <choose>
      <when test="name != null">
        name LIKE CONCAT('%', #{name}, '%')
      when>
      <when test="categoryId != null">
        category_id = #{categoryId}
      when>
      <otherwise>
        is_hot = 1
      otherwise>
    choose>
  where>
select>

<delete id="deleteUsers">
  DELETE FROM users
  WHERE id IN
  <foreach collection="ids" item="id" open="(" separator="," close=")">
    #{id}
  foreach>
delete>

<sql id="Base_Columns">
  id, username, email, create_time
sql>

<select id="getUser" resultType="User">
  SELECT <include refid="Base_Columns"/> FROM users WHERE id = #{id}
select>

<select id="searchGoods" resultType="Goods">
  SELECT * FROM goods
  <where>
    <if test="name != null">
      AND name LIKE CONCAT('%', #{name}, '%')
    if>
    <if test="minPrice != null and maxPrice != null">
      AND price BETWEEN #{minPrice} AND #{maxPrice}
    if>
    <if test="brandIds != null">
      AND brand_id IN
      <foreach collection="brandIds" item="id" open="(" separator="," close=")">
        #{id}
      foreach>
    if>
  where>
  <if test="orderBy != null">
    ORDER BY ${orderBy} ${sort}
  if>
  LIMIT #{offset}, #{pageSize}
select>

<insert id="batchInsertLogs">
  INSERT INTO logs (event_type, content, operator)
  VALUES
  <foreach collection="logs" item="log" separator=",">
    (#{log.eventType}, #{log.content}, #{log.operator})
  foreach>
insert>

try (SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
  LogMapper mapper = session.getMapper(LogMapper.class);
  for (Log log : logs) {
    mapper.batchInsertLogs(log);
  }
  session.commit();
}

ORDER BY ${orderBy}


ORDER BY 
<if test="orderBy == 'price' or orderBy == 'create_time'">
  ${orderBy}
if>

<resultMap id="OrderWithUserMap" type="Order">
  <association property="user" column="user_id" select="getUser" fetchType="lazy"/>
resultMap>