MyBatis的动态SQL---组合条件查询与传递集合和数组（超详细）

MyBatis的动态SQL—组合条件查询与传递集合和数组（超详细）

一、 定义

MyBatis 提供了强大的 动态 SQL 支持，使得开发者可以根据不同的查询条件动态生成 SQL 语句。通过动态 SQL，可以避免手写复杂的拼接逻辑 。

二、mybatis提供的几大标签

1. if 标签

<if> 标签用于根据条件动态拼接 SQL 语句。如果条件成立，则将对应的 SQL 片段加入最终的 SQL 中。

<select id="getUser" resultType="User">
    SELECT * FROM users
    <where>
        <if test="id != null">
            AND id = #{id}
        </if>
        <if test="name != null">
            AND name = #{name}
        </if>
    </where>
</select>

• 解释：<if> 标签会自动处理 SQL 中的 AND 或 OR，在没有条件时不会产生多余的关键字。
• 注意：test 表达式可以判断参数是否为空、大小比较等。

2. choose、when和otherwise标签

这组标签类似于 Java 中的 switch 语句，用于从多个条件中选择一个执行。

<select id="getUserByCondition" resultType="User">
    SELECT * FROM users
    <where>
        <choose>
            <when test="id != null">
                AND id = #{id}
            </when>
            <when test="name != null">
                AND name = #{name}
            </when>
            <otherwise>
                AND status = 'active'
            </otherwise>
        </choose>
    </where>
</select>

解释：如果 id 存在，则优先匹配第一个 <when>，否则匹配 name，最后如果都不满足，则使用 <otherwise> 的默认条件。

3. trim标签

<trim> 标签用于自定义 SQL 片段的修饰符，比如去除多余的逗号或 AND。它可以在生成的 SQL 语句前后添加或删除内容。

<update id="updateUser">
    UPDATE users
    <set>
        <trim suffixOverrides=",">
            <if test="name != null">
                name = #{name},
            </if>
            <if test="email != null">
                email = #{email},
            </if>
        </trim>
    </set>
    WHERE id = #{id}
</update>

解释：<trim> 标签的 suffixOverrides="," 表示去除生成的 SQL 语句末尾的逗号。

4. foreach标签

<foreach> 标签用于遍历集合（如 List、Set、Array 等）并动态生成 SQL 语句，通常用于批量操作或 IN 查询。

<select id="getUsersByIds" resultType="User">
    SELECT * FROM users WHERE id IN
    <foreach item="id" index="index" collection="idList" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

解释：collection 指的是传递的集合，item 是集合中的每个元素，open 和 close 分别表示 SQL 语句的开头和结尾，separator 是元素之间的分隔符 。

5. set标签

<set> 标签常用于 UPDATE 语句，自动处理多个 SET 子句，并去掉末尾多余的逗号。

<update id="updateUser">
    UPDATE users
    <set>
        <if test="name != null">
            name = #{name},
        </if>
        <if test="email != null">
            email = #{email},
        </if>
    </set>
    WHERE id = #{id}
</update>

解释：<set> 标签自动去掉最后的逗号，生成合法的 SQL 语句。

6.where标签

<where> 标签用于处理 WHERE 条件，它可以智能地添加或删除多余的 AND 或 OR，确保生成的 SQL 语句格式正确。

<select id="getUserByDynamicConditions" resultType="User">
    SELECT * FROM users
    <where>
        <if test="id != null">
            id = #{id}
        </if>
        <if test="name != null">
            AND name = #{name}
        </if>
    </where>
</select>

解释：如果 id 或 name 为空，<where> 标签会自动去除多余的 AND，保持 SQL 语句的正确性。

7.bind标签

<bind> 标签用于创建临时变量，方便在复杂的动态 SQL 中使用。通常用于处理需要预处理的表达式或格式化参数。

<select id="getUserByNamePattern" resultType="User">
    <bind name="pattern" value="'%' + name + '%'"/>
    SELECT * FROM users WHERE name LIKE #{pattern}
</select>

解释：<bind> 标签可以将变量 name 的值进行处理，生成一个包含通配符 % 的字符串，然后在 LIKE 查询中使用。

三、组合条件查询问题

组合条件查询的问题：
比如：我可以通过id查询 也可以通过 用户名 还可以通过密码查询 也可以随机组合查询 如果都不传递参数那么就查询所有数据

