Mybatis复杂查询及动态SQL

- 日理万妓 2024-04-26 10:56 9阅读 0赞

#### 文章目录 ####

*  一. 较复杂的查询操作
 *   *  1. 参数占位符\#\{\}和$\{\}
     *  2. SQL注入
     *  3. like查询
     *  4. resultType与resultMap
     *  5. 多表查询
     *   *  5.1. 一对一表映射
         *  5.2. 一对多表映射
 *  二. 动态SQL
 *   *  1. if标签
     *  2. trim标签
     *  3. where标签
     *  4. set标签
     *  5. foreach标签

本篇中使用的数据表即基础映射类都是基于上一篇博客的，如果有需要可以移步： [MyBatis配置及单表操作][MyBatis]

## 一. 较复杂的查询操作 ##

### 1. 参数占位符\#\{\}和$\{\} ###

`#{}`：预处理符，生成的执行 sql 中将`id=#{6}`替换为`id=?`，最后使用`6`替换了`?`作为参数。  
`${}`：替换符，生成的执行 sql 中将`id=${6}`替换为`id=6`，直接替换，属于字符串拼接。

🍂两种占位符都可以正常使用的场合：传入的参数类型是数值类型。

使用`${}`：

select * from userinfo where id=${
        id}
    select * from userinfo where id=6

使用`#{}`：

select * from userinfo where id=#{
        id}
    select * from userinfo where id=?

对于这两种参数占位符，都能使用的情况下，更建议使用`#{}`就使用`#{}`，因为`${}`存在 SQL 注入的问题，以及在特殊场景下如果传入的类型是字符串也会出问题。

🍂只能使用`#{}`而不能使用`${}`的场合：传入参数类型为 String

使用`${}`：

select * from userinfo where username=${
        username}
    //实际执行的语句 (比如前端通过 URL 传入的参数)
    Preparing: select * from userinfo where username=张三

但是在 sql 中通过字符串来查询数据，是需要加上引号的，而使用`${}`生成的sql并没有带引号，因此不适用于字符串参数的sql。

使用`#{}`：

select * from userinfo where username=#{
        username}
    //实际执行语句
    select * from userinfo where username=?

由于使用`#{}`是将目标参数替换为占位符，然后利用 JDBC 中的占位符机制实现 sql 语句的填充，最终是会带上引号的，所以使用`#{}`构造的 sql 是可以正常运行的，并且没有 SQL 注入的问题。

🎯所以，`#{}`相比于`${}`，它支持所有类型的参数，包括数值类与字符串类，而`${}`支持数值类型，不支持字符串类型的参数，但也可以在原来 sql 里面为`${}`外面加上一对引号来使用。

select * from userinfo where username='${
        username}'
    //实际执行语句
    select * from userinfo where username='张三'

当传递的参数为字符串类型的时候，虽然加上一对引号，使用`${}`也可以使用，但是`${}`存在 SQL 注入问题，所以仍然不推荐，有关 SQL 注入后面在下文有介绍。

🍂大部分场合下，使用`#{}`都可以解决，但还是存在一小部分只能是`${}`来处理的。

如当我们需要按照升序或者逆序得到数据库查询结果的时候，这种场合就只能使用`${}`，使用`#{}`会报错，我们来演示一下。

首先，我们在`Mapper`接口中声明一个方法：作用就是按照排序获取结果集

List<Userinfo> getAllByOrder(@Param("myOrder") String myOrder);

我们再去`xml`文件中去写 sql 语句：首先我们使用`$`进行演示

<select id="getAllByOrder" resultType="com.example.demo.model.UserInfo">
        select * from userinfo order by id ${
        myOrder}
    </select>

写一个单元测试，代码很简单，就是调用 sql，然后得到结果集：

@Test
    void getOrderList() {
        
        List<UserInfo> userMappers = userMapper.getOrderList("desc");
        System.out.println(userMappers);
    }

单元测试结果：  
可以正常查询，得到的结果与预期也是相同的。  
![img][]  
我们再来试一试使用`#{}`来构造 sql 语句：

<select id="getOrderList" resultType="com.example.demo.model.UserInfo">
        select * from userinfo order by createtime #{
        order};
    </select>

