mybatis框架复习（二）：动态SQL的使用详解，批量插入和批量删除数据

淡淡的烟草味﹌ 2023-09-29 07:26 6阅读 0赞

**目录**

1.if与where（非常常用）

2. trim元素

3.choose、when、otherwise

4.set元素

5.foreach元素（里面的注释有重点）

6.批量插入数据

7.sql片段

MyBatis提供了对SQL语句动态的组装能力，大量的判断都可以在 MyBatis的映射XML文件里面配置，以达到许多我们需要大量代码 才能实现的功能，大大减少了我们编写代码的工作量。

<table> 
 <thead> 
  <tr> 
   <th>元素</th> 
   <th>作用</th> 
   <th>备注</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>if</td> 
   <td>判断语句</td> 
   <td>单条件分支判断</td> 
  </tr> 
  <tr> 
   <td>choose、when、 otherwise</td> 
   <td>相当于Java中的 case when语句</td> 
   <td>多条件分支判断</td> 
  </tr> 
  <tr> 
   <td>trim、where、set</td> 
   <td>辅助元素</td> 
   <td>用于处理一些 SQL拼装问题</td> 
  </tr> 
  <tr> 
   <td>foreach</td> 
   <td>循环语句</td> 
   <td>在in语句等列举 条件常用</td> 
  </tr> 
 </tbody> 
</table>

#### 1.if与where（非常常用） ####

if元素相当于Java中的if语句，它常常与test属性(test就相当于一个条件的判断结果)联合使用。现在我们要根据name去查找学生，但是name是可选的，如下所示：

**注意：这个test标签对数据的判断，这个数据是从Java后端获取到的；**

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5pyq5p2l5b6I6ZW_77yM5Yir5Y-q55yL55y85YmN_size_20_color_FFFFFF_t_70_g_se_x_16][]

**这个报错说明：在if标签中的变量的判断时用的是从Java代码端拿到的数据，我们这里Java代码端的数据是dId，而数据库中存储的数据字段是 did , 值得注意的是：对数据库中的数据做修改，我们得从Java端拿到最终要修改的数据，才能把要修改的数据传给数据库端；**

下面这样写就没问题了：判断条件变成了判断Java端传过来的数据dId

发现**直接使用if标签**会导致具体的sql语句中多了一个and连接符，所以导致报错了；

<select id="selectByUser" resultMap="userMap">
            select * from user where
            <if test="id != null">
                and id = #{id}
            </if>
            
            <if test="username != null and username != '' " >
                and `user_name` = #{username}
            </if>
            <if test="password != null and password != ''">
                and `password` = #{password}
            </if>
        </select>

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5pyq5p2l5b6I6ZW_77yM5Yir5Y-q55yL55y85YmN_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

解决方法：

①在查询的where后面加一个 1=1 ，这样是有点取巧的样子；然后运行的sql结果如下：

select * from user where 1=1 and id = ? and `user_name` = ? and `password` = ?

②**使用where标签；where标签存在的一个原因就是为了解决这个多出来的and；就是我们不自己来写这个where了，直接使用where标签，然后把if的判断标签放进这个where标签；下面的sql就是以后的动态查询的标准书写，主要是根据你传入的字段来查询；下面这种查询时主流的查询；**

<resultMap id="userMap" type="com.ydlclass.entity.User">
            <id column="id" property="id" />
            <result column="user_name" property="username"/>
            <result column="password" property="password"/>
        </resultMap> 
    
     <select id="selectByUser" resultMap="userMap">
            select * from user
            <where>
                <if test="id != null">
                    and id = #{id}
                </if>
                
                <if test="username != null and username != '' " >
                    and `user_name` = #{username}
                </if>
                <if test="password != null and password != ''">
                    and `password` = #{password}
                </if>
            </where>
        </select>

-- 编译产生的sql:
    select * from user WHERE `user_name` = ? and `password` = ?

#### 2. trim元素 ####

注意：用得好有巧用，学习初期一般用得比较少；

有时候我们要去掉一些特殊的SQL语法，比如常见的and、or，此 时可以使用trim元素。trim元素意味着我们需要去掉一些特殊的字 符串，**prefix代表的是语句的前缀，而prefixOverrides代表的是你需要去掉的那种字符串，suffix表示语句的后缀，suffixOverrides代表去掉的后缀字符串。**

<select id="selectByUser" resultMap="userMap">
            select * from user
            <trim prefixOverrides="and" prefix="where">
                <if test="id != null">
                    and id = #{id}
                </if>
                
                <if test="username != null and username != '' " >
                    and `user_name` = #{username}
                </if>
                <if test="password != null and password != ''">
                    and `password` = #{password}
                </if>
            </trim>
        </select>

-- 编译之后生成的sql:
    select * from user where id = ? and `user_name` = ? and `password` = ?

#### 3.choose、when、otherwise ####

有些时候我们还需要多种条件的选择，在Java中我们可以使用 switch、case、default语句，而在映射器的动态语句中可以使用 choose、when、otherwise元素。

if else if

<select id="selectByUser" resultMap="userMap">
            select * from user
          <where>
              <choose>
                  <when test="id != null">
                       and id = #{id}
                  </when>
                  <when test="username != null and username != '' ">
                      and `user_name` = #{username}
                  </when>
                  <when test="password != null and password != ''">
                      and `password` = #{password}
                  </when>
              </choose>
          </where>
        </select>

生成的sql语句：只进入了一个when ,相当于if ... else.if

select * from user WHERE id = ?

#### 4.set元素 ####

**修改语句，动态的sql;**

