六、Mybatis-动态SQL

动态SQl

1.if 标签

  <select id="xx" resultType="xxx">
        select * from user where 1=1
        <if test="name != null and name !=''">
            and name = #{name}
        </if>
        <if test="age != null and age != ''">
            and age = #{age}
        </if>


    </select>

2. where标签

 <select id="xxx">
        select * from user
    <where>
        <if test="name != null and name !=''">
            and name = #{name}
        </if>
        <if test="age != null and age != ''">
            and age = #{age}
        </if>
    </where>
    </select>

where会把 多余的 and，or，自动去掉。当where标签内没有内容时，就不会显示where

3.trim标签

• prefix/suffix 将trim标签内的内容，前面/后面添加指定内容

• prefixOverrides/suffixOverrides 将trim标签中内容前面或后面 去掉指定内容

• 若标签中没有内容时，trim标签也没有任何效果

 <select id="xxxx">

    <!---
      prefix/suffix 将trim标签内的内容，前面/后面添加指定内容
      prefixOverrides/suffixOverrides 将trim标签中内容前面或后面 去掉指定内容
      若标签中没有内容时，trim标签也没有任何效果
    -->
        <trim prefix="where"   suffixOverrides="and | or">
            <if test="name != null and name !=''">
                and name = #{name}
            </if>
            <if test="age != null and age != ''">
                and age = #{age}
            </if>

        </trim>
    </select>

4.choose when otherwise 标签

choose when otherwise 相当于 if…else…if…else

所以 when 和 otherwise 只有一个条件执行

    <select id="xxxx">
        select * from user 
        <where>
            <choose>
                <when test="name != null and name != ''">
                    name = #{name}
                </when>
                
                <when test="age != null and age != null">
                    age = #{age}
                </when>

                <otherwise>
                    eid =1
                </otherwise>
            </choose>
        </where>
    </select>

5.foreach 标签

批量删除

  <delete id="xxxx">
        delete from user where id in(
            <foreach collection="ids" item="id" separator=",">
                #{id}
            </foreach>
            )
    </delete>

或者

 <delete id="xxxx">
        delete from user where id in
            <foreach collection="ids" item="id" separator="," open="(" close=")">
                #{id}
            </foreach>
            
    </delete>

或者

 <delete id="xxxx">
        delete from user where 
            <foreach collection="ids" item="id" separator="or" >
               id= #{id}
            </foreach>

</delete>

批量添加

<insert id="">
        insert into user values
        <foreach collection="emps" item="emp" separator=",">
            (null,#{emp.name},#{emp.sex},#{emp.age})
        </foreach>
    </insert>