重新执行单元测试，看下面的结果：  
![img][img 1]  
我们发现程序报错了，这是因为使用了`desc`的字符串（带引号）替换了占位符，而我们所需要的不是一个`desc`字符串，而是直接一个`desc`的关键字（不带引号），所以 sql 也抛出了语法错误，这里最终执行的 sql 如下：

select * from userinfo order by createtime ‘desc’;

而期望执行的 sql 为：

select * from userinfo order by createtime desc;

所以在传递 sql 命令（关键字）的时候，不能使用`#{}`，只能使用`${}`。

总之，如果不得不使用`${}`，那么传递的参数一定要能被穷举，此时才能在业务代码中对传递的值进行安全校验，否则是不能使用的。

### 2. SQL注入 ###

SQL 注入就是使用`${}`实现一些特殊的语句，来达到非法获取数据的目的，如不通过正确的密码获取某账户的信息，下面我们来演示一下，就以登录的例子来演示，SQL 注入可以在不知道密码的前提下登录成功，并且获取到用户的相关信息。

首先我将数据库只保留一个用户信息，方便演示 SQL 注入问题：  
![img][img 2]

1️⃣第一步，在`Mapper`接口中定义方法`login`，返回登录成功的用户对象。

public UserInfo login(@Param("username") String username, @Param(("password")) String password);

2️⃣第二步，在`xml`文件中编写 sql 语句，我们需要演示 SQL 注入，所以我们使用`${}`来构造 sql 语句。

<select id="login" resultType="com.example.demo.model.UserInfo">
        select * from userinfo where username='${
        username}' and password='${
        password}';
    </select>

3️⃣第三步，编写单元测试，传入有注入问题的 sql 语句`' or 1='1`，此时不需要密码就能拿到相关的用户信息。

@Test
    void login() {
        
        String username = "admin";
        String password = "' or 1='1";
    
        UserInfo userInfo = userMapper.login(username, password);
        System.out.println(userInfo);
    }

结果如下：  
我们在不知道用户密码的情况下，就登录成功，并拿到了用户的信息。  
![img][img 3]  
最终执行的 sql 语句为：

select * from userinfo where username='admin' and password='' or 1='1';

相当于它在原来条件判断的语句下，后面有加上一个或的逻辑，并且或后面的表达式为`true`，这样就使得原来的 SQL 语句中的条件判断部分一定为真，所以就在没有正确密码的情况下拿到了用户的基本信息。

所以我们能不使用`${}`就不使用`${}`，它存在 SQL 注入问题，如果必须使用`${}`则需要验证一下传递的参数是否合法，比如上面定义排序的 sql，传递的参数只能是`desc`或者是`asc`，如果不是就不能执行这条 SQL，防止 SQL 注入情况的发生。

### 3. like查询 ###

在 Mybatis 中使用`like`查询比较特殊，因为直接使用`#{}`会报错，而使用`${}`，由于输入的字符串情况很多，无法做到枚举，验证比较困难，无法避免 SQL 注入问题。

首先，我们来演示使用`#{}`进行`like`查询。

1️⃣第一步，声明方法。

List<UserInfo> getLikeList(@Param("username") String username);

2️⃣第二步，xml 中实现 执行 sql。

<select id="getLikeList" resultType="com.example.demo.model.UserInfo">
        select * from userinfo where username like '%#{
        username}%'
    </select>

3️⃣第三步，单元测试。

@Test
    void getLikeList() {
        
        String username = "a";
        List<UserInfo> list = userMapper.getLikeList(username);
        System.out.println(list);
    }

结果如下：

报错了，因为`#{}`会被替换成一个字符串，而在这个`%#{username}%`语句中`#{username}`是不能带上引号的，带上就违背 SQL 语法，执行就会出错。

![img][img 4]  
使用`#{}`会多出一对引号，而使用`${}`又无法穷举出所有情况进行验证，就可能有 SQL 注入问题，所以这里是不能直接使用`#{}`的，我们需要搭配 MySQL 内置的字符串拼接语句`concat`。

我们将 sql 改为使用`concat`进行字符串拼接：

