MyBatis动态SQL详解

动态SQL的作用

动态SQL,就是将查询条件中的判断语句，提前在代码中判断完成，然后在数据库中执行的就是简单的，可利用索引的SQL语句了。
它具有完成逻辑判断和动态拼接sql功能。

就比如这样的场景：
我们在进行查询时有一种常见的逻辑：当我们填写了这个查询条件，就查出符合该条件的数据，我们没有填写查询条件时，就查询出全部数据。
使用静态SQL可能会使数据库无法利用到索引，降低它的性能。

动态SQL都有哪些？

有九种动态SQL标签：

trim
where
set
foreach
if
choose
when
otherwise
bind

trim的使用方法：

通过trim标签，可以动态构建where、set等条件

查询操作(条件查询)：

    <select id="selectByNameOrSchool" parameterType="User" resultMap="UserResult">
        select id,name,age,email,address
        from user
        <trim prefix="where" prefixOverrides="and">
            <if test="name!=null and name!=''">and name like concat('%', #{name}, '%')</if>
            <if test="school != null  and school != ''"> and school = #{school}</if>
        </trim>
    </select>

更新操作：

    <update id="UpdateUser" parameterType="User">
        update user
        <trim prefix="SET" suffixOverrides=",">
            <if test="username != null and username != ''">username = #{username},</if>
        </trim>
        where id = #{id}
    </update>

新增操作：

  <insert id="inserUser" parameterType="User" useGeneratedKeys="true" keyProperty="id">
        insert into com_team_sh
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="username != null and username != ''">username,</if>
   			<if test="age != null">age,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="username != null and username != ''">#{username},</if>
            <if test="age != null">#{age},</if>
        </trim>
    </insert>

prefix ：前缀添加的内容
suffix ：后缀添加的内容
prefixOverrides ：前缀需要覆盖的内容，一般是第一个判断条件前面多余的结构，如，在第一个判断条件前多了‘and’
suffixOverrides ：后缀需要覆盖的内容 一般是最后一个数据的最后面的符号，如：set的时候，最后一个值的后面多一个逗号

where的使用方法

where会自动判断SQL语句，只有< where>内的条件成立时，才会拼接SQL中加入where关键字，否则不会添加，还会去除多余的“AND”或“OR”.

查询操作（条件查询）：

    <select id="selectByNameOrSchool" parameterType="User" resultMap="UserResult">
        select id,name,age,email,address
        from com_user
        <where>
            <if test="name!=null and name!=''">and name like concat('%', #{name}, '%')</if>
            <if test="school != null  and school != ''"> and school = #{school}</if>
        </where>
    </select>

set的使用方法

用于更新操作，在动态包含的SQL语句前输出一个SET关键字，并将SQL语句中最后一个逗号去除。

    <update id="UpdateUser" parameterType="User">
        update user
        <set>
            <if test="username != null and username != ''">username = #{username},</if>
        </set>
        where id = #{id}
    </update>

foreach的使用方法

批量删除：

    <delete id="deleteUserByIds" parameterType="String">
        delete from com_team where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

mapper层：

int deleteComUserByIds(Long[] ids);

批量删除传入参数有两种形式：数组或集合
集合使用：Set< Long > ids
数组使用 : Long[] ids

批量更新：

    <update id="updateComUserBatch" parameterType="List">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            update com_user
            <set>
                <if test="item.name !=null and item.name !=''"> name=#{item.name},</if>
                <if test="item.age!=null">age=#{item.age},</if>
                <if test="item.phoneNumber !=null ">phone_number=#{phoneNumber}</if>
                <if test="item.school !=null and item.school !=''">school=#{item.school},</if>
            </set>
            WHERE id=#{item.id}
        </foreach>
    </update>

mapper层：

    int updateComUserBatch( List<ComUser> comUserList);

注意：如果要使用批量更新，需要在数据库配置上加上&allowMultiQueries=true 来允许批量更新，不然会报错

数据库配置：

        druid:
            master:
                url: jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true
                username: root
                password: 123456

批量插入：

<insert id="insertBatchMhPage" parameterType="mhPage" useGeneratedKeys="true" keyProperty="id">
    insert into mh_page (name_id,chapter)
    VALUES
    <foreach collection="list" item="item" open="" separator="," close="">
        (
    <trim suffix="" suffixOverrides=",">
        <if test="item.nameId != null">#{item.nameId},</if>
        <if test="item.chapter != null">#{item.chapter},</if>        
    </trim>
        )
    </foreach>
</insert>

if的使用方法

作为条件判断，如果不符合if中的条件，则不会执行if标签中的语句

<if test="name != null and name !=''">name=#{name},</if>

choose、when、otherwise的使用方法

类似于java中的switch语句。
例如下面例子：
如果传入了name就按name进行模糊查询，
没有传入name就按school进行查询，
如果name和school都没有传入，
就按照otherwise中的条件进行查询（查询所有role为队长的）

    <select id="selectByNameOrSchool" parameterType="User" resultMap="UserResult">
        select id,name,age,email,address
        from com_user
        WHERE is_deleted=0
        <choose>
            <when test="name !=null and name !=''">and name like concat('%', #{name}, '%')</when>
            <when test="school !=null and school !=''">and school=#{school}</when>
            <otherwise>and role='队长'</otherwise>
        </choose>
    </select>

bind的使用方法

从OGNL表达式中创建一个变量，并将其绑定到上下文，常用于模糊查询的SQL中

例如：
根据name进行模糊查询

    <select id="selectByName" parameterType="ComUser" resultMap="ComUserResult">
        <bind name="namePattern" value="'%'+_parameter.getName()+'%'"/>
        select id,name,age,email,address
        from com_user
        WHERE name LIKE #{namePattern}
    </select>

如果不传name的值，会报空指针异常。
所以上面这种写法一定要传一个name值

动态SQL的执行原理

使用OGNL从SQL参数对象中计算表达式的值，根据表达式的值动态拼接SQL，以此来完成动态SQL的功能。

OGNL表达式是Object-Graph Navigation Language的缩写，是一种功能强大的表达式语言，通过简单一致的表达式语法，可以存取对象的任意属性，调用对象的方法，遍历整个对象的结构图，实现字段类型转换.