Mybatis（三）动态sql语句

动态sql语句操作

　　　1、MyBatis中#{ }和${ }的区别

　　　在 mapper 中定义的参数传到 xml 中之后，在查询之前 mybatis 会对其进行动态解析。mybatis 为我们提供了两种支持动态 sql 的语法：#{} 以及 ${}，他们都可以用来动态传递参数，补全SQL语句。

　　　#{"参数名"}在SQL中相当于一个参数占位符“?”，用来补全预编译语句。它补全预编译语句时，可以理解为在此参数值两端加了单引号。举例如下，当需要动态的按id查询用户信息时。

　　select * from my_user where id = #{id};
　如果我们为id赋值为1，这条SQL执行效果相当于下面的SQL。

　　select * from my_user where id = '1';
　由于预编译SQL使用PreparedStatement对象抽象预编译语句，之后使用也无需再次编译，而且能够防止注入式攻击。所以，虽然这时候两种方式得到相同的结果，但是只要能够使用#{ }解决，我们都应该使用#{ }。

　　${"参数名"}就是单纯的字符串拼接，拼接完成后才会对SQL进行编译、执行，所以性能较低，也无法复用。但是在有些#{ }无法胜任的地方，还是会需要${ }来完成。比如当SQL中数据库表名为参数时，如果使用#{ }，如下所示。

　　select * from #{tableName};
　当我们为tableName赋值为"my_user"时，相当于执行以下SQL。

　　select * from 'my_user';
　由于表名不能加单引号，所以语法错误。这时候就需要使用${ }来进行字符串拼接。

　　select * from ${tableName};

　为tableName赋值为"my_user"后，相当于执行以下SQL。

　　select * from my_user;

　　2、动态sql语句书写

　　（1）、使用 if 标签

　　　例：根据性别和名字查询用户

<!--如果不用if标签，做条件判断，如果查询条件为null或"",就会报 异常-->
<select id="queryUserByWhere" parameterType="user" resultType="user">
    SELECT id, username, birthday, sex, address FROM `user`
    WHERE 1=1
    <if test="sex != null and sex != ''">
        AND sex = #{sex}
    </if>
    <if test="username != null and username != ''">
        AND username LIKE
        '%${username}%'
    </if>
</select>

　　（2）、使用Where标签

　　为什么要使用Where标签？

　　想想下面的例子，如果我要根据两个字段性别和用户名进行查询，但是放在前面的查询条件没有给值，这时候我们就必须在where 后面写1=1，很麻烦，可以使用where标签进行改造。

　　例：根据性别和名字查询用户

<!-- 根据条件查询用户 -->
<select id="queryUserByWhere" parameterType="user" resultType="user">
    SELECT id, username, birthday, sex, address FROM `user`
<!-- where标签可以自动添加where，同时处理sql语句中第一个and关键字 -->
    <where>
        <if test="sex != null">
            AND sex = #{sex}
        </if>
        <if test="username != null and username != ''">
            AND username LIKE
            '%${username}%'
        </if>
    </where>
</select>

　　（3）、使用sql片段

　　Sql中可将重复的sql提取出来，使用时用include引用即可，最终达到sql重用的目的。

<!-- 声明sql片段 -->
<sql id="userFields">
    id, username, birthday, sex, address
</sql>

<!-- 根据条件查询用户 -->
<select id="queryUserByWhere" parameterType="user" resultType="user">
    <!-- SELECT id, username, birthday, sex, address FROM `user` -->
    <!-- 使用include标签加载sql片段；refid是sql片段id -->
    SELECT <include refid="userFields" /> FROM `user`
    <!-- where标签可以自动添加where关键字，同时处理sql语句中第一个and关键字 -->
    <where>
        <if test="sex != null">
            AND sex = #{sex}
        </if>
        <if test="username != null and username != ''">
            AND username LIKE
            '%${username}%'
        </if>
    </where>
</select>

　（4）、使用foreach标签

　　想一想：如果我们要查询的是一个List，那么我们就要传不是一个id（或者username……）了，或者一次要穿n个id（其他字段同id），这样就要用到foreach标签。

　　foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及在迭代结果之间放置分隔符。这个元素是很智能的，因此它不会偶然地附加多余的分隔符。

　　注意：你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象传递给 foreach 作为集合参数。当使用可迭代对象或者数组时，index 是当前迭代的次数，item 的值是本次迭代获取的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

　　向sql传递数组或List，mybatis使用foreach解析

<!-- 根据ids查询用户 -->
<select id="queryUserByIds" parameterType="queryVo" resultType="user">
    SELECT * FROM `user`
    <where>
        <!-- foreach标签，进行遍历 -->
        <!-- collection：遍历的集合，这里是QueryVo的ids属性 -->
        <!-- item：遍历的项目，可以随便写，，但是和后面的#{}里面要一致 -->
        <!-- open：在前面添加的sql片段 -->
        <!-- close：在结尾处添加的sql片段 -->
        <!-- separator：指定遍历的元素之间使用的分隔符 -->
        <foreach collection="ids" item="item" open="id IN (" close=")"
            separator=",">
            #{item}
        </foreach>
    </where>
</select>