MyBatis 4. 动态 SQL_哪个元素可以用于引入外部sql片段-CSDN博客

本文链接：https://blog.csdn.net/qq_37138933/article/details/79496211

动态 SQL

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。

动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多的元素需要来了解。MyBatis 3 大大提升了它们,现在用不到原先一半的元素就可以了。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。

if
choose (when, otherwise)
trim (where, set)
foreach

1. if

<select id="getUsersByNameLikeAndAge" resultType="com.chen.entity.User">
    SELECT * FROM users WHERE
    <if test="name != null and name != ''">name like #{name}</if>
    <if test="age != null &amp;&amp; age != 20">and age=#{age}</if>
</select>

特殊符号需要写为转义字符，其中 && 需要被替换为 && 。

2. where

在上面的例子中，当匹配条件并不完全匹配时，即如果出现只有第二个条件匹配时，SELECT * FROM users WHERE and age=#{age} ，又或者两个都不匹配时，SELECT * FROM users WHERE ，此时查询会失败。

第一种办法是在sql 语句中的 where 后加入永真式，并修改为

<select id="getUsersByNameLikeAndAge" resultType="com.chen.entity.User">
    SELECT * FROM users WHERE 1=1 
    <if test="name != null and name != ''">and name like #{name}</if>
    <if test="age != null &amp;&amp; age != 20">and age=#{age}</if>
</select>

第二种办法可以通过 where 元素，它知道只有在一个以上的if条件有值的情况下才去插入 “WHERE” 子句。而且，若最后的内容是 “AND” 或 “OR” 开头的，where 元素也知道如何将他们去除。

<select id="getUsersByNameLikeAndAge" resultType="com.chen.entity.User">
    SELECT * FROM users
    <where>
        <if test="name != null and name != ''">name like #{name}</if>
        <if test="age != null &amp;&amp; age != 20">and age=#{age}</if>
    </where>
</select>

3. trim

可以通过自定义 trim 元素来定制我们想要的功能。

<trim prefix="" prefixOverrides="" suffix="" suffixOverrides="">
    ...
</trim>

trim 有四个属性：

prefix ：前缀，它会将拼接后的整个 sql 语句加一个前缀。
prefixOverrides ：前缀覆盖，去掉 sql 语句前面多余的字符。
suffix ：后缀，它会将拼接后的整个 sql 语句加一个后缀。
suffixOverrides ：后缀覆盖，去掉 sql 语句前面多余的字符。

<select id="getUsersByNameLikeAndAge" resultType="com.chen.entity.User">
    SELECT * FROM users
    <trim prefix="where" suffixOverrides="and">
        <if test="name != null and name != ''">name like #{name} and </if>
        <if test="age != null &amp;&amp; age != 20">age=#{age}</if>
    </trim>
</select>

和 where 元素等价的自定义 trim 元素为：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ... 
</trim>

4. set

类似的用于动态更新语句的解决方案叫做 set。set 元素可以被用于动态包含需要更新的列，而舍去其他的。

<update id="updateUserBySet">
    UPDATE users
    <set>
        <if test="name!=null">name=#{name},</if>
        <if test="age!=null">age=#{age}</if>
    </set>
    where id=#{id}
</update>

而和 set 元素等价的自定义 trim 元素为：

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

5. choose 分支选择

有些时候，我们不想用到所有的条件语句，而只想从中择其一。针对这种情况，MyBatis 提供了 choose 元素，它像是 Java 中的带 break 的 switch 语句。

<select id="getUsersByChoose" resultType="com.chen.entity.User">
    SELECT * FROM users
    <where>
        <choose>
            <when test="id!=null">id = #{id}</when>
            <when test="name!=null">name like #{name}</when>
            <when test="age!=null">age = #{age}</when>
            <otherwise>
                age = 20
            </otherwise>
        </choose>
    </where>
</select>

6. foreach

可以通过 foreach 元素来实现迭代，可以将任何可迭代对象（如列表、集合等）和任何的字典或者数组对象传递给foreach作为集合参数。

当使用可迭代对象或者数组时，index是当前迭代的次数，item的值是本次迭代获取的元素。

当使用字典（或者Map.Entry对象的集合）时，index是键，item是值。

其中 separator 是每个元素之间的分隔符，open 是所有结果的一个开始字符，close 是所有结果的一个结束字，#{变量名} 是取出当前遍历的元素。

<select id="getUsersByForeach" resultType="com.chen.entity.User">
    SELECT * FROM users WHERE id IN
    <foreach collection="list" item="id" separator="," open="(" close=")">
        #{id}
    </foreach>
</select>

批量插入数据

1.可以通过 foreach 批量插入数据

<insert id="addUsers">
    INSERT INTO users(name,age,d_id) VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.name},#{user.age},#{user.department.id})
    </foreach>
</insert>

此时 sql 语句可能会被拼接为

INSERT INTO users(name,age,d_id) VALUES (?,?,?) , (?,?,?) , (?,?,?)

2.通过 ; 来分隔多条查询

<insert id="addUsers">
    <foreach collection="list" item="user" separator=";">
        INSERT INTO users(name,age,d_id)
        VALUES (#{user.name},#{user.age},#{user.department.id})
    </foreach>
</insert>

此时 sql 语句可能会被拼接为

INSERT INTO users(name,age,d_id) VALUES (?,?,?) ; INSERT INTO users(name,age,d_id) VALUES (?,?,?) ; INSERT INTO users(name,age,d_id) VALUES (?,?,?)

但同时也会抛出 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException 异常，需要通过 mysql 中设置 allowMultiQueries （可以修改数据库配置文件url=jdbc:mysql:///mybatis?allowMultiQueries=true）来允许通过 ; 来分隔多条查询，其默认为 false 。

7. 内置参数

mybatis 默认还有两个内置参数

_parameter ：代表整个参数
- 当传入单个参数：_parameter 就是这个参数
- 当传入多个参数：参数会被封装为 map ，而 _parameter 就代表这个 map 。
_databaseId ：如果配置了 databaseIdProvider 标签，则 _databaseId 代表当前数据库的别名。

8. bind 元素

bind 元素可以将 OGNL 表达式的值绑定到一个变量中，方便后来引用这个变量的值。

<select id="getUserLike" resultType="com.chen.entity.User">
  <bind name="_name" value="'%' + name + '%'" />
  SELECT * FROM users
  WHERE name LIKE #{_name}
</select>

9. sql 元素

sql 元素可以抽取可重用的 sql 片段，方便后来引用。可以通过 include 元素来引用外部定义的 sql 。而 include 可以自定义一些 property ，sql 元素内部可以使用自定义的属性（通过 ${property}）。

<sql id="selectSql">
    select * from ${tableName}
</sql>
<select id="getUsersByForeach" resultType="com.chen.entity.User">
    <include refid="selectSql">
        <property name="tableName" value="users"/>
    </include>
    WHERE id IN
    <foreach collection="list" item="id" separator="," open="(" close=")">
        #{id}
    </foreach>
</select>