【Spring Boot】Mybatis操作数据库，动态SQL：＜if＞标签、＜trim＞标签、＜where＞标签、＜set＞标签、＜foreach＞标签、＜sql＞和＜include＞标签

06-01 1201阅读

动态SQL

1.动态SQL的作用？

1.1 动态SQL的作用
1.2 标签
2.准备数据

2.1.MySQL数据准备
2.2.创建对应的实体类
3.\标签

3.1 在注解中使用
3.2 在XML文件中实现
3.3 不使用\标签
4.\标签

4.1 标签的属性
4.2 在注解中使用
4.3 在XML文件中使用
5.\标签

5.1 不使用\标签
5.2 在注解中使用
5.3 在XML文件中使用
6.\标签

6.1 在注解中使用
6.2 在XML文件中使用
6.\标签

6.1 属性介绍
6.2 在注解中使用
6.3 在XML文件中使用

7.\和\标签

1.动态SQL的作用？

1.1 动态SQL的作用

当我们注册时需要填写一些信息，有写信息必须填写，有些信息可以不填写，这时我们使用传统的SQL无法满足需求，需要使用动态SQL。

比如，注册时让填写用户名（必填）、密码（必填）、电话（非必填）；

如果使用传统的SQL：

insert into user_info username, password, phone values(?,?,?);

当只填写用户名和密码时，再用上述的SQL语句就会报错，需要改成使用如下的SQL语句：

insert into user_info username, password values(?,?);

当用户填写用户名、密码和电话时使用上述的SQL语句也会报错

上述的文件需要使用动态SQL的方法，动态SQL的实现需要使用标签。

1.2 标签

标签
功能：根据条件动态拼接SQL片段，只有当条件满足时，标签内的SQL才会被包含。
语法：
```
    SQL片段
```
标签
功能：对SQL片段进行前缀或后缀的添加或移除，灵活处理SQL拼接中的多余关键字（如AND、OR、,等）。
语法：
```
	
	    SQL片段
	
```
标签
功能：专门用于动态生成WHERE子句，自动处理多余的AND或OR，简化WHERE条件的拼接。
语法：
```
    SQL条件片段
```
标签
功能：用于动态生成UPDATE语句中的SET子句，自动移除多余的逗号（,）。
语法：
```
    SQL更新片段
```
和标签
功能：
：定义可重用的SQL片段。
：引用中定义的片段。
语法：
```
	
	
	    SQL内容
	
	
	
	
```

2.准备数据

2.1.MySQL数据准备

（1）创建数据库：

CREATE DATABASE mybatis_study DEFAULT CHARACTER SET utf8mb4;

（2）使用数据库

-- 使⽤数据数据
USE mybatis_study;

（3）创建用户表

