Mybatis动态SQL

Mybatis提供的拼接Sql语句的机制。

1.if

<select id="listSearchStudents" resultType="entity.Student">
    select *
    from stu
    where sname = #{sname}
    <if test="phone != null and phone !='' ">
        and phone = #{phone}
    </if>
</select>

2.choose when otherwise

<select id="listSearchStudents1" resultType="entity.Student">
    select *
    from stu
    where 1=1
    <choose>
        <when test="sname != null and sname != '' ">
            and sname like '%${sname}%'
        </when>
        <when test="phone != null and phone != '' ">
            and phone like '%${phone}%'
        </when>
        <!-- 以上都不满足 -->
        <otherwise>

        </otherwise>
    </choose>
</select>

3.where

子元素有返回值，在对应语句加where

<select id="listSearchStudents2" resultType="entity.Student">
    select *
    from stu
    <where>
        <if test="sname != null and sname != '' ">
            and sname like '%${sname}%'
        </if>
        <if test="phone != null and phone != '' ">
            and phone like '%${phone}%'
        </if>
    </where>
</select>

4.set

用于update

<update id="updateStudentBySid" parameterType="Integer">
    update stu
    <set>
        <if test="sname != null and sname != '' ">
            sname = #{sname},
        </if>
        <if test="phone != null and phone != '' ">
            phone = #{phone},mj
        </if>
    </set>
    where sid = #{sid}
</update>

5.trim

• prefix 指定给Sql语句增加的前缀
• prefixOverrides 指定给Sql语句要去掉的前缀
• suffix 指定给Sql语句增加的后缀
• suffixOverrides 指定给Sql语句要去掉的后缀
  代替where和set使用

<trim prefix="where" prefixOverrides="and">
  <if test="">
    and sid = #{sid}
  </if>
</trim>
<trim prefix="set" suffixOverrides=",">
  <if test="">
      sid = #{sid}，
   </if>
</trim>

6.foreach

用于遍历 支持数组 List Set 接口的集合
通常用于in关键字

<select id="listStudentsByForeach" resultType="entity.Student">
    select *
    from stu
    where sid in
    <foreach collection="list" item="sid" index="index" open="(" separator="," close=")">
        #{sid}
    </foreach>
</select>