<select id="getLikeList" resultType="com.example.demo.model.UserInfo">
        select * from userinfo where username like concat('%', #{
        username}, '%')
    </select>

重新执行单元测试，此时就可以正常执行了。  
![img][img 5]

### 4. resultType与resultMap ###

`resultType`表示数据库返回的数据映射在 java 程序中所对应的类型，只要定义类中的属性与数据库中表的字段名字一致就没有任何问题，但是如果不一致，冲突的属性就无法获取到数据库查询的结果。

比如用户名属性在数据库中的名字是`username`，而在 java 程序类中的属性名为`name`，此时通过 mybatis 将数据传递到程序中的对象时，获取到的`name`属性为`null`，就不能正确地获取到对应的属性值，为了解决这个数据库字段与类中中字段不匹配的问题，我们需要使用到`resultMap`。

resultMap 的使用方式就是在`xml`文件中设置`<resultMap>`标签，至少需要设置两个属性，一个是`id`表示你这个 resultMap 标签的名字，还有一个是`type`属性，它表示映射到程序中类的类型，需包含包名。

这个标签里面需要设置至少两个子标签，一个是`id`标签，另外一个是`result`标签，前者表示主键，后者表示数据库表中普通的列，这两种标签也是至少需要设置两个属性，一个是`column`表示数据库表中的字段名，另外一个是`property`表示程序类中的对应属性名，如果只是在单表进行查询，只设置不同字段名的映射就可以了，但是如果是多表查询，必须将数据表中所有的字段与类中所有的属性生成映射关系。

就像下面这样，图中类属性与数据表字段是相同的，实际情况可以存在不同的字段名：  
![img][img 6]

所以，当类中的属性和数据库表中的字段名不一致，面对查询结果为`null`的这种情况，有三种解决方案：

1.  将类中的属性和数据库表的字段名改成一致的。
2.  使用 sql 语句中的 as 进行列名（字段名）重命名，让列名（字段名）等于属性名即可。
3.  就是上面介绍的，定义一个 resultMap，将属性名和字段名进行手动映射。

### 5. 多表查询 ###

#### 5.1. 一对一表映射 ####

一对一关系就是对于一个属性只与另外一个属性有关系的映射，这就是一对一的关系，举个例子，对于一篇博客，它只会对应到一个用户，那么博客与用户的关系就是一对一的关系，下面我们尝试在 mybatis 中实现一对一多表联查。

首先我们将数据库中的博客表与 java 程序中的博客类对应起来，就是按照数据库中的博客表建立一个类：

![img][img 7]

@Data
    public class Articleinfo {
        
        private int id;
        private String title;
        private String content;
        private LocalDateTime createtime;
        private LocalDateTime updatetime;
        private int uid;
        private int rcount;
        private int state;
        // 联表字段
        private UserInfo userInfo;
    }

目前文章表中只有一条数据，如下图：  
![img][img 8]  
1️⃣第一步，创建`Mapper`接口和对应的`xml`文件。

![img][img 9]

![img][img 10]

2️⃣第二步，在接口中声明方法和在 xml 中写 sql 标签与语句。

// 根据文章 id 获取文章对象, 显示用户信息
    public ArticleInfo getArticleById(@Param("id") Integer id);
    <select id="getArticleById" resultType="com.example.demo.model.ArticleInfo">
        select * from articleinfo where id=#{
        id};
    </select>

3️⃣第三步，编写单元测试并执行。

![img][img 11]

结果如下：

由于我们数据表的字段与类的属性名是一致的，那些普通的属性都一一对应上了，都成功被赋值了，但是由于`UserInfo`类在数据表中没有，所以并没有得到 UserInfo 对象。  
![img][img 12]

🍂如果我们想要拿到这个对象，我们可以使用`resultMap`，而且 sql 也需要修改，想要实现一对一多表查询，需要设置多表查询 sql 语句，我们使用左外连接进行多表查询：

<select id="getArticleById" resultMap="BaseMap">
        select a.*, u.* from articleinfo as a left join userinfo as u on  a.uid=u.id where a.id=#{
        id};
    </select>

