4. 高级应用篇 - 4.2 动态SQL - 《🌱架构之路》

概述
解决问题
代码示例

概述

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

解决问题

1、在映射文件中，会编写很多有重叠部分的SQL语句，比如SELECT语句和WHERE语句等这些冗余的sql语句，该如何处理
2、SQL语句中的where条件有多个，但是页面只传递过来一个条件参数，此时会发生问题。

代码示例

根据用户名称模糊查询用户信息

if标签

综合查询的案例中，查询条件是由页面传入，页面中的查询条件可能输入用户名称，也可能不输入用户名称。
if标签包含的属性如下

test：判断条件

    <select id="findUserList" resultType="com.example.kkbstudy.pojo.User">
        select * from `user` where 1=1
        <if test="name !=name != null and name != ''">
            name = #{name}
        </if>
    </select>

注意：要做『不等于空』字符串校验。

where标签

上边的sql中的1=1，虽然可以保证sql语句的完整性：但是存在性能问题。Mybatis提供where标签解决该问题。
where标签包含的属性如下
代码修改如下：

无

    <select id="findUserList" resultType="com.example.kkbstudy.pojo.User">
        select * from `user`
        <!-- where标签会处理它后面的第一个and -->
        <where>
            <if test="name !=name != null and name != ''">
                and name = #{name}
            </if>
        </where>
    </select>

sql片段

在映射文件中可使用sql标签将重复的sql提取出来，然后使用include标签引用即可，最终达到sql重用的目的
sql标签包含的属性如下：

id: 这段sql的唯一标识
lang:
databaseId:

include标签属性如下

refid:指向引用sql的id

    <!--  将查询字段抽取出来：-->
        <sql id="selectColumn">
        id,`name`
    </sql>
        <!--  将where条件抽取出来：-->
    <sql id="queryUserWhere">
        <if test="name !=name != null and name != ''">
            and name = #{name}
        </if>
    </sql>
    <select id="findUserList" resultType="com.example.kkbstudy.pojo.User">
        select <include refid="selectColumn"/> from `user`
        <!-- where标签会处理它后面的第一个and -->
        <where>
            <!-- 使用include引用 -->
            <include refid="queryUserWhere"/>
        </where>
    </select>

注意： 1、如果引用其它mapper.xml的sql片段，则在引用时需要加上namespace，
如下：

<include refid="namespace.sql片段”/>

foreach

需求
综合查询时，传入多个id查询用户信息，用下边两个sql实现：

SELECT * FROM USER WHERE username LIKE '%老郭%' AND (id =1 OR id =10 OR id=16)
SELECT * FROM USER WHERE username LIKE '%老郭%' AND id IN (1,10,16)

当需要在xml里实现这样效果的时候就需要用到foreach标签。
foreach标签包含的属性如下：

collection:指定输入的集合参数的参数名称
item:声明集合参数中的元素变量名
index:集合遍历时的下标
open:集合遍历时，需要拼接到遍历sql语句的前面
close:集合遍历时，需要拼接到遍历sql语句的后面
separator:集合遍历时，需要拼接到遍历sql语句之间的分隔符号

用户pojo添加ids字段

private List<Integer> ids;

这样最终的mapper就变成了了这样

    <!--  将查询字段抽取出来：-->
        <sql id="selectColumn">
        id,`name`
    </sql>
        <!--  将where条件抽取出来：-->
    <sql id="queryUserWhere">
        <if test="name !=name != null and name != ''">
            and name = #{name}
        </if>
        <if test="ids != null and ids.size() > 0">
            <foreach collection="ids" item="id" open=" AND id IN ( " close=" ) " separator=",">
                #{id}
            </foreach>
        </if>
    </sql>
    <select id="findUserList" resultType="com.example.kkbstudy.pojo.User">
        select <include refid="selectColumn"/> from `user`
        <!-- where标签会处理它后面的第一个and -->
        <where>
            <!-- 使用include引用 -->
            <include refid="queryUserWhere"/>
        </where>
    </select>

注意：
如果parameterType不是POJO类型，而是List或者Array的话，那么foreach语句中，collection属性值需要固定写死为list或者array。
如下

    <select id="findUserListByIds" resultType="com.example.kkbstudy.pojo.User">
        select <include refid="selectColumn"/> from `user`
        <where>
            <if test="list != null and list.size()>0">
                <foreach collection="list" item="id" open=" AND id IN ( " close=" ) " separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

trim

mybatis的trim标签一般用于去除sql语句中多余的and关键字，逗号，或者给sql语句前拼接 “where“、“set“以及“values(“ 等前缀，或者添加“)“等后缀，可用于选择性插入、更新、删除或者条件查询等操作。
trim标签的包含的属性如下

prefix:给sql语句拼接的前缀
prefixOverrides:去除sql语句前面的关键字或者字符，该关键字或者字符由prefixOverrides属性指定，假设该属性指定为"AND"，当sql语句的开头为"AND"，trim标签将会去除该"AND"
suffix:给sql语句拼接的后缀
suffixOverrides:去除sql语句后面的关键字或者字符，该关键字或者字符由suffixOverrides属性指定

<trim prefix="where" prefixOverrides="AND |OR " suffix=";" suffixOverrides=",">
</trim>

set

在动态 update 语句中可以使用元素动态更新列。

<update id="update" parameterType="com.example.kkbstudy.pojo.User">
        update `user`
        <set>
            <if test="name !=null and name != ''">
                name = #{name}
            </if>
        </set>
        where id #{id}
    </update>

choose

有些时候不想用到所有的条件语句，而只想从中择取一二，针对这种情况，MyBatis 提供了元素，和一起使用，它有点像 Java 中的 switch 语句。**

    <!--使用choose、when、otherwise元素根据条件动态查询用户信息-->
    <select id="selectUserByChoose" resultType="com.example.kkbstudy.pojo.User" parameterType= "com.example.kkbstudy.pojo.User">
        select * from `user` where 1=1
        <choose>
            <when test="name!=null and name!=''">
                and `name` like concat('%',#{name},'%')
            </when>
            <otherwise>
                and id > 10
            </otherwise>
        </choose>
    </select>

bind

在进行模糊查询时，如果使用“${}”拼接字符串，则无法防止 SQL 注入问题。如果使用字符串拼接函数或连接符号，但不同数据库的拼接函数或连接符号不同。

例如 MySQL 的 concat 函数、Oracle 的连接符号“||”，这样 SQL 映射文件就需要根据不同的数据库提供不同的实现，显然比较麻烦，且不利于代码的移植。幸运的是，MyBatis 提供了元素来解决这一问题。

    <!--使用bind元素进行模糊查询-->
    <select id="selectUserByBind" resultType="com.example.kkbstudy.pojo.User" parameterType= "com.example.kkbstudy.pojo.User">
        <!-- bind 中的 name 是 com.example.kkbstudy.pojo.User 的属性名-->
        <bind name="paran_name" value="'%' + name + '%'"/>
        select * from `user` where `name` like #{paran_name}
    </select>