Mybatis高级(动态SQL)

目录

一、动态SQL

1.1 数据准备：

1.2 <if>标签

1.3<trim> 标签

1.4<where>标签

1.5<set>标签

1.6 <foreach>标签 

1.7<include>  标签

---

一、动态SQL

动态SQL是Mybatis的强⼤特性之⼀，能够完成不同条件下不同的sql拼接

在注册用户的时候，可能会有这样⼀个问题，如下图所示：

修改个人信息时会有两种字段：必填字段和非必填字段，那如果在添加用户的时候有不确定的字段传入。这个时候就需要使用动态标签来判断了，比如添加的时候性别gender为非必填字段，具体实现如下:

1.1 数据准备：

UserInfo类：

@Data @NoArgsConstructor @AllArgsConstructor 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; }

user_info表：

-- 创建数据库 DROP DATABASE IF EXISTS mybatis_test; CREATE DATABASE mybatis_test DEFAULT CHARACTER SET utf8mb4; -- 使⽤数据数据 USE mybatis_test; -- 创建表[⽤⼾表] DROP TABLE IF EXISTS user_info; 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(), PRIMARY KEY (`id`) ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; -- 添加用户信息 INSERT INTO mybatis_test.user_info(username, `password`, age, gender, phone) VALUES ('admin', 'admin', 18, 1, '18612340001'); INSERT INTO mybatis_test.user_info(username, `password`, age, gender, phone) VALUES ('zhangsan', 'zhangsan', 18, 1, '18612340002'); INSERT INTO mybatis_test.user_info(username, `password`, age, gender, phone) VALUES ('lisi', 'lisi', 18, 1, '18612340003'); INSERT INTO mybatis_test.user_info(username, `password`, age, gender, phone) VALUES ('wangwu', 'wangwu', 18, 1, '18612340004');

article_info表：

-- 创建⽂章表 DROP TABLE IF EXISTS article_info; CREATE TABLE articleinfo ( id INT PRIMARY KEY auto_increment, title VARCHAR(100) NOT NULL, content TEXT NOT NULL, uid INT NOT NULL, delete_flag TINYINT(4) DEFAULT 0 COMMENT 'O - 正常，1 - 删除', create_time DATETIME DEFAULT now(), update_time DATETIME DEFAULT now() ) DEFAULT charset 'utf8mb4'; # 插入数据 INSERT INTO articleinfo ( title, content, uid ) VALUES ( 'Java', 'Java正⽂', 1 ); INSERT INTO article_info ( title, content, uid ) VALUES ( 'Python', 'Python正⽂', 2 ); INSERT INTO article_info ( title, content, uid ) VALUES ( 'MySQL', 'MySQL正⽂', 1);

 在数据库中查询数据:

user_info表：

article_info表：

1.2 <if> 标签

Mapper接口：

Integer insertUserByCondition(UserInfo userInfo);

Mapper.xml实现:

<?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.example.book.mapper.UserInfoMapper"> <insert id="insertUserByCondition"> INSERT INTO user_info ( username, `password`, age, <if test="gender != null"> gender, </if> phone) VALUES ( #{username}, #{age}, <if test="gender != null"> #{gender}, </if> #{phone}) </insert> </mapper>

测试：

1、设置gender不为空

@Test void insertUserByCondition() { log.info("添加用户信息"); UserInfo userInfo = new UserInfo(); userInfo.setUsername("小王"); userInfo.setPassword("123456"); userInfo.setAge(18); userInfo.setGender(1); userInfo.setPhone("12345678901"); userInfoMapper.insertUserByCondition(userInfo); log.info("添加成功！"); }

测试结果：

2、设置gender为空

