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>