在update语句中，如果我们**只想更新某几个字段的值，这个时候可以使用set元素配合if元素来完成**。注意：set元素遇到,会自动把逗号（,）去掉。

使用set关键字来更新数据表：

<update id="update">
            update `user` set
            <trim suffixOverrides="," suffix="">
                <if test="username != null and username != '' ">
                    user_name = #{username},
                </if>
                <if test="password != null and password != ''">
                    password=#{password}
                </if>
            </trim>
        <where>
          <if test="id != null">
              and id = #{id}
          </if>
        </where>
        </update>
    -- 注意sqs语句的字段拼接，前面的字段使用逗号连接起来的，最后一个字段是用and连接的，
    还有就是数据库中判断字段是否为null,是用is来判断的，不是用=号，一定要注意；

产生的sql:
    
    update `user` set user_name = ?, password=? WHERE id = ?

主要使用的是：**set标签，这样就可以避免多余的逗号产生**；下面使用set标签来完成这个update，**所有的单表都是这样写update的；**

<update id="update">
            update `user`
            <set>
                <if test="username != null and username != '' ">
                    user_name = #{username},
                </if>
                <if test="password != null and password != ''">
                    password=#{password}
                </if>
            </set>
        <where>
          <if test="id != null">
              and id = #{id}
          </if>
        </where>
        </update>

-  产生的SQL语句：
    update `user` SET user_name = ?, password=? WHERE id = ?

#### 5.foreach元素（里面的注释有重点） ####

foreach元素是一个循环语句，它的作用是遍历集合，可以支持数组、List、Set接口。 我们可以借用这个循环可以达到**批量删除或者是批量查询的业务**；

*  collection配置的是传递进来的参数名称，就是你要遍历的集合的集合名；
 *  item配置的是循环中当前的元素。
 *  index配置的是当前元素在集合的位置下标。
 *  open和 close配置的是查询的结果以什么符号将这些集合元素包装起来。
 *  separator是查询的返回结果中各个元素的间隔符。

接口：
        /**
         * 根据id批量删除用户
         * @param ids  这里的ids是一个形参！！！
         * 注意：要注意把参数绑定，否则容易报绑定异常错误
         */
        int deleteByIds(@Param("xxx") List<Integer> ids);

/**
         * 根据id批量删除用户
         */
        @Test
        public void testDeleteByIds(){
            //使用自动提交的方法来实现事务的提交
            try(SqlSession session = sqlSessionFactory.openSession(true)){
                UserMapper mapper = session.getMapper(UserMapper.class);
                 List<Integer> list = new ArrayList<>();
                 list.add(3);
                 list.add(133);
                int affectedRows = mapper.deleteByIds(list);
                log.debug("affectedRows is [{}]", affectedRows);
            }
        }

<delete id="deleteByIds">
            delete from `user`
            where id in
        <foreach collection="xxx" item="id" open="(" close=")" separator=",">
    
            id=#{id}
        </foreach>
        </delete>

运行之后的sql:
    delete from `user` where id in ( id=? , id=? )

**批量查找数据：**

<select id="selectByIds">
            select from `user`
            where id in
            -- 在前端拿数据记得绑定数据，否则容易报数据绑定异常
        <foreach collection="xxx" item="id" open="(" close=")" separator=",">
            id=#{id}
        </foreach>
        </select>

#### 6.批量插入数据 ####

**批量插入数据，批量删除数据，批量查找数据都可以结合foreach标签来使用；**

/**
         * 批量插入数据
         * @param users
         * @return
         */
        int batchInsert(@Param("users") List<User> users);

<insert id="batchInsert" parameterType="list">
            insert into `user` (user_name,password)
            VALUES
            <foreach collection="users" item="user" separator=","> 
                
           
                (#{user.username},#{user.password})
            </foreach>
        </insert>

@Test
        public void testBatchInsert(){
            //使用自动提交的方法来实现事务的提交
            try(SqlSession session = sqlSessionFactory.openSession(true)){
                UserMapper mapper = session.getMapper(UserMapper.class);
                List<User> list = new ArrayList<>();
                list.add(new User(null,"sdd","123"));
                list.add(new User(null,"aa","12345566"));
                int affectedRows = mapper.batchInsert(list);
                log.debug("affectedRows is [{}]", affectedRows);
            }
        }

编译产生的sql语句：
     insert into `user` (user_name,password) VALUES (?,?) , (?,?)

### 7.sql片段 ###

在真正的生产环境中，是不允许使用\*来查询的，一般是你需要那个字段你就查询这个字段就行了；

有时候可能某个 sql 语句我们用的特别多，**为了增加代码的重用性，简化代码**，我们需要将这些代码抽取出来，然后使用时直接调用。

创建sql片段：

<sql id="sql">
            id,user_name,password
        </sql>

-- 这个是不用sql片段写的：
    <select id="selectAll" resultMap="userMap">
       select * from user
    </select>
    
    
    -- 使用sql片段写的：  这个refid="sql"就是sql片段对应的id
    <select id="selectAll" resultMap="userMap">
       select <include refid="sql" /> from user
    </select>

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5pyq5p2l5b6I6ZW_77yM5Yir5Y-q55yL55y85YmN_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/94f97e789b0643c2b31bfaa55df8fe8a.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5pyq5p2l5b6I6ZW_77yM5Yir5Y-q55yL55y85YmN,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5pyq5p2l5b6I6ZW_77yM5Yir5Y-q55yL55y85YmN_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/244b0862998a4b9e95c43efd5d148ea5.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5pyq5p2l5b6I6ZW_77yM5Yir5Y-q55yL55y85YmN,size_20,color_FFFFFF,t_70,g_se,x_16