使用动态sql

动态SQL的元素种类

1.if
2.where
3.choose (when, otherwise)
4.trim
5.set
6.foreach
7.bind

if标签的使用

判断查询条件是否存在,决定是否拼接在where条件后.

    <!--1.if标签-->
    <select id="selectByItem" parameterType="User" resultType="User">
        select * from user
        where
        1=1
        <if test="id !=null">
        and id = #{id}
        </if>
        <if test="username !=null and username !=''">
            and username = #{username}
        </if>
        <if test="password !=null and password !=''">
            and password = #{password}
        </if>
        <if test="sex !=null and sex !=''">
            and sex = #{sex}
        </if>
        <if test="age !=null">
            and age = #{age}
        </if>

    </select>

where标签的使用

生成where关键字,并可以去除紧跟where后的sql的关键字.

<select id="selectByItem2" parameterType="User" resultType="User">
        select * from user
       <where>
            <if test="id !=null">
                and id = #{id}
            </if>
            <if test="username !=null and username !=''">
                and username = #{username}
            </if>
            <if test="password !=null and password !=''">
                and password = #{password}
            </if>
            <if test="sex !=null and sex !=''">
                and sex = #{sex}
            </if>
            <if test="age !=null">
                and age = #{age}
            </if>
       </where>

    </select>

choose (when, otherwise) 标签的用法

他的作用相当于java中Switch语句,只能拼接满足的一个条件.

    <select id="selectByItem3" parameterType="User" resultType="User">
        select * from user
        <where>
            <choose>
                <when test="id !=null">
                    and id = #{id}
                </when>
                <when test="username !=null and username !=''">
                    and username = #{username}
                </when>
                <when test="password !=null and password !=''">
                    and password = #{password}
                </when>
                <when test="sex !=null and sex !=''">
                    and sex = #{sex}
                </when>
                <when test="age !=null">
                    and age = #{age}
                </when>
                <otherwise>
                    and 1=1
                </otherwise>
            </choose>
        </where>
    </select>

trim标签的使用

用来去除指这的sql字符串.
常用属性:
1.prefix: 生成sql条件前拼接前缀
2.prefixOverrides: 去除前缀后面的紧跟前缀的指定的字符串
3.suffix:生成sql字符串后拼接后缀
4.suffixOverrides:去除后缀前面的紧跟后缀的指定的字符串

<select id="selectByItem4" parameterType="User" resultType="User">
        select * from user
        <trim prefix="where" prefixOverrides="and | or">
            <if test="id !=null">
                and id = #{id}
            </if>
            <if test="username !=null and username !=''">
                and username = #{username}
            </if>
            <if test="password !=null and password !=''">
                and password = #{password}
            </if>
            <if test="sex !=null and sex !=''">
                and sex = #{sex}
            </if>
            <if test="age !=null">
                and age = #{age}
            </if>
        </trim>
    </select>

set标签的使用

和where标签类似.在生成的Sql串中生成set关键字

<update id="update2" parameterType="User" >
        update user
        <set>
            <if test="username!=null and username!=''">
                username=#{username},
            </if>
            <if test="password !=null and password !=''">
                password = #{password},
            </if>
            <if test="sex !=null and sex !=''">
                sex = #{sex},
            </if>
            <if test="age !=null">
                age = #{age},  <!--会去除生成sql串最后的关键字 ,-->
            </if>
        </set>
        where id = #{id}
    </update>

foreach标签的使用

和java中for作用一样,用于迭代集合或数组数据.

	<!--6.foreach标签,
        collection:如果是数组使用array,如果是集合list,set
        item:每次迭代的数据存放的变量
        open:是以指定字符串开始
        close:以指定字符串结束
        separator:指定分隔符
    -->
    <select id="selectByItem5"  resultType="User">
        SELECT * FROM USER
             <foreach collection="array" item="id" open=" WHERE  id IN(" separator="," close=")">
                #{id}
            </foreach>
    </select>

    <!--如果数组或集合数据封装到POJO中,则传入参数为POJO对像,collection中该POJO的相应数组或集合属性-->
    <select id="selectByItem6"  resultType="User" parameterType="User">
        SELECT * FROM USER
        <foreach collection="ids" item="id" open=" WHERE  id IN(" separator="," close=")">
            #{id}
        </foreach>
    </select>