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

目录

1.if与where（非常常用）

2. trim元素

3.choose、when、otherwise

4.set元素

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

6.批量插入数据

7.sql片段

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

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

1.if与where（非常常用）

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

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

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

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

<if test="dId != null and dId != ''">
    and `did` = #{dId}
</if>

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

 <select id="selectByUser" resultMap="userMap">
        select * from user where
        <if test="id != null">
            and id = #{id}
        </if>
        <!-- 注意：只有sql语句中的字段名要和数据库中的字段相对应，
这里的username是从Java代码段拿到的，所以要和实体类的属性相对应-->
        <if test="username != null and username != '' " >
            and `user_name` = #{username}
        </if>
        <if test="password != null and password != ''">
            and `password` = #{password}
        </if>
    </select>

解决方法：

①在查询的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>
            <!-- 注意：只有sql语句中的字段名要和数据库中的字段相对应，这里的username是从Java代码段拿到的，所以要和实体类的属性相对应-->
            <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>
            <!-- 注意：只有sql语句中的字段名要和数据库中的字段相对应，这里的username是从Java代码段拿到的，所以要和实体类的属性相对应-->
            <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=",">
<!-- 这里的collection是前台或者是Java后端传进来的集合参数，要记得把Java代码传进来的参数绑定这个collection的value值，
     item是传进来的这个集合对象的变量指代名，我们就是使用这个变量来操作传进来的集合的，所以占位符中填的变量要
     与这个item元素相同，因为这个占位符里面的变量就是Java端要向数据库端传输的数据，占位符等号前面的变量是数据库中的字段，
     这样才能一条路通畅的把数据从Java端传到数据库中；
 -->
        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=","> 
            
<!-- 注意：如果这里不加separator=","那就会导致变一变产生的sql语句有问题，然后导致编译报sql语法错误；没有那个分割符产生的sql: insert into `user` (user_name,password) VALUES (?,?) (?,?)  两(?,?)间少了一个逗号-->       
            (#{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>