MyBatis_05 动态SQL

动态SQL

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。

MyBatis的动态SQL是基于OGNL表达式的，它可以帮助我们方便的在SQL语句中实现某些逻辑。
MyBatis中用于实现动态SQL的元素主要有：

• if
• choose（when，otherwise）
• trim（where，set）
• foreach

1. if元素

if可以在sql语句中，做非空判断 例如：例如我们在查询学生的时候，如果条件中的字段没有值，则不加入到sql语句中。

<select id="findStuLike" resultType="Student" parameterType="Student">
    select *
    from student
    where 
    <if test="id!=null">
        id=#{id}
    </if>
    <if test="name!=null and name!=''">
        and name like #{name}
    </if>
    limit 1
</select>

2. where

• <where>标签会知道如果它包含的标签中有返回值的话，它就插入一个<where>。
• 如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉。

在上述if的SQL中有一个问题，如果某个条件不满足，拼接的SQL中，会在<where>的后面有一 个and，这样拼接的SQL语句就有问题。这时，就可以使用<where>元素，它的作用就是把拼接后的sql语句中，带and前缀的去掉。
例如：

<select id="findStuLike" resultType="Student" parameterType="Student">
    select *
    from student
    <where>
        <if test="id!=null">
            id=#{id}
        </if>
        <if test="name!=null and name!=''">
            and name like #{name}
        </if>
    </where>
    limit 1
</select>

3. set

在写SQL语句中，<set>后面如果跟了动态SQL，同样会多出一个逗号，我们可以使用<set>元素 把拼接后的sql语句中，带","后缀的去掉。 例如：

<update id="updateStu">
    update student
    <set>
        <if test="name!=null and name!=''">
            name=#{name}
        </if>
        <if test="age!=null">
            age=#{age}
        </if>
        <if test="email!=null and email!=''">
            email=#{email}
        </if>
    </set>
    where id=#{id}
</update>

4、trim

trim标记是一个格式化的标记，可以完成set或者是where标记的功能。
在上述元素where和set的使用底层其实就是用trim实现的，我们先来看下trim元素中的四个属性：

• prefix：前缀，给整个字符串拼接后的结果加一个指定前缀。
• prefixOverrides：前缀覆盖，去掉整个字符串前面指定的字符。
• suffix：后缀，给整个字符串拼接后的结果加一个指定的后缀。
• suffixOverrides：后缀覆盖，去掉整个字符串后面指定的字符。

1、我们用trim来模拟where元素

<select id="findStuLike2" resultType="Student" parameterType="Student">
    select *
    from student
    <trim prefix="where" prefixOverrides="and">
        <if test="id != null">
            id = #{id}
        </if>
        <if test="name != null and name!=''">
            and name like #{name}
        </if>
    </trim>
</select>

2、我们用trim元素模拟set元素

<update id="updateStu2">
    update student
    <trim prefix="set" suffixOverrides=",">
        <if test="name != null and name!=''">
            p_name = #{name},
        </if>
        <if test="age != null">
            age = #{age},
        </if>
        <if test="email != null and email!=''">
            email = #{email}
        </if>
    </trim>
    where id = #{id}
</update>

5、choose(when,otherwise)

有时候，我们不想用到所有的查询条件，只想选择其中的一个，查询条件有一个满足即可,使用 <choose> 标签可以解决此类问题，类似于 Java 的 switch 语句。
例如：只要有一个条件满足，就执行查询，其它条件不管有没有值，都不会执行。

<select id="findStuLike3" resultType="Student" parameterType="Student">
    select *
    from student
    <where>
        <choose>
            <when test="id != null">
                id = #{id}
            </when>
            <when test="name != null and name!=''">
                and name like #{name}
            </when>
            <otherwise>
                and 1=1
            </otherwise>
        </choose>
    </where>
</select>

6、foreach

foreach主要用来做循环处理，里面包括6个属性：

• collection=“ids” --集合
• item=“id” --从集合中取一个元素
• index="" --下标
• open="" --拼接前缀
• close="" --拼接后缀
• separator="" --设置再循环中，用什么分割元素

我们通过案例来学习：

1、查询员工id为1,2,7,8,11,15

这个SQL我们可以写成：

select *
from student
where id in(1,2,3,4);

那么在动态SQL中可以这样写：

<select id="findStuByIds" resultType="student">
    select *
    from student
    where id in
    <foreach collection="ids" item="id" separator="," open="(" close=")">
        #{id}
    </foreach>
</select>

2、批量添加员工的第一种写法：

这个SQL我们可以写成：

INSERT INTO student VALUES(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?);

那么在动态SQL中可以这样写：

<insert id="addStudentBath" parameterType="Student">
    insert into student values
    <foreach collection="stus" item="stu" separator=",">
        (null,#{stu.name},#{stu.age},#{stu.email},#{stu.teacher.id})
    </foreach>
</insert>

3、批量添加员工的第二种写法：

这个SQL我们可以写成：

INSERT INTO student VALUES(?,?,?,?,?);
INSERT INTO student VALUES(?,?,?,?,?);
INSERT INTO student VALUES(?,?,?,?,?);

那么在动态SQL中可以这样写：

<insert id="addStudentBath2" parameterType="Student">
    <foreach collection="stus" item="stu" separator=";">
        insert into student values(null,#{stu.name},#{stu.age},#{stu.email},#{stu.teacher.id})
    </foreach>
</insert>

注意：如果使用mysql数据库。这种写法要设置mysql连接器属性：allowMultiQueries=true

url=jdbc:mysql://localhost:3306/test?allowMultiQueries=true

上一篇：MyBatis_04 一对多、级联查询、缓存