此外，我们除了设置`username`与`Articleinfo`类中每个属性与数据表的映射之外，我们还要在`Articleinfo`类对应的`resultMap`中使用association标签。最少需要设置两个属性，一个是`property`表示在主表`Articleinfo`中对应副表`UserInfo`映射对象的变量名，另外一个是副表`UserInfo`对应的`resultMap`。

`Articleinfo`类对应的 resultMap：

`UserInfo`类对应的 resultMap：

注意多表查询时要将 resultMap 中的所有字段进行映射，如果`UserInfo`类的 resultMap 没有将所有的属性都与数据库的表映射，就会造成获取到的`userInfo`对象中的数据不完整，假设只设置了`id`与`name`的映射，那就只能获取到的对象`id`与`name`的值，其他字段是获取不到的。

将两张表的 resultMap 映射好后，我们运行同样的单元测试代码，结果如下：  
![img][img 13]  
🎯但此时仍然存在一个问题，我们所建的两个表存在名字相同的字段，可能会出现数据覆盖的情况，如两个表的主键都叫`id`，但是`id`在两个表的含义是不同的，在用户表它表示用户`id`，在文章表它表示文章的`id`，现在我们将获取两表的数据的`id`改为不相同，再来看一看单元测试运行的结果：  
![img][img 14]  
![img][img 15]  
从正常的逻辑上看，由于不存在`id`为`1`的用户，所以获取到`UserInfo`对象应该为`null`才对，但是运行的结果却存在`UserInfo`对象，并且与文章表的重名字段都被赋值了文章表中的数据，为了解决这个问题，我们必须在文章表（主表）的`resultMap`中设置属性`columnPrefix`，它的值随便设置，作用是识别副表字段时加上一段前缀，如我们给用户表的字段加上前缀`u_`，此时 sql 中就不能使用`*`来一次表示所有元素了，需要一个一个单独设置，并将字段全部重命名，带上`u_`前缀 。

`association`字段设置：

sql 语句需要将用户表的字段全部重命名：

<select id="getArticleById" resultMap="BaseMap">
        select a.*, u.id as u_id,
               u.username as u_username,
               u.password as u_password,
               u.photo as u_photo,
               u.createtime as u_createtime,
               u.updatetime as u_updatetime,
               u.state as u_state
        from articleinfo as a left join userinfo as u on  a.uid=u.id where a.id=#{
        id};
    </select>

![img][img 16]

我们将`userInfo`对应的用户表的`id`再改回为`1`。  
![img][img 17]  
再次执行单元测试代码：  
![img][img 18]  
此时是能够获取到相应的数据的，所以如果两个表字段重名了，进行多表查询时，需要设置`columnPrefix`属性，这样才能够避免不同表同名字段数据覆盖的问题。

所以，在创建数据库的数据表时，尽量不要让表与表中的字段重名。

🍂除了基于`xml`实现多表查询，还可以基于注解实现，使用注解就简单了不少，直接设置参数为相应的 sql 即可，比如再声明一个`getArticleById2`方法，使用注解实现具体的执行 sql。

// 根据文章 id 获取文章对象, 显示用户信息 (基于注解)
    @Select("select a.*, u.* from articleinfo as a left join userinfo as u on  a.uid=u.id where a.id=#{id};")
    ArticleInfo getArticleById2(@Param("id") Integer id);

单元测试代码：

@Test
    void getArticleById2() {
        
        ArticleInfo articleinfo = articleMapper.getArticleById(1);
        System.out.println(articleinfo);
    }

结果如下：

![img][img 19]

#### 5.2. 一对多表映射 ####

一对多的关系，就是对于一个属性，它对映着多个其他的属性，比如用户与博客之间的关系，一个用户可以对应多篇博客，那么用户与博客之间的关系就是一对多的关系；我们再尝试使用 mybatis 实现多对多的多表联查。

🍂下面我们以用户表为主，文章表为辅，来演示如何进行一对多关系的多表查询。

既然是一对多的关系，那我们可以在`UserInfo`类中加上一个储存`ArticleInfo`对象的`List`，来储存用户发布或所写的文章。

