关于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 语句构建器