mybatis学习笔记四——动态 SQL

小灰灰 2021-12-10 04:13 322阅读 0赞

### 1.choose, when, otherwise（相当于像 Java 中的 switch 语句。） ###

<select id="findActiveBlogLike"
         resultType="Blog">
      SELECT * FROM BLOG WHERE state = ‘ACTIVE’
      <choose>
        <when test="title != null">
          AND title like #{title}
        </when>
        <when test="author != null and author.name != null">
          AND author_name like #{author.name}
        </when>
        <otherwise>
          AND featured = 1
        </otherwise>
      </choose>
    </select>

### 2.where if 语句（用的比较多，注意int类型和String类型） ###

<select id="findActiveBlogLike"
         resultType="Blog">
      SELECT * FROM BLOG
      <where>
        <if test="state != null">
             state = #{state}
        </if>
        <if test="title != null">
            AND title like #{title}
        </if>
        <if test="author != null and author.name != null">
            AND author_name like #{author.name}
        </if>
      </where>
    </select>

### 3.foreach   通常是在构建 IN 条件语句的时候 ###

<select id="selectPostIn" resultType="domain.blog.Post">
      SELECT *
      FROM POST P
      WHERE ID in
      <foreach item="item" index="index" collection="list"
          open="(" separator="," close=")">
            #{item}
      </foreach>
    </select>

### 4.update中的 set if 语句 ###

<update id="updateAuthorIfNecessary">
      update Author
        <set>
          <if test="username != null">username=#{username},</if>
          <if test="password != null">password=#{password},</if>
          <if test="email != null">email=#{email},</if>
          <if test="bio != null">bio=#{bio}</if>
        </set>
      where id=#{id}
    </update>

### 5.trim（自定义规则） ###

这里删去的是后缀值，同时添加了前缀值。

### 6.bind  ###

元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文。

<select id="selectBlogsLike" resultType="Blog">
      <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
      SELECT * FROM BLOG
      WHERE title LIKE #{pattern}
    </select>

### 7.多数据库支持\* ###

### 8.动态 SQL 中的可插拔脚本语言\* ###