@Data
    public class UserInfo {
        
        private Integer id;
        private String username;
        private String password;
        private String photo;
        private String createtime;
        private String updatetime;
        private Integer state;
        // 联表字段
        private List<Articleinfo> aList;
    }

实现多表查询的大致过程如下：

*  在 Mapper 接口中声明方法，我们声明一个方法，就是通过用户 id 获取用户信息以及对应的文章列表。

// 根据 id 查询用户信息, 显示相应文章信息
    UserInfo getUserAndArticlesById(@Param("uid") Integer id);

*  在`xml`文件当中写`resultMap`映射关系，与一对一多表查询不同的是，我们需要设置`collection`标签，而不是`association`标签。
 *  在`xml`文件的`resultMap`标签中至少设置 resultMap 名字`id`，对应映射的类`type`等属性，里面需要设置数据表与类中所有属性的映射，以及设置`collection`标签，需要设置`property`属性表示需映射的对象名，设置 resultMap 即副表的`resultMap`路径，由于你无法保证表与表之间是否存在重名字段，需要设置`columnPrefix`为副表的字段添加上一个前缀，防止重名数据覆盖。

*  在对应的`xml`文件当中写 sql 标签以及语句。

<select id="getUserAndArticlesById" resultMap="BaseMap">
        select u.*, 
               a.id as a_id,
               a.title as a_title,
               a.content as a_content,
               a.createtime as a_createtime,
               a.updatetime as a_updatetime,
               a.uid as a_uid,
               a.rcount as a_rcount,
               a.state as a_state
               from userinfo as u left join articleinfo as a on u.id=a.uid where u.id=#{
        uid}
    </select>

*  编写单元测试代码，验证结果。

@Test
    void getUserAndArticlesById() {
        
        Integer id = 1;
        UserInfo userInfo = userMapper.getUserAndArticlesById(id);
        System.out.println(userInfo);
    }

结果如下：  
![img][img 20]

🍂同样也可以使用注解完成，实现起来更简单。

// 根据 id 查询用户信息, 显示相应文章信息 (基于注解)
    @Select("select u.*, a.* from userinfo as u left join articleinfo as a on u.id=a.uid where u.id=#{uid};")
    UserInfo getUserAndArticlesById2(@Param("uid") Integer id);

单元测试代码：

@Test
    void getUserAndArticlesById2() {
        
        Integer id = 1;
        UserInfo userInfo = userMapper.getUserAndArticlesById(id);
        System.out.println(userInfo);
    }

结果如下：

![img][img 21]

🍂上面的方式都是对两张表联合后进行的查询，是一步实现的，其实也可以分步实现，比如这里可以先根据`uid`查询到用户信息，再由`uid`查询到相应文章信息，可以使用多线程分别执行这两步效率更高，然后将文章信息整合到用户对象中的`aList`字段中即可。

定义方法，这里使用注解实现相应执行 sql。

// 根据用户 id 查询用户信息。
    @Select("select * from userinfo where id=#{id}")
    UserInfo getUserById2(@Param("id")Integer id);
    
    // 根据用户 id 查询相应文章信息 
    @Select("select * from articleinfo where uid=#{uid}")
    List<ArticleInfo> getListByUid(@Param("uid")Integer uid);

单元测试代码：

@Test
    void getUserList() {
        
        int uid = 1;
        // 定义线程池
        ThreadPoolExecutor threadPool =
                new ThreadPoolExecutor(5, 10,
                        100, TimeUnit.SECONDS,
                        new LinkedBlockingQueue<>(100));
        final Object[] resultArray = new Object[2];
        threadPool.submit(new Runnable() {
        
            @Override
            public void run() {
        
                // 1. 根据 uid 查询 userinfo
                resultArray[0] = userMapper.getUserById2(uid);
            }
        });
        threadPool.submit(new Runnable() {
        
            @Override
            public void run() {
        
                // 2. 根据 uid 查询查询文章列表
                resultArray[1] = articleMapper.getListByUid(uid);
            }
        });
        // 组装数据 (等线程池执行完成之后)
        while (threadPool.getTaskCount() !=
                threadPool.getCompletedTaskCount()) {
        
        }
        UserInfo userinfo = (UserInfo) resultArray[0];
        userinfo.setAList((List<ArticleInfo>) resultArray[1]);
        System.out.println(userinfo);
    }