1、编写SQL描述

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.qfedu.edu.dynamicsql.UserMapper">

    <!--    这里是研究组合条件查询的问题-->
    <select id="findUserByCondition" parameterType="user" resultType="user">
        select * from t_user where 1=1
       <include refid="condition"></include>
    </select>


    <!--    组合条件查询的第二种方式
            where标签会把第一个满足条件的的and给去掉
    -->
    <select id="findUserByCondition2" parameterType="user" resultType="user">
        select * from t_user
        <where>
           <include refid="condition"></include>
        </where>
    </select>


<!--    抽取这个SQL片段-->
    <sql id="condition">
        <if test="userId!=null">
            and userId=#{userId}
        </if>
        <if test="username!=null and username!=''">
            and username=#{username}
        </if>
        <if test="password!=null and password!=''">
            and password=#{password}
        </if>
    </sql>



    <!--    组合条件查询用户数据
             prefixOverrides：覆盖第一个and 简单的说满足条件的第一个and去掉
             suffixOverrides=""：覆盖最后一个啥
             prefix=""：添加一个前缀
             suffix=""：添加一个什么后缀
     -->
    <select id="findUserByCondition3" parameterType="com.qfedu.edu.vo.UserQueryVo" resultType="user">
      select * from t_user
      <if test="user!=null">
          where
          <trim prefixOverrides="and" >
              <if test="user.userId!=null">
                  and userId=#{user.userId}
              </if>
              <if test="user.username!=null and user.username!=''">
                  and username=#{user.username}
              </if>
              <if test="user.password!=null and user.password!=''">
                  and password=#{user.password}
              </if>
          </trim>
      </if>
    </select>
</mapper>

2、测试

package com.qfedu.edu.dynamicsql;

import com.qfedu.edu.pojo.User;
import com.qfedu.edu.vo.UserQueryVo;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.io.IOException;
import java.util.List;

import static com.qfedu.edu.utils.MyBatisUtilsMax.close;
import static com.qfedu.edu.utils.MyBatisUtilsMax.getSqlSession;

/**
 * @author xiaobobo
 * @title: Test001
 * @projectName cd-java-fy-2401-framwork-demo
 * @description: TODO
 * @date 2024/9/2  15:24
 */
public class Test001 {


    @Test
    public void testFindUserByCondition() throws IOException {
        SqlSession sqlSession = getSqlSession();
        //获取接口对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);


//        UserQueryVo userQueryVo = new UserQueryVo();

        User user = new User();
        user.setUserId(123);
        user.setUsername("小波波");

//        userQueryVo.setUser(null);
        //再调用这个接口中的方法
        List<User> userList = userMapper.findUserByCondition(user);
        System.out.println("返回来的这个用户对象是：" + userList);
        close();
    }
}

3、UserQueryVo的编写

@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserQueryVo {
    //这里维护这个User对象
    private User user;
}

四、传递集合和数组的问题

传递集合和数组的问题
传递集合的时候 这个类型就是list
这个集合的名字 也是 list
item：这个是每一次遍历出来的数据
open：SQL语句以什么开始
close：SQL语句以什么结束
separator：遍历出来的值与之之间使用,分割

1、编写SQL描述

<!--    下面研究传递集合和数组的问题
         传递集合的时候 这个类型就是list
         这个集合的名字 也是 list
         item：这个是每一次遍历出来的数据
         open：SQL语句以什么开始
         close：SQL语句以什么结束
         separator：遍历出来的值与之之间使用,分割
-->
    <select id="listByIds1" parameterType="list" resultType="user">
        select * from t_user
         <foreach collection="list" item="userId" open="where userId in(" close=")" separator=",">
             #{userId}
         </foreach>
    </select>

<!--    这个研究的是传递数组类型的参数
        传递数组类型参数的时候 那么这个集合的名字叫做 array
 -->
    <select id="listByIds2" parameterType="integer[]" resultType="user">
        select * from t_user
         <foreach collection="array" item="userId" open="where userId in(" close=")" separator=",">
             #{userId}
         </foreach>
    </select>

2、编写测试

@Test
    public void testListByIds1() throws IOException {
        SqlSession sqlSession = getSqlSession();
        //获取接口对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        List<Integer> ids=new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        //再调用这个接口中的方法
        List<User> userList = userMapper.listByIds1(ids);
        System.out.println("返回来的这个用户对象是：" + userList);
        close();
    }
    @Test
    public void testListByIds2() throws IOException {
        SqlSession sqlSession = getSqlSession();
        //获取接口对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        Integer[] ids={1,2,3,4};
        //再调用这个接口中的方法
        List<User> userList = userMapper.listByIds2(ids);
        System.out.println("返回来的这个用户对象是：" + userList);
        close();
    }