@Test void insertUserByCondition() { log.info("添加用户信息"); UserInfo userInfo = new UserInfo(); userInfo.setUsername("小王"); userInfo.setPassword("123456"); userInfo.setAge(18); // userInfo.setGender(1); userInfo.setPhone("12345678901"); userInfoMapper.insertUserByCondition(userInfo); log.info("添加成功！"); }

测试结果：

1.3 <trim>标签

之前的插入用户功能，只是有一个gender字段可能是选填项，如果有多个字段，一般考虑使用标签结合标签，对多个字段都采取动态生成的方式。 标签中有如下属性：

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

调整Mapper.xml的插入语句为：

<?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.example.book.mapper.UserInfoMapper"> <insert id="insertUserByCondition"> INSERT INTO user_info <trim prefix="(" suffix=")" suffixOverrides=","> <if test="username !=null"> username, </if> <if test="password !=null"> `password`, </if> <if test="age != null"> age, </if> <if test="gender != null"> gender, </if> <if test="phone != null"> phone, </if> </trim> VALUES <trim prefix="(" suffix=")" suffixOverrides=","> <if test="username !=null"> #{username}, </if> <if test="password !=null"> #{password}, </if> <if test="age != null"> #{age}, </if> <if test="gender != null"> #{gender}, </if> <if test="phone != null"> #{phone} </if> </trim> </insert> </mapper>

测试： 

运行结果：

 

在以上sql动态解析时，会将第一个部分做如下处理： ·基于prefix配置，开始部分加上（ ·基于suffix配置，结束部分加上） ·多个组织的语句都以，结尾，在最后拼接好的字符串还会以，结尾，会基于suffixOverrides配置去掉最后一个，

1.4<where>标签

当我们查询不同电脑时，系统会根据我们的筛选条件，动态组装where条件

实现：

 接口定义:

List<UserInfo> queryByCondition(Integer age, Integer gender, Integer deleteFlag);

Mapper.xml实现:

<?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.example.book.mapper.UserInfoMapper"> <select id="queryByCondition" resultType="com.example.book.model.UserInfo"> select id, username, age, gender, phone, delete_flag, create_time, update_time from user_info <where> <if test="age != null"> and age = #{age} </if> <if test="gender != null"> and gender = #{gender} </if> <if test="deleteFlag != null"> and delete_flag = #{deleteFlag} </if> </where> </select> </mapper>

测试：

测试结果：

只会在子元素有内容的情况下才插入where子句，而且会自动去除子句的开头的AND或 OR  

1.5<set>标签

需求：根据传入的用户对象属性来更新用户数据，可以使用标签来指定动态内容. 接口定义：根据传入的用户id属性，修改其他不为null的属性

Mapper接口：

Integer updateUserByCondition(UserInfo userInfo);

Mapper.xml：

<?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.example.book.mapper.UserInfoMapper"> <update id="updateUserByCondition"> update user_info <set> <if test="username != null"> username = #{username}, </if> <if test="age != null"> age = #{age}, </if> <if test="deleteFlag != null"> delete_flag = #{deleteFlag}, </if> </set> where id = #{id} </update> </mapper>

<set>：动态的在SQL语句中插入set关键字，并会删掉额外的逗号.（用于update语句中）

测试：

@Test void updateUserByCondition() { UserInfo userInfo = new UserInfo(); userInfo.setId(12); userInfo.setUsername("小张"); userInfo.setAge(22); userInfo.setDeleteFlag(1); userInfoMapper.updateUserByCondition(userInfo); }

测试结果：

1.6 <foreach>标签 

对集合进行遍历时可以使用该标签。标签有如下属性

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

需求:根据多个userid,删除用户数据

Mapper接口:

void deleteByIds(List<Integer> ids);

ArticleMapper.xml 中新增删除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.example.book.mapper.ArticleInfoMapper"> <delete id="deleteByIds"> delete from article_info where id in <foreach collection="ids" item="id" separator="," open="(" close=")"> #{id} </foreach> </delete> </mapper>

测试：

@Test void testDeleteByIds() { articleInfoMapper.deleteByIds(List.of(2,3)); }

运行结果：

1.7 <include> 标签

在xmI映射文件中配置的SQL，有时可能会存在很多重复的片段，此时就会存在很多余的代码

我们可以对重复的代码片段进行抽取，将其通过<sql>标签封装到一个SQL片段，然后再通过 <include>标签进行引用。 <sql>：定义可重用的SQL片段 <include>：通过属性refid，指定包含的SQL片段 

<sql id="allColumn"> id, username, age, gender, phone, delete_flag, create_time, update_time </sql>

通过标签在原来抽取的地方进行引用。操作如下：

<select id="queryAllUser" resultMap="BaseMap"> select <include refid="allColumn"></include> from user_info </select> <select id="queryById" resultType="com.example.demo.model.UserInfo"> select <include refid="allColumn"></include> from userinfo where id= #{id} </select>