执行测试方法，结果如下：

![img][img 22]

## 二. 动态SQL ##

**动态 SQL 是 MyBatis 的强大特性之一了，如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号；利用动态 SQL，可以彻底摆脱这种痛苦；使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。**

之前介绍的 mybatis 增删查改，所传的参数都是一定会传入的，但是在实际情况中，很多参数都是非必传参数，使用动态 SQL 就可以解决传入的参数是非必传参数的情况。

动态 SQL 可以解决多余符号的问题，如`,`等。

### 1. if标签 ###

`if`标签的作用就是判断一个参数是否有值，如果没有值就将对应的参数隐藏。

语法：

<if test="表达式">
    	sql
    </if>
    // 例如
    <if test="参数!=null">
    	sql部分语句
    </if>

当表达式为真，则插入`if`标签中的`sql`，否则不插入。

我们以在用户表中插入一条记录为例，其中插入的字段中头像`photo`不是必传的参数：

方法声明：

// 使用动态sql插入数据
    int addUser(UserInfo userInfo);

动态SQL语句：  
其中的`photo`是非必传参数，我们使用`if`标签来判断它是否有值，没有值就不插入目标的 sql 语句。

<insert id="addUser">
        insert into userinfo(username, password
        <if test="photo!=null">
            , photo
        </if>
        ) values(#{
        username}, #{
        password}
        <if test="photo!=null">
            , #{
        photo}
        </if>
        )
    </insert>

单元测试代码：

@Test
    void addUser() {
        
        UserInfo userinfo = new UserInfo();
        userinfo.setUsername("zhangsan");
        userinfo.setPassword("123");
        int result = userMapper.addUser(userinfo);
        System.out.println("受影响行数: " + result);
    }

在单元测试代码中，没有给 photo 赋值，if 标签会判断它为空，不会生成插入 photo 字段的 sql。

结果如下：

![img][img 23]  
数据库查询结果：

![img][img 24]  
再来试一试给 photo 传值的情况，此时生成的 sql 有三个参数：

@Test
    void addUser() {
        
        UserInfo userinfo = new UserInfo();
        userinfo.setUsername("lisi");
        userinfo.setPassword("123");
        userinfo.setPhoto("default.png");
        int result = userMapper.addUser(userinfo);
        System.out.println("受影响行数: " + result);
    }

结果如下：  
最终生成的语句多了一个`photo`参数。  
![img][img 25]

数据库结果：

![img][img 26]

### 2. trim标签 ###

`if`标签可以实现非必传参数 sql 的构造，在极端情况下，有很多个非必传参数，此时如果只使用`if`标签构造出的 sql 语句很有可能会多出一个`,`，因为有很多非必传参数，如果只传来一个参数，由于不确定后面是否还会有参数，因此会预留一个`,`，此时如果没有其他参数，就会多出一个`,`。

而`trim`标签可以去除 sql 语句前后多余的指定字符，它需要搭配`if`标签使用。

`<trim>`标签中有如下属性：

*  prefix：表示整个语句块，以 prefix 的值作为前缀
 *  suffix：表示整个语句块，以 suffix 的值作为后缀
 *  prefixOverrides：表示整个语句块要去除掉的前缀
 *  suffixOverrides：表示整个语句块要去除掉的后缀

语法：

假设`username`，`password`，`photo`都是非必传参数，但是至少传递一个，我们来写插入语句的动态 SQL。

<insert id="addUser2">
        insert into userinfo
        <trim prefix="(" suffix=")" prefixOverrides="," suffixOverrides=",">
            <if test="username!=null">
                username,
            </if>
            <if test="password!=null">
                password,
            </if>
            <if test="photo!=null">
                photo
            </if>
        </trim>
        values
        <trim prefix="(" suffix=")" prefixOverrides="," suffixOverrides=",">
            <if test="username!=null">
                #{
        username},
            </if>
            <if test="password!=null">
                #{
        password},
            </if>
            <if test="photo!=null">
                #{
        photo}
            </if>
        </trim>
    </insert>

单元测试代码：