-- 创建表[⽤⼾表]
CREATE TABLE `user_info` (
 `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
 `username` VARCHAR ( 127 ) NOT NULL,
 `password` VARCHAR ( 127 ) NOT NULL,
 `age` TINYINT ( 4 ) NOT NULL,
 `gender` TINYINT ( 4 ) DEFAULT '0' COMMENT '1-男 2-⼥ 0-默认',
 `phone` VARCHAR ( 15 ) DEFAULT NULL,
 `delete_flag` TINYINT ( 4 ) DEFAULT 0 COMMENT '0-正常, 1-删除',
 `create_time` DATETIME DEFAULT now(),
 `update_time` DATETIME DEFAULT now() ON UPDATE now(),
 PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

（4）添加用户信息

-- 添加⽤⼾信息
INSERT INTO mybatis_study.user_info( username, `password`, age, gender, phone )
VALUES ( 'admin', 'admin', 18, 1, '18612340001' );
INSERT INTO mybatis_study.user_info( username, `password`, age, gender, phone )
VALUES ( 'zhangsan', 'zhangsan', 18, 1, '18612340002' );
INSERT INTO mybatis_study.user_info( username, `password`, age, gender, phone )
VALUES ( 'lisi', 'lisi', 18, 1, '18612340003' );
INSERT INTO mybatis_study.user_info( username, `password`, age, gender, phone )
VALUES ( 'wangwu', 'wangwu', 18, 1, '18612340004' );

2.2.创建对应的实体类

实体类的属性名与表中的字段名⼀⼀对应

@Data
public class UserInfo {
    private Integer id;
    private String username;
    private String password;
    private Integer age;
    private Integer gender;
    private String phone;
    private Integer deleteFlag;
    private Date createTime;
    private Date updateTime;
}

注意：在实际开发中不管什么实体类都要设置删除标志、创建时间、修改时间

3.标签

3.1 在注解中使用

在注解中写标签时需要使用标签

Mapper接口：

@Mapper
public interface UserInfoMapper {
    @Options(useGeneratedKeys= true, keyProperty = "id")
    @Insert(""+
            "insert into user_info (username, password, age, "+
            "gender,"+
            "phone)"+
            "values(#{username}, #{password},#{age},"+
            " #{gender},"+
            " #{phone})"
          +"")
    Integer Insert(UserInfo userInfo);
}

说明： #{gender},中的gender如果不为空，则把#{gender},拼接到SQL字符串中；如果gender为空，则#{gender},不会在SQL语句中出现。

测试代码：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoMapperTest {
    @Test
    void insert() {
        UserInfo userInfo =new UserInfo();
        userInfo.setUsername("333");
        userInfo.setPassword("333");
        userInfo.setAge(20);
        // 不填写gender
//        userInfo.setGender(1);
        userInfo.setPhone("3333");
        Integer count = userInfoMapper.Insert(userInfo);
        log.info("影响的行数："+count + "  返回的id："+ userInfo.getId());
    }

SQL中的结果：

如果测试代码改为如下：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoMapperTest {
    @Test
    void insert() {
        UserInfo userInfo =new UserInfo();
        userInfo.setUsername("333");
        userInfo.setPassword("333");
        userInfo.setAge(20);
        userInfo.setGender(1);
//        userInfo.setPhone("3333");
        Integer count = userInfoMapper.Insert(userInfo);
        log.info("影响的行数："+count + "  返回的id："+ userInfo.getId());
    }
}

运行结果：

可以看到Preparing中的SQL语句中多一个，号，需要学习标签

3.2 在XML文件中实现

Mapper接口：

@Mapper
public interface UserInfoXMLMapper {
    //定义接口
    Integer insert(UserInfo userInfo);
}

XML文件：



    
        insert into user_info (username,
                               password,
                               age,
        
                               gender,
        
        
                               phone
        
        )
        values(
               #{username},
               #{password},
               #{age},
        
               #{gender},
        
        
               #{phone}
        
        )

测试类：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoXMLMapperTest {
	
    @Autowired
    private UserInfoXMLMapper userInfoXMLMapper;
    @Test
    void insert() {
        UserInfo userInfo= new UserInfo();
        userInfo.setUsername("4444");
        userInfo.setPassword("4444");
        userInfo.setAge(19);
        // 不填写gender
//        userInfo.setGender(1);
        userInfo.setPhone("4444");
        Integer count = userInfoXMLMapper.insert(userInfo);
        log.info("影响的行数："+count + "  返回的主键："+ userInfo.getId());
    }
}

运行结果：

MySQL插入的结果：

如果测试代码改为如下：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoXMLMapperTest {
	@Autowired
    private UserInfoXMLMapper userinfoXMLMapper;
    @Test
    void insert() {
        UserInfo userInfo =new UserInfo();
        userInfo.setUsername("333");
        userInfo.setPassword("333");
        userInfo.setAge(20);
        userInfo.setGender(1);
//        userInfo.setPhone("3333");
        Integer count = userInfoXMLMapper.Insert(userInfo);
        log.info("影响的行数："+count + "  返回的id："+ userInfo.getId());
    }
}

运行结果：

可以看到Preparing中的SQL语句中多一个，号，需要学习标签

3.3 不使用标签

有同学就会想，把所有的的字段都列出来，想插入什么值就在对象中设置什么值，使用标签不是麻烦了吗！

如下面：

Mapper接口

@Mapper
public interface UserInfoMapper {
    @Insert("insert into user_info (username, password, age, gender, phone)"+
    "values(#{username}, #{password}, #{age}, #{gender}, #{phone})")
    Integer Insert(UserInfo userInfo);
}

测试代码：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoMapperTest {
    @Test
    void insert() {
        UserInfo userInfo =new UserInfo();
        userInfo.setUsername("333");
        userInfo.setPassword("333");
        userInfo.setAge(20);
        // 不填写gender
//        userInfo.setGender(1);
        userInfo.setPhone("3333");
        Integer count = userInfoMapper.Insert(userInfo);
        log.info("影响的行数："+count + "  返回的id："+ userInfo.getId());
    }

运行结果：

插入SQL中的结果：

而使用标签插入的结果是0，哪一个是对的呢？

看一下创建表user_info设置的默认值：

gender设置的默认值为0，为什么插入的是null呢？

答：创建的userInfo对象时会把所有属性初始化为默认值，String类型的默认值为null，所以把null作为参数传入了SQL中。

所以，动态SQL需要使用标签。

4.标签

4.1 标签的属性

当SQL语句中使用多个标签是会出现有,多余的问题，标签就可以派上用场了。

标签中有如下属性：

（1）prefix：表⽰整个语句块，以prefix的值作为前缀

（2）suffix：表⽰整个语句块，以suffix的值作为后缀

（3）prefixOverrides：表⽰整个语句块要去除掉的前缀

（4）suffixOverrides：表⽰整个语句块要去除掉的后缀

4.2 在注解中使用

Mapper接口：

@Mapper
public interface UserInfoMapper {
    @Options(useGeneratedKeys= true, keyProperty = "id")
    @Insert(""+
            "insert into user_info ("+
                ""+
                "username, password, age, "+
                "gender,"+
                "phone"+
                ""+
            ")"+
            "values("+
                ""+
                "#{username}, #{password},#{age},"+
                " #{gender},"+
                " #{phone}"+
                ""+
            ")"+
            "")
    Integer Insert(UserInfo userInfo);
}

测试代码：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void insert() {
        UserInfo userInfo =new UserInfo();
        userInfo.setUsername("444");
        userInfo.setPassword("4444");
        userInfo.setAge(20);
        userInfo.setGender(1);
//        userInfo.setPhone("4444");
        Integer count = userInfoMapper.Insert(userInfo);
        log.info("影响的行数："+count + "  返回的id："+ userInfo.getId());
    }
}

运行结果：

MySQL：

Mapper最终的改进：

@Mapper
public interface UserInfoMapper {
    @Options(useGeneratedKeys= true, keyProperty = "id")
    @Insert(""+
            "insert into user_info "+
                ""+
                "username, password, age, "+
                "gender,"+
                "phone"+
                ""+
            "values "+
                ""+
                "#{username}, #{password},#{age},"+
                " #{gender},"+
                " #{phone}"+
                ""+
            "")
    Integer Insert(UserInfo userInfo);
}

4.3 在XML文件中使用

Mapper接口：

@Mapper
public interface UserInfoXMLMapper {
    //定义接口
    Integer insert(UserInfo userInfo);
}

XML文件：



    
        insert into user_info (
        
            username,
            password,
            age,
            
                gender,
            
            
                phone
            
        
        )
        values(
        
            #{username},
            #{password},
            #{age},
            
                #{gender},
            
            
                #{phone}
            
        
        )

测试代码：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoXMLMapperTest {
    @Autowired
    private UserInfoXMLMapper userInfoXMLMapper;
    @Test
    void insert() {
        UserInfo userInfo= new UserInfo();
        userInfo.setUsername("5555");
        userInfo.setPassword("5555");
        userInfo.setAge(20);
        userInfo.setGender(1);
        //userInfo.setPhone("5555");
        Integer count = userInfoXMLMapper.insert(userInfo);
        log.info("影响的行数："+count + "  返回的主键："+ userInfo.getId());
    }
}

运行结果：

MySQL：

XML文件最终的改进：



    
        insert into user_info
        
            username,
            password,
            age,
            
                gender,
            
            
                phone
            
        
        values
        
            #{username},
            #{password},
            #{age},
            
                #{gender},
            
            
                #{phone}

在以上 sql 动态解析时，会将第⼀个部分做如下处理：

（1）基于prefix配置，开始部分加上配置，结束部分加上“（”

（2）基于suffix配置，结束部分加上“）”

（3）多个组织的语句可能以,结尾，在最后拼接好的字符串还会以suffixOverrides 配置去掉最后⼀个,

5.标签

需求: 传⼊的用户对象，根据属性做where条件查询，用户对象中属性不为 null 的，都为查询条件.。

如 username 为 “a”，则查询条件为 where username=“a”

5.1 不使用标签

Mapper接口：

@Mapper
public interface UserInfoMapper {
    @Select(""+
            "select id,username, password, age, gender, phone,delete_flag,create_time,update_time "+
            " from user_info "+
            " where "+
            " username=#{username}"+
            " and age=#{age}  "+
            " and phone=#{phone}"+
            ""
    )
    List queryAllUser(UserInfo userInfo);
 }

测试代码：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void queryAllUser() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("lisi");
        userInfo.setAge(20);
        userInfo.setPhone("lisi");
        userInfoMapper.queryAllUser(userInfo);
    }
}

运行结果：

把测试代码改为：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void queryAllUser() {
        UserInfo userInfo = new UserInfo();
        //userInfo.setUsername("lisi");
        userInfo.setAge(20);
        userInfo.setPhone("lisi");
        userInfoMapper.queryAllUser(userInfo);
    }
}

运行结果：

把测试代码改为：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void queryAllUser() {
        UserInfo userInfo = new UserInfo();
        //userInfo.setUsername("lisi");
        //userInfo.setAge(20);
        //userInfo.setPhone("lisi");
        userInfoMapper.queryAllUser(userInfo);
    }
}

运行结果：

上述两个问题可以使用在where语句后添加 1=1（不推荐），也可以使用标签解决

5.2 在注解中使用

Mapper接口：

@Mapper
public interface UserInfoMapper {
    @Select(""+
            "select id,username, password, age, gender, phone,delete_flag,create_time,update_time "+
            " from user_info "+
            " "+
            "and username=#{username}"+
            " and age=#{age}  "+
            " and phone=#{phone}"+
            ""+
            ""
    )
    List queryAllUser(UserInfo userInfo);
}

测试代码：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void queryAllUser() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("lisi");
        userInfo.setAge(20);
        userInfo.setPhone("lisi");
        userInfoMapper.queryAllUser(userInfo);
    }
}

运行结果：

测试代码：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void queryAllUser() {
        UserInfo userInfo = new UserInfo();
        //userInfo.setUsername("lisi");
        userInfo.setAge(20);
        userInfo.setPhone("lisi");
        userInfoMapper.queryAllUser(userInfo);
    }
}

运行结果：

5.3 在XML文件中使用

Mapper接口：

@Mapper
public interface UserInfoXMLMapper {
    List queryAllUser(UserInfo uerInfo);
}

XML文件：



    
        
        
        
        
    
    
        select id ,username, password, age, gender, phone,
               delete_flag, create_time ,
               update_time from user_info
        
            
                and username=#{username}
            
            
                and age=#{age}
            
            
                and phone=#{phone}

测试代码：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoXMLMapperTest {
    @Autowired
    private UserInfoXMLMapper userInfoXMLMapper;
    @Test
    void queryAllUser() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("lisi");
        userInfo.setAge(20);
        userInfo.setPhone("lisi");
        List list = userInfoXMLMapper.queryAllUser(userInfo);
        for (Object item : list) {
            log.info(item.toString());
        }
    }
}

运行结果：

测试代码：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoXMLMapperTest {
    @Autowired
    private UserInfoXMLMapper userInfoXMLMapper;
    @Test
    void queryAllUser() {
        UserInfo userInfo = new UserInfo();
        //userInfo.setUsername("lisi");
        //userInfo.setAge(20);
        //userInfo.setPhone("lisi");
        List list = userInfoXMLMapper.queryAllUser(userInfo);
        for (Object item : list) {
            log.info(item.toString());
        }
    }
}

运行结果：

6.标签

需求: 根据传⼊的⽤⼾对象属性来更新⽤⼾数据，可以使⽤标签来指定动态内容.接⼝定义: 根据传⼊的⽤⼾ id 属性，修改其他不为 null 的属性

6.1 在注解中使用

Mapper接口：

@Mapper
public interface UserInfoMapper {
    @Update(
            ""+
            "update user_info " +
                ""+
                    " username=#{username}, " +
                    " password=#{password},"+
                    " age=#{age},"+
                    "gender=#{gender},"+
                    " phone=#{phone}, "+
                ""+
            "where id = #{id} "+
            ""
    )
    Integer update(UserInfo userInfo);
}

测试代码：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoMapperTest {
    @Test
    void update() {
        UserInfo userInfo = new UserInfo();
        userInfo.setId(3);
        userInfo.setUsername("33333");
        userInfo.setPhone("33333");
        userInfoMapper.update(userInfo);
    }
}

运行结果：

MySQL：

6.2 在XML文件中使用

Mapper接口：

@Mapper
public interface UserInfoXMLMapper {
    Integer update(UserInfo uerInfo);
}

XML文件：



    
        
        
        
        
    
    
        update user_info
        
            
                username=#{username},
            
            
                password=#{password},
            
            
                age=#{age},
            
            
                gender=#{gender},
            
            
                phone=#{phone},
            
        
        where id =#{id}

测试代码：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoXMLMapperTest {
    @Autowired
    private UserInfoXMLMapper userInfoXMLMapper;
    @Test
    void update() {
        UserInfo userInfo= new UserInfo();
        userInfo.setUsername("44444");
        userInfo.setPhone("44444");
        userInfo.setId(4);
        userInfoXMLMapper.update(userInfo);
    }
}

运行结果：

MySQL：

6.标签

6.1 属性介绍

对集合进⾏遍历时可以使⽤该标签。标签有如下属性：

（1）collection：绑定⽅法参数中的集合，如 List，Set，Map或数组对象

（2）item：遍历时的每⼀个对象

（3）open：语句块开头的字符串

（4）close：语句块结束的字符串

（5）separator：每次遍历之间间隔的字符串

6.2 在注解中使用

需求: 根据多个userid, 删除⽤⼾数据

Mapper接口：

@Mapper
public interface UserInfoMapper {
    @Delete(""+
            "delete from user_info where id in "+
            ""+
            "#{id}"+
            ""+
            ""
    )
    Integer delete(@Param("ids") List ids);
}

测试代码：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void delete() {
        List list = Arrays.asList(29,30);
        userInfoMapper.delete(list);
    }

运行结果：

6.3 在XML文件中使用

需求: 根据多个userid, 删除⽤⼾数据

Mapper接口：

@Mapper
public interface UserInfoXMLMapper {
    // 定义接口
    // 注意，这里要用@param注解明确参数
    Integer delete(@Param("ids") List ids);
}

XML文件：



    
        
        
        
        
    
    
        delete from user_info
               where id in
        
            #{id}

测试代码：

@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoXMLMapperTest {
    @Autowired
    private UserInfoXMLMapper userInfoXMLMapper;
    @Test
    void delete() {
        List list = Arrays.asList(25,26,27,28);
        userInfoXMLMapper.delete(list);
    }
}

运行结果：

7.和标签

在xml映射⽂件中配置的SQL，有时可能会存在很多重复的⽚段，此时就会存在很多冗余的代码

我们可以对重复的代码⽚段进⾏抽取，将其通过标签封装到⼀个SQL⽚段，然后再通过进行引用

定义可重⽤的SQL⽚段

通过属性refid，指定包含的SQL⽚段

如：

	id, username, age, gender, phone, delete_flag, create_time, update_time

通过标签在需要的位置进行引用


	select
	
	from userinfo

	
	select
	
	from userinfo where id= #{id}

免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们。