动态SQL语句总结

主要内容

if元素
choose、when、otherwise元素
trim、where、set元素
foreach元素

if 元素

下面先看一个例子

    <select id="selectUserById" resultType="MyUser" parameterType="MyUser">
        select * from user where 1=1
        <if test ="name !=null and name !=''">
            and name like concat('%',#{name},'%')
        </if>
        <if test = "sex !=null and sex !=''">
            and sex = #{sex}
        </if>
    </select>

在上面的例子中，运用的是sql语句的拼接，在这里需要注意的是有如下几点：

    1. resultType与resultMap不能同时使用  
    2. #{xxx}与${vlaue}的使用看上一篇博客  
    3. if的判断条件的test里面的参数目前我知道有三种声明方式如下：  

    1）对象作为参数，则test里面的参数为对象的属性  
    2)@Param声明的参数  
    3)map方式键值对

choose、when 、otherwise元素

有些时候不想用到所以的条件语句，而只想从中选择一二，针对这种情况，Mybatis提供了<choose>元素，如下例子。

    <select id = "selectUserBychoose" resultType="MyUser" parameterType="MyUser">
        select * from user where 1=1
        <choose>
            <when test="name !=null and name !=''">
                and name like concat('%',#{name},'%')
            </when>
            <when test="sex != null and sex !='' "
                and sex=#{sex}
            </when>
            <otherwise>
                and id>10
            </otherwise>
        </choose>

上面sql语句的含义是当满足test里面的其中之一时，执行when子句，两者都不满足时执行otherwise子句，这样做就可以实现if元素没法实现的一些选择功能。

trim、where、set元素

trim 的主要功能是可以在自己包含的内容上加上某些前缀，也可以在后面加上某些后缀，与之对应的属性是prefix和suffix；可以把包含内容的首部某些内容覆盖，及忽略，也可以把尾部的某些内容覆盖，对应的属性是prefixOverrides和suffixOverrides。正因为<trim>元素有这样的功能，所以也可以非常简单利用<trim>来代替<where>元素功能。代码如下：

trim应用

    <select id="selectByTrim" resultType="MyUser" parameterType="MyUser">
        select * from user
        <trim prefix="where" prefixOverrides="and | or">
            <if test="name !=null and name !=''">
                and name like concat('%',#{name},'%')
            </if>
            <if test="sex !=null and sex !=''">
                and sex=#{sex}
            </if>
        </trim>
    </select>  

    <!-- sql动态中的trim标签
    prefix"在前面进行添加"
    prefixOverreds="在前面进行去掉"
    suffix="在后面进行添加"
    suffixOverreds="在后面进行去掉"
    重点：执行的重点，先去除，在添加
    使用trim 会在prefix设置的值后面添加空格  在使用trim进行模糊查询不可取
    -->

    <update id="upid" parameterType="Teacher">   
        update Teacher
        <trim prefix="set" suffixOverreds",">
            name=#{name},age=#{age}
        </trim>
        where id=#{id}
    </update>

where子句的应用

<select id="selectByWhere" resultType="MyUser" parameType="MyUser">
    select * from user
    <where>
        <if test="name !=null and name !=''">
            and name like concat('%',#{name},'%')
        </if>
        <if test="sex !=null and sex !=''">
            and sex=#{sex}
        </if>
    </where>
</select>

set元素的应用：

<select id="selectBySet" resultType="MyUser" parameType="MyUser">
    update user
    <set>
        <if test="name !=null and name !=''"
            name like concat('%',#{name},'%')
        </if>
        <if test="sex !=null and sex !=''"
            sex like concat('%',#{sex},'%')
        </if>
    </set>
    where id=#{id}
</select>

set 子句会在update的sql语句中用到，对应的值千万别忘了where限定。

foreach元素的应用:
foreach主要用在构建in条件中，它可以在SQL语句中迭代一个集合，<foreach>元素的属性主要有item、index、collection、open、separator、close。item表示集合中每个元素进行迭代时的别名，index指定一个名字，用于表示在迭代过程中每次迭代到的位置，open表示该语句以什么开始，separator表示在每次进行迭代之间以什么符号进行分隔符，close表示以什么结束。在使用<foreach>元素时最关键，最容易出错的是collection属性，该属性是必选的，但在不同情况下值不同，有以下几种情况：

    * 如果传入的是单参数且参数类型是一个List，collection属性值为list  
    * 如果传入的参数是单参数且参数类型是一个arry数组，collection值为arry
    * 如果传入的参数是多个，需要把她们封装成一个Map，当然单参数也可以封装成Map。map的key值参数名，collection属性值是传入的List或者arry对象在封装的Map中的key

具体例子如下：

<select id="selectByForeach" resultType="MyUser" parameterType="List">
    select *from user
    <foreach item="item" index='index' collection='list' open="("separator='',"close=")">
        #{item}
    </foreach>
</select> 
<!-- 
    foreach的作用：
        1.查询中的使用 多值查询 in
        2.批量添加
-->
<!-- in的使用 -->
<select id="selectByForeach" resultType="MyUser" parameterType="List">
    select * from user where age in
        <foreach collection="list" item="age" open="(" close=")" separator=",">
            #{age}
        </foreach>
</select>

<!-- 使用foreach与trim联合使用 批量添加-->
<!-- sql的声明字段 -->
<sql id="fileName">
    (id,name,age,sex)
</sql>
<insert id="insertByForeach" parameterType="list">
    insert into teacher <include refid="fileName"/>  values
        <trim suffixOverreds=","><!-- 去掉最后一个"," -->
            <foreach collection="list" item="user">
                (id,#{user.name},#{user.age},#{user.sex}),
            </foreach>
        </trim>
</insert>

bind元素

在进行模糊查询时，如果使用”${}“拼接字符串，则无法防止SQL注入问题；bind使用可以一定程度上避免这个问题。代码演示如下：

    <select id="selectByBind" resultType="MyUser" parameType="List">
        <bind name ="paran_uname" vlaue = "'%'+uname+'%' "/>
        select * from user
            where name like #{paran_uname}
    </select>

    <!-- 
        sql动态中的bind标签对变量或者查询的条件进行重新定义,模糊查询中trim的prefix和suffix使用后会在后面添加空格，显然不适合模糊查询。  
    -->
    <select id="selectBind" parameterType=""Teacher resultType="Teacher">
        <bind name="name" vlaue="'%'+name+'%'"/>
        select * from teacher where name like #{name}
    </select>