@Test
    void addUser2() {
        
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("wangwu");
        userInfo.setPassword("12345622");
        int result = userMapper.addUser(userInfo);
        System.out.println("受影响行数: " + result);
    }

运行结果与生成的 sql 语句：

我们发现多余的逗号被自动去除了。

![img][img 27]

### 3. where标签 ###

`where`标签主要是替换`where`关键字使用，可以动态的生成条件，如果 sql 中没有使用到`where`（没有查询条件），就不会生成`where`，存在查询条件，就会生成含有 where 的查询 sql 语句，并且可以自动去除前面多余的`and`。

此时我们 userinfo 表中的数据如下：

![img][img 28]  
假设我们可以根据`id`，`username`，`passward`中的一个或者几个获取用户信息。

<select id="getUserListByWhere" resultType="com.example.demo.model.UserInfo">
        select * from userinfo
        <where>
            <if test="id>0">
                id=#{
        id}
            </if>
            <if test="username!=null">
                and username=#{
        username}
            </if>
            <if test="password!=null">
                and  password=#{
        password}
            </if>
        </where>
    </select>

单元测试代码：

这里只传入一个`username`看是否可以自动删除前面多余的`and`。

@Test
    void getListByWhere() {
        
        UserInfo userinfo = new UserInfo();
        userinfo.setUsername("wangwu");
        List<UserInfo> list = userMapper.getUserListByWhere(userinfo);
        System.out.println(list);
    }

结果如下：  
发现自动生成了`where`语句并删除了多余的`and`。  
![img][img 29]  
如果我们不设置任何参数，查询一个`null`值，就不会生成`where`语句。

![img][img 30]

![img][img 31]  
以上`<where>`标签也可以使用 `<trim prefix="where" prefixOverrides="and">` 替换，和以下写法效果是一样的。

<select id="getUserListByWhere" resultType="com.example.demo.model.UserInfo">
        select * from userinfo
        <trim prefix="where" prefixOverrides="and">
            <if test="id>0">
                id=#{
        id}
            </if>
            <if test="username!=null">
                and username=#{
        username}
            </if>
            <if test="password!=null">
               and password=#{
        password}
            </if>
        </trim>
    </select>

### 4. set标签 ###

`set`标签与`where`标签很相似，`where`用来替换查询SQL，而`set`是用于修改 sql 中，用来自动生成`set`部分的SQL语句。

`set`标签可以自动去除最后面的一个`,`。

比如我们写一个能够修改账户名`username`，密码`password`，头像`photo`的动态 SQL，根据`id`进行修改。

方法声明：

// 使用动态SQL实现修改用户信息，包括账户名，密码，头像
    int updateUser(UserInfo userInfo);

动态 SQL：

<update id="updateUser">
        update userinfo
        <set>
            <if test="username!=null">
                username=#{
        username},
            </if>
            <if test="password!=null">
                password=#{
        password},
            </if>
            <if test="photo!=null">
                photo=#{
        photo}
            </if>
        </set>
        where id=#{
        id}
    </update>

单元测试代码：

@Test
    void updateUser() {
        
        UserInfo userinfo = new UserInfo();
        userinfo.setId(3);
        // 修改密码为 456
        userinfo.setPassword("456");
        int result = userMapper.updateUser(userinfo);
        System.out.println("受影响行数 : " + result);
    }

结果如下：  
修改成功并且可以根据传入参数个数自动生成相应的修改 sql，以及可以自动去除最后的`,`。

![img][img 32]

数据库验证：

![img][img 33]  
以上`<set>`标签也可以使用 `<trim prefix="set" suffixOverrides=",">`替换，和以下写法效果是一样的。

<update id="updateUser">
        update userinfo
        <trim prefix="set" suffixOverrides=",">
            <if test="username!=null">
                username=#{
        username},
            </if>
            <if test="password!=null">
                password=#{
        password},
            </if>
            <if test="photo!=null">
                photo=#{
        photo}
            </if>
        </trim>
        where id=#{
        id}
    </update>

### 5. foreach标签 ###

对集合进行遍历可以使用`foreach`标签，常用的场景有批量删除功能。

