MyBatis动态SQL

<![CDATA[ ]]> 的作用

 <![CDATA[  ]]>是xml语法，在<![CDATA[ ... ]]>的所有内容都会被解析器忽略（特殊字符不转译）

<if test="">   <where>  <choose> <trim>  这些标签都不会被解析，所有要尽量缩小<![CDATA[ ]]>的使用范围

• if

    <select id="dynamicFindBook1" resultMap="BookResult" parameterType="Map">
        select * from book where 1=1 
        <if test="bookName!=null">
        <![CDATA[and bname like #{bookName}]]>
        </if>
        <if test="maxPrice!=null">
        <![CDATA[ and price <= #{maxPrice} ]]>
        </if>
        <if test="studentId!=null">
        and student_id =#{studentId}
        </if>
    </select>

• choose/when/otherwise

    <select id="dynamicFindBook2" resultMap="BookResult" parameterType="Map">
      select * from book
      <choose>
                 <!--  注意要加上 ' ' 单引号-->
      <when test="searchBy=='bookName'">
      where bname like#{bookName}
      </when>
      <when test="searchBy=='studentId'">
      where student_id=#{studentId} 
      </when>
      <otherwise>
      <![CDATA[where price<=#{price} ]]>
      </otherwise>
      </choose>
    </select>

• where

  如果所有if不成立，则不会加where，如果第一条if不成立，那么后面成立的第一个if会剔除and

    <select id="dynamicFindBook3" resultMap="BookResult" parameterType="Map">
      select * from book
      <where>
      <if test="bookName!=null">
        bname like #{bookName}
        </if>
        <if test="maxPrice!=null">
        <![CDATA[ and price <= #{maxPrice} ]]>
        </if>
        <if test="studentId!=null">
        and student_id =#{studentId}
        </if>
      </where>
    </select>

• trim

  trim和where类似，更灵活，当基句中已存在where时，可以将改为 prefix=”and”

    <select id="dynamicFindBook4" resultMap="BookResult" parameterType="Map">
      select * from book 
      <trim prefix="where" prefixOverrides="and|or"> 
      <if test="bookName!=null">
        bname like #{bookName}
        </if>
        <if test="maxPrice!=null">
        <![CDATA[ and price <= #{maxPrice} ]]>
        </if>
        <if test="studentId!=null">
        and student_id =#{studentId}
        </if>
      </trim>
    </select>

• foreach

    <select id="dynamicFindBook5" resultMap="BookResult" parameterType="Map">
      select * from book 
      <if test="studentIds!=null">
      <where>
      <foreach item="studentId" collection="studentIds">
      or student_id=#{studentId} 
      </foreach>
      </where>
      </if>
    </select>

  上面的例子也可使用in

  但是当集合中没有数据时，将会出错，而用or不会

    <select id="dynamicFindBook6" resultMap="BookResult" parameterType="Map">
      select * from book 
      <if test="studentIds!=null">
      <where>
      student_id in 
      <foreach item="studentId" collection="studentIds" open="(" separator="," close=")">
      #{studentId} 
      </foreach>
      </where>
      </if>
    </select>

• set 相对于update修改的

  注意在if的内容中 后面有一个 , 逗号

    <update id="dynamicFindBook7" parameterType="com.z.entity.Book">
      update book 
      <set>
      <if test="name!=null"> bname=#{name}, </if>
        <if test="price!=null"> price=#{price}, </if>
      </set>
      where bid=#{id}
    </update>