学学Mybatis（四）

动态SQL

动态sql好强大啊！

if

<select id="findActiveBlogWithTitleLike"     resultType="Blog">  SELECT * FROM BLOG   WHERE state = ‘ACTIVE’   <if test="title != null">    AND title like #{title}  </if></select>

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>

where，set

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

<pre class="html" name="code"><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><span style="font-family:Courier New;background-color: rgb(240, 240, 240);"></span>

妈妈再也不用担心拼接sql语句时候多了一个where 多了一个逗号啦！

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>

muti-db vendor support

一个配置了"_databaseId"变量的databaseIdProvider对于动态代码来说是可用的，这样就可以根据不同的数据库厂商构建特定的语句。

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