JavaEE进阶----19.＜Mybatis进阶（动态SQL）＞

详解动态SQL

<if>标签、<trim>标签、<where>标签、<set>标签、<foreach>标签、<include>标签 & <SQL>标签

MySQL（进阶）

一、动态SQL

也就是SQL语句中指定的属性，若我们不想输入进行查询，插入，修改

那么我们就忽略掉这个属性。就当做它不存在，怎么做到呢？这就是动态SQL。

（一）、XML方式

1.1<if>标签

在插入时，如果不想填写某些属性，那么我们用if标签来实现。

有一个属性test。填的是条件

假如gender为null，我们就不添加这个gender。它会自动使用默认值。

    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        insert into
        userinfo
        (username,password,age,
        <if test="gender!=null">
            gender,
        </if>
        phone)
        values
        (#{username},#{password},#{age},
        <if test="gender!=null">
            #{gender},
        </if>
         #{phone})
    </insert>

我们进行测试，当我们设置gender的值为1。

我们进行测试，当我们不设置gender的值后gender默认为0了。

1.2 <trim>标签

如果有多个 if 标签。会存在，的拼接位置不对的badSQL问题。

有四个属性，

1.prefix=""给代码块最前面加字符串，

2.suffix=""给代码块最后面加字符串。

3.prefixOverrides=""去除代码块最前面指定的字符串，

4.suffixOverrides=""去除代码块最后面的指定字符串

比如用if标签除去的属性，若除去最后面的属性

那么此时代码就会多出一个逗号，导致SQL编译失败。此时就可以使用trim标签的suffixOverrides属性来去除掉它了。

我们分别用trim标签

去掉代码块最前面的逗号，和去掉最后面的逗号，

增加代码块最前面的左括号 ( 和增加代码块最后面的右括号 )

    <insert id="insert3" useGeneratedKeys="true" keyProperty="id">
        insert into
        userinfo
         <trim prefixOverrides="," suffixOverrides="," prefix="(" suffix=")">
             <if test="username!=null">username,</if>
             <if test="password!=null">password,</if>
             <if test="age!=null">age,</if>
             <if test="gender!=null">gender,</if>
             <if test="phone!=null">phone,</if>
         </trim>
        values
        <trim prefixOverrides="," suffixOverrides="," prefix="(" suffix=")">
            <if test="username!=null">#{username},</if>
            <if test="password!=null">#{password},</if>
            <if test="age!=null">#{age},</if>
            <if test="gender!=null">#{gender},</if>
            <if test="phone!=null">#{phone},</if>
        </trim>
    </insert>

此时我们就可以任意传入参数也不会报错了。

1.3 <where>标签

我们在淘宝买东西，筛选的时候。系统会根据我们的筛选条件,动态组装where 条件

1.用于方便并列查询时，若不选其中一个属性SQL语句中的and多余问题。

<where>标签只会在子元素有内容的情况下才插入where子句，而且会自动去除子句的开头的AND或 OR

2.并且若查询条件为空，where标签自动会去掉SQL语句中的where语句。直接查询所有的信息

以上标签也可以使用 <trim prefix="where" prefixOverrides="and"> 替换,但是此种情况下,当子元素都没有内容时,where关键字也会保留。

    <select id="queryUserListByWhere" resultType="com.qiyangyang.mybatisdemo.model.UserInfo">
        select * from userinfo
        <where>
            <if test="userName != null">
                username = #{userName}
            </if>
            <if test="age!=null">
                and age = #{age}
            </if>
        </where>
    </select>

使用where标签，解决了and多余的问题。

2.若查询条件为空，where标签自动会去掉SQL语句中的where语句。直接查询所有的信息

1.4 <set>标签

用于更新操作时，解决出现多余逗号的问题。（用于update语句中）

去除整个代码块最后的逗号。

以上标签也可以使用<trim prefix="set" prefixOverrides=","> 替换。

需求:根据传入的用户对象属性来更新用户数据，可以使用标签来指定动态内容.

接口定义：根据传入的用户 id 属性，修改其他不为 null 的属性

    <update id="update2">
        update userinfo
        <set>
            <if test="username!=null">username = #{username},</if>
            <if test="password!=null">password = #{password},</if>
            <if test="age!=null">age = #{age},</if>
        </set>
        where id=#{id}
    </update>

1.5 <foreach>标签

对集合进行遍历时可以使用该标签。标签有如下属性：

• collection：绑定方法参数中的集合，如List，Set，Map或数组对象

• item：遍历时的每一个对象

• open：语句块开头的字符串

• close：语句块结束的字符串

• separator：每次遍历之间间隔的字符串

需求:根据多个userid,删除用户数据

接口方法:void deleteByIds(List ids);

是一个循环，用于批量操作，比如批量删除

SQL：

delete form userinfo where id in (14,15,16)

有如下属性

1.collection表示从哪里拿的数据，

2.separator表示用什么符号分割

3.open表示以什么符号开始，

4.close表示以什么符号结束

5.item表示每一个元素的名字

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

注：如果使用的是aliyun的依赖，那么collection="ids"会报错，需要写成collection="list"

1.6 <include>标签 & <SQL>标签

用于解决代码重复的问题，可以理解为用SQL标签，定义了一个字符串

想用的时候，用<include>标签导入进去就可以了。

SQL标签：定义可重复SQL代码

<include>标签：通过属性refid，指定包含的SQL片段

eg：

    <sql id="cols">
        username,password,age,gender,phone,
    </sql>

    <select id="queryUserListL" resultType="com.qiyangyang.mybatisdemo.model.UserInfo">
        select <include refid="cols"></include>
               delete_flag as deleteFlag,
               create_time as createTime,
               update_time as updateTime from userinfo
    </select>

（二）、注解方式（暂不推荐）

与XML一样，只是将标签换成注解。注解的缺陷在于不会有任何报错提醒。

注解括号中我们使用

"<script>"标签

来代表这是动态SQL。

可能是某个引号错了，很难发现错误点在哪里。其实是item前面多了个单引号。

由于已经使用双引号拼接了，因此foreach标签中的属性使用单引号。

    @Delete("<script>"+
            "delete from userinfo where id in "+
            "<foreach collection='ids' separator=',' open='(' close=')' item='id'>" +
            "#{id} " +
            "</foreach>"+
            "</script>")
    void batchDelete(@Param("ids") List<Integer> ids);