9. 动态SQL_ 字节宝

Mybaits 框架的动态 SQL 技术是一种根据特定条件动态拼接 SQL 语句的功能，它存在的意义是为了解决拼接 SQL 语句字符串时的痛点问题

‍

Mybatis 中动态 SQL 怎么理解？有什么作用？怎么去实现？

‍

Mybatis 的动态 SQL 是指可以根据不同的条件动态生成 SQL 语句。它的作用主要是实现动态生成不同的 SQL 语句，以达到更灵活、高效的查询、更新、删除等操作。它大大增强了 SQL 动态适配的能力。

‍

动态 SQL 的实现主要有两种方式：基于 XML 的实现和基于注解的实现。

基于 XML 的实现：在 Mybatis 的 Mapper XML 文件中使用文本节点、if 语句、where 语句、choose 语句、foreach 语句等元素和属性，可以根据不同的条件生成不同的 SQL 语句，实现灵活查询。

例如，以下是一个使用了 if 和 where 的例子：

代码语言：javascript复制

<select id="queryByStatus" resultType="User">
  SELECT * FROM user WHERE 1=1
  <if test="status != null">
    AND status = #{status}
  </if>
  <if test="username != null">
    AND username like '%'||#{username}||'%'
  </if>
</select>

基于注解的实现：通过在 DAO 接口的方法上使用@SelectProvider、@InsertProvider、@UpdateProvider、@DeleteProvider 注解，提供一个动态 SQL 生成的类的实例方法来生成 SQL。

例如，以下是一个使用@SelectProvider 注解的例子：

代码语言：javascript复制

@SelectProvider(type = SqlProvider.class, method = "queryByStatus")
List<User> queryByStatus(String status, String username);

‍

其中，SqlProvider 是一个动态 SQL 的提供类，其中 queryByStatus 方法返回一个字符串类型的 SQL 语句，该方法根据用户实际传入的参数动态生成 SQL 语句。

‍

动态 SQL 的实现可以根据实际的业务需求和场景，选择基于 XML 或注解两种方式进行编写。使用动态 SQL 可以方便地生成不同的 SQL 语句，使得查询、插入、更新等操作更加灵活、高效。同时，也需要注意 SQL 注入和动态 SQL 的维护难度问题。

1. if

‍

if 标签可通过test属性 的表达式进行判断，若表达式的结果为 true ，则标签中的内容会执行；反之标签中的内容不会执行

‍

if 中 test 属性判断的是实体类当中属性的值

‍

语法展示：

‍

代码语言：javascript复制

        <select id="getEmpByCondition" resultType="Emp">
            select * from t_emp where
            <if test="empName != null and empName != '' ">
                emp_name = #{empName}
            </if>

            <if test="age != null and age != ''">
                and age = #{age}
            </if>

            <if test="gender != null and gender != ''">
                and gender = #{gender}
            </if>
        </select>

2. where

‍

where 和 if 一般结合使用：

a>若 where 标签中的 if 条件都不满足，则 where 标签没有任何功能，即不会添加 where 关键字
b>若 where 标签中的 if 条件满足，则 where 标签会自动添加 where 关键字，并将条件最前方多余的 and 去掉

‍

注意：where 标签不能去掉条件最后多余的 and

‍

代码语言：javascript复制

<select id="getEmpByCondition" resultType="Emp">
        select * from t_emp
        <where>
        <if test="empName != null and empName != '' ">
            emp_name = #{empName}
        </if>

        <if test="age != null and age != ''">
            and age = #{age}
        </if>

        <if test="gender != null and gender != ''">
            and gender = #{gender}
        </if>
    </where>
    </select>

3. trim

‍

trim 用于去掉或添加标签中的内容

‍

常用属性：

prefix：在 trim 标签中的内容的前面添加某些内容
prefixOverrides：在 trim 标签中的内容的前面去掉某些内容
suffix：在 trim 标签中的内容的后面添加某些内容
suffixOverrides：在 trim 标签中的内容的后面去掉某些内容

‍

代码语言：javascript复制

    <select id="getEmpByCondition" resultType="Emp">
        select * from t_emp
        <trim prefix="where" suffixOverrides="and">
            <if test="empName != null and empName != '' ">
                emp_name = #{empName} and
            </if>

            <if test="age != null and age != ''">
                age = #{age} and
            </if>

            <if test="gender != null and gender != ''">
                gender = #{gender}
            </if>
       </trim>
    </select>

4. choose , when , otherwise

‍

choose、when、 otherwise 相当于 if...else if..else

‍

代码语言：javascript复制

    <select id="getEmpByChose" resultType="Emp">
        select * from t_emp
        <where>
            <choose>
                <when test="empName != null and empName != ''">
                    emp_name =  #{empName}
                </when>
                <when test="age != null and age != ''">
                    age = #{age}
                </when>
                <when test="gender != null and gender != ''">
                    gender = #{gender}
                </when>
            </choose>
        </where>
    </select>

5. foreach

‍

代码语言：javascript复制

