关于mybatis:四动态-SQLSQL-语句构建器笔记

1、if

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

or
<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  where 1=1
    <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>
</select>

2、choose、when、otherwise

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

3、trim、set
<trim prefix=”” suffix=”” suffixOverrides=”” prefixOverrides=””></trim>
prefix：增加前缀
suffix: 增加后缀
suffixOverrides：去除前缀
prefixOverrides：去除后缀

<insert id="insert" parameterType="com.tortuousroad.groupon.cart.entity.Cart">
        insert into cart
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="userId != null">
                user_id,
            </if>
            <if test="dealId != null">
                deal_id,
            </if>
            <if test="dealSkuId != null">
                deal_sku_id,
            </if>
         
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id,jdbcType=BIGINT},
            </if>
            <if test="userId != null">
                #{userId,jdbcType=BIGINT},
            </if>
            <if test="dealId != null">
                #{dealId,jdbcType=BIGINT},
            </if>
            <if test="dealSkuId != null">
                #{dealSkuId,jdbcType=BIGINT},
            </if>
           
        </trim>
    </insert>

set

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

4、foreach

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

5、script
要在带注解的映射器接口类中应用动静 SQL，能够应用 script 元素。比方:

@Update({"<script>",
  "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}",
  "</script>"})
void updateAuthorValues(Author author);

6、bind
bind 元素容许你在 OGNL 表达式以外创立一个变量，并将其绑定到以后的上下文。比方：

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

7、多数据库反对
如果配置了 databaseIdProvider，你就能够在动静代码中应用名为“_databaseId”的变量来为不同的数据库构建特定的语句。比方上面的例子：

<insert id="insert">
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    <if test="_databaseId =='oracle'">
      select seq_users.nextval from dual
    </if>
    <if test="_databaseId =='db2'">
      select nextval for seq_users from sysibm.sysdummy1"
    </if>
  </selectKey>
  insert into users values (#{id}, #{name})
</insert>

五、SQL 语句构建器