*  collection：绑定方法参数中的集合，如 List，Set，Map或数组对象
 *  item：遍历时的每一个对象
 *  open：语句块开头的字符串
 *  close：语句块结束的字符串
 *  separator：每次遍历之间间隔的字符串

为了方便演示批量删除，我们随便插入几条数据到数据库：  
![img][img 34]  
方法声明：

// 使用动态 sql 批量删除元素
    public int deleteIds(List<Integer> ids);

动态 SQL 语句：

<delete id="delByIds">
        delete from userinfo where id in
        <foreach collection="ids" open="(" close=")" item="id" separator=",">
            #{
        id}
        </foreach>
    </delete>

单元测试代码：  
删除数据库中`id`为 8 9 10 11 的用户。

@Test
    void delByIds() {
        
        List<Integer> list = new ArrayList<Integer>() {
        {
        
            add(8);
            add(9);
            add(10);
            add(11);
        }};
        int result = userMapper.delByIds(list);
        System.out.println("受影响行数" + result);
    }

结果如下：  
![img][img 35]  
验证数据库，成功生成了批量删除的 sql，这就是`foreach`标签的作用，它能够遍历集合。  
![img][img 36]

[MyBatis]: https://blog.csdn.net/Trong_/article/details/132723722?spm=1001.2014.3001.5501
[img]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/74846452041d49c186e1a6ea278ea4ee.png
[img 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/1f6fd24d0c4c48c68bab7833b2bfaec6.png
[img 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/9a6943d41465463dba6f197664d6e2cc.png
[img 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/b4377710e5d44cac94138dd8b140dc32.png
[img 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/aa37a0d7baf245e5aaec24de492911c3.png
[img 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/441438727c234223be319249ffe4530a.png
[img 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/cd676c93b7a745569aaddf49c0299c28.png
[img 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/e9b2236cfafc4a9cb249c1abf81b329b.png
[img 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/2c50cb581ac9467093a1b74e568ea546.png
[img 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/7d6f8e0d78ac4970b51429550a077717.png
[img 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/4ed9c1d292f8423fb62dc3c1e8e24a1e.png
[img 11]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/1f377fc18e5f4cfbad2df963dc8a3795.png
[img 12]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/6afc193131824ee1b60089dbbaa6f3ce.png
[img 13]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/6ed2650cd13b4138b059857552cbb6fe.png
[img 14]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/a8d0ccc78c0a467390d228ee1a5093d6.png
[img 15]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/4a13e4fa970146e0ab41d1662fd44d04.png
[img 16]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/1001332e142f4f308c61ce33704ea79e.png
[img 17]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/ecc8c9423f1340f5a2a35c95651fb22c.png
[img 18]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/ff0704c5e6d64ea8a0615c74ca56e6fb.png
[img 19]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/967b63850a9a4d37b8ee4b855feacbbc.png
[img 20]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/3ac883e85e3945ec9d78b099d2dec3e8.png
[img 21]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/9ea78d1c26144b9185f282da3b1152f7.png
[img 22]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/d505189426724ef6a829c8440de83d94.png
[img 23]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/7218ac7d7c2c4f1c873f543473a99883.png
[img 24]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/c84d8f50049a4e2abab4a679778bd280.png
[img 25]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/1e8489ccf45841be9ef302882457c502.png
[img 26]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/b3c010ad0c6b4a8f94d54f8113118878.png
[img 27]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/6b601ab7bfca46ad970fc8eec6e58bcc.png
[img 28]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/56583f7ee1cb4825b48829db27923a0e.png
[img 29]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/0e84d6558c134104a5fbecc1317c5da0.png
[img 30]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/0fe8196cec6d4f0cb102e1b0cfd64b07.png
[img 31]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/434521e2399d4813ac2ab5f7157e5ff0.png
[img 32]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/4783fa17365244169566441b42ef46b8.png
[img 33]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/862333364358469d8d4d057f9e9df009.png
[img 34]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/9d34cad4da014d83a67962f7b63b212b.png
[img 35]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/db542b665ad04486905ff2b3f5416f01.png
[img 36]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/655a85a9b55441a9a72a1ef158dbd0b3.png