<!--int insertMoreEmp(List<Emp> emps);-->
<insert id="insertMoreEmp">
	insert into t_emp values
	<foreach collection="emps" item="emp" separator=",">
		(null,#{emp.ename},#{emp.age},#{emp.sex},#{emp.email},null)
	</foreach>
</insert>

<!--int deleteMoreByArray(int[] eids);-->
<delete id="deleteMoreByArray">
	delete from t_emp where
	<foreach collection="eids" item="eid" separator="or">
		eid = #{eid}
	</foreach>
</delete>

<!--int deleteMoreByArray(int[] eids);-->
<delete id="deleteMoreByArray">
	delete from t_emp where eid in
	<foreach collection="eids" item="eid" separator="," open="(" close=")">
		#{eid}
	</foreach>
</delete>

‍

collection : 设置要循环的数组或集合
item : 用一个字符串表示数组或集合中的每一个数据
separator : 设置每次循环的数据之间的分隔符
open : 循环的所有内容以什么开始
close : 循环的所有内容以什么结束

‍

Mybatis 中的 foreach 标签用于循环遍历一个集合，动态生成 SQL 中的 in 语句。它的作用主要是构建动态 SQL 语句，一般用于 IN 查询、批量插入、更新和删除等操作。

foreach 标签的使用方法如下：

代码语言：javascript复制

<select id="selectByIds" resultMap="userResultMap">
  SELECT * from user WHERE id IN
  <foreach collection="ids" item="id" open="(" separator="," close=")">
    #{id}
  </foreach>
</select>

上述代码中，ids 是一个列表，在 SQL 执行时，会将集合中的元素遍历一遍，生成类似于 (1, 2, 3) 的 SQL 语句。其中，open、separator、close 等属性用于定义拼接语句的开头、元素间的分隔符和结尾。

除了对于 IN 语句拼接，foreach 标签还可以用于批量插入、更新和删除等操作，例如：

代码语言：javascript复制

<insert id="batchInsert" parameterType="java.util.List" useGeneratedKeys="true">
  INSERT INTO user (username, password) VALUES
  <foreach collection="users" item="user" separator=",">
    (#{user.username}, #{user.password})
  </foreach>
</insert>

<update id="batchUpdate" parameterType="java.util.List">
  UPDATE user SET
  <foreach collection="users" item="user" separator=",">
    password = #{user.password}
  </foreach>
  WHERE id IN
  <foreach collection="ids" item="id" separator="," open="(" close=")">
    #{id}
  </foreach>
</update>

<delete id="batchDelete" parameterType="java.util.List">
  DELETE FROM user WHERE id IN
  <foreach collection="ids" item="id" separator="," open="(" close=")">
    #{id}
  </foreach>
</delete>

这三个示例分别是批量插入、批量更新和批量删除的实现方式。注意，这里 users 和 ids 也是列表，分别代表了待插入的用户和需要操作的记录的 ID。

通过使用 foreach 标签，Mybatis 使得动态 SQL 的拼接变得灵活而高效。可以通过遍历集合，动态生成 SQL 语句，以达到动态适配的目的。

‍

6. SQL 片段

‍

sql 片段，可以记录一段公共 sql 片段，在使用的地方通过 include 标签进行引入

‍

代码语言：javascript复制

<sql id="empColumns">
	eid,ename,age,sex,did
</sql>
select <include refid="empColumns"></include> from t_emp

‍

6.1 SQL 片段的含义

‍

Mybatis 中的 SQL 片段是一段预编译的 SQL 语句，它可以被多次使用。SQL 片段的作用是将常用的 SQL 语句封装成一个可重用的组件，方便在多个地方使用。

Mybatis 中的 SQL 片段有两种类型：

动态 SQL 片段：根据不同的条件生成不同的 SQL 语句。动态 SQL 片段可以根据参数的不同生成不同的 SQL 语句，适用于需要根据不同条件进行查询的情况。
静态 SQL 片段：固定的 SQL 语句，不需要根据参数的不同而变化。静态 SQL 片段适用于一些简单的查询操作，例如查询所有数据、插入数据等。

Mybatis 中的 SQL 片段可以通过 XML 文件或注解的方式定义，其中 XML 文件是一种常见的方式。在 XML 文件中，可以使用 <select> 、<update>、<insert>等标签来定义 SQL 片段，并通过参数绑定来实现动态 SQL 的功能。

‍

6.2 SQL 片段的作用

‍

SQL 片段（SQL Fragments）是 Mybatis 中一个重要的功能，它的作用是将一段 SQL 片段用 <sql> 元素封装，并在需要的地方通过 <include> 元素引用。主要有以下几个作用：

提高 SQL 的代码复用性：将一段经常重复使用的 SQL 代码封装到 SQL 片段中，可以在多个地方引用，避免了代码的冗余和重复编写。
减小 SQL 的维护难度：将一段 SQL 代码封装成 SQL 片段，可以方便维护和修改，避免了对 SQL 语句的重复修改和维护。
提高 SQL 程序的可读性和可维护性：将一段 SQL 代码封装到 SQL 片段中，可以使代码结构更加清晰，方便阅读和理解。

在 Mybatis 的 Mapper XML 文件中，可以在 <sql> 标签中定义 SQL 片段，例如：

代码语言：javascript复制

<sql id="queryColumns">
  id, username, password
</sql>

然后在其他的 SQL 语句中，通过 <include> 元素引用该 SQL 片段：

代码语言：javascript复制

<select id="selectById" parameterType="int" resultMap="userResultMap">
  SELECT <include refid="queryColumns"/> FROM user WHERE id = #{id}
</select>

上述语句中，使用 <include> 引用了 SQL 片段 "queryColumns" 中定义的代码 id, username, password，简化了 SQL 语句的编写。

通过使用 SQL 片段，可以方便地提高代码复用和维护的效率，使 SQL 代码更加清晰和易于阅读和理解。

‍

sql 集合数据注解字符串

0 人点赞