六步学会mybatis---------第五章：动态sql

1.if标签

<select id="selPersons" parameterType="Map" resultMap="PersonResultMap">
        SELECT * FROM person where 1=1

            <if test="name !=null and name != ''">
                and p_name = #{name }
            </if>
            <if test="age != null">
                and p_age > #{age }
            </if>

    </select>

test：判断条件，格式：属性名 = 值1 and 参数名=值2…
如果是参数map，属性名就key
如果条件成立，会将if里的sql拼接上，如果是第一个，会自动去掉and。

测试一下生成的sql

2. where标签

写where 1=1，sql很不美观
用where标签会自动判断是否需要加where

<select id="selPersons" parameterType="Map" resultMap="PersonResultMap">
        SELECT * FROM person
        <where>
            <if test="name !=null and name != ''">
                and p_name = #{name }
            </if>
            <if test="age != null">
                and p_age > #{age }
            </if>
        </where>
    </select>

测试出来的sql，对比一下

3.choose, when, otherwise标签

<select id="getEmployees" parameterType="Employee" resultMap="baseResultMap">
        select * from employee
        <where>
            <choose>
                <when test="name!=null">
                    and e_name=#{name}
                </when>
                <when test="age!=null">
                    and e_age=#{age}
                </when>
                <otherwise>
                     1=1
                </otherwise>
            </choose>
        </where>
</select>

Ps:不穿透，when中有一个成立，otherwise不执行

4.Set标签

<update id="updatePerson" parameterType="Person">
        update person
        <set>
            <if test="name != null and name != ''">
                p_name = #{name },
            </if>
            <if test="age != null">
                p_age = #{age },
            </if>
        </set>
        <where>
            p_id = #{id}
        </where>
    </update>

自动判断是否需要拼接set条件。用于update

5.Foreach标签

用于批量操作

<insert id="addPersons" parameterType="List">
        insert into person (p_name,p_age,p_hobby) values
        <foreach collection="list" item="person" separator=",">
            (#{person.name},#{person.age},#{person.hobby})
        </foreach>
    </insert>

    <delete id="delPersons" parameterType="List">
        delete from person where p_id in
        <foreach collection="list" item="person" separator="," open="(" close=")">
            #{person.id}
        </foreach>
    </delete>

collection: 集合类型
item：当前项的名字
separator: 分割字符
open：起始字符
close：结束字符