Mybatis进阶——动态SQL

动态SQL 是Mybatis的强大特性之一，能够完成不同条件下的不同SQL拼接，可以参考官方文档：动态 SQL_MyBatis中文网

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.mybatis.model.UserInfo"></mapper>

1. <if> 标签

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

注册分为两种字段：必填字段和非必填字段，那如果在添加用户的时候有不确定的字段传入，程序应该如何实现呢，这个时候就需要使用动态标签来判断了，比如添加的时候性别 gender 为非必填字段，具体实现如下：

userInfoMapper接口代码：

@Mapper
public interface UserInfoMapper {Integer insertByCondtion(UserInfo userInfo);
}

xml配置文件：

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

测试类代码：

@SpringBootTest
class UserInfoMapperTest {@Autowiredprivate UserInfoMapper userInfoMapper;@Testvoid insertByCondtion() {UserInfo userInfo = new UserInfo();userInfo.setUsername("zhaoliu");userInfo.setPassword("zhaoliu");userInfo.setAge(15);
//        userInfo.setGender(1);userInfoMapper.insertByCondtion(userInfo);}
}

表中gender属性默认是0，如图：

当我们不指定gender值时，直接执行上面代码，结果如下：

当指定gender的值时，代码如下：

@SpringBootTest
class UserInfoMapperTest {@Autowiredprivate UserInfoMapper userInfoMapper;@Testvoid insertByCondtion() {UserInfo userInfo = new UserInfo();userInfo.setUsername("zhaoliu");userInfo.setPassword("zhaoliu");userInfo.setAge(15);userInfo.setGender(1);userInfoMapper.insertByCondtion(userInfo);}
}

结果如下：

这就是动态SQL语句，想给他传值就变成所传的值，不想给他传值会有默认值0。也就变成复杂SQL了，之前博客写的那些都是简单标签，如果没有<if> 标签，单凭借之前的那些简单SQL，是不能做到上面这样的功能的。

上面的SQL语句也可以写成注解的形式，但是不推荐，其代码如下：

@Mapper
public interface UserInfoMapper {@Insert("<script>" +"insert into userinfo (username, password, age" +"<if test='gender != null'>, gender</if>)" +"values (#{username},#{password}, #{age}" +"<if test='gender != null'>, #{gender}</if>)"+"</script>")Integer insertByCondtion2(UserInfo userInfo);
}

可以看出注解的形式非常复杂；

2. <trim> 标签

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

标签中有如下属性：

prefix：表示整个语句块，以 prefix 的值作为前缀。

suffix：表示整个语句块，以 suffix 的值作为后缀。

prefixOverrides：表示整个语句块要去除掉的前缀。

suffixOverrides：表示整个语句块要去除掉的后缀。

把username设置默认为admin，代码如下：

DROP DATABASE IF EXISTS mybatis_test;CREATE DATABASE mybatis_test DEFAULT CHARACTER SET utf8mb4;-- 使用数据数据
USE mybatis_test;-- 创建表[用户表]
DROP TABLE IF EXISTS userinfo;
CREATE TABLE `userinfo` (`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,`username` VARCHAR ( 127 ) NOT NULL DEFAULT 'admin' COMMENT '默认admin',`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.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'shangjialu', '111111', 18, 1, '15809211621' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'shenmengyao', '222222', 18, 2, '18612340002' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'yuanyiqi', '333333', 18, 2, '18612340003' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'zuojinyuan', '555555', 18, 2, '18612340004' );

Mapper.xml的内容如下：

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

把setUsername的方法注释掉，测试类代码如下：

 @Testvoid insertByCondtion() {UserInfo userInfo = new UserInfo();
//        userInfo.setUsername("zhaoliu");userInfo.setPassword("zhoushiyu");userInfo.setAge(15);userInfo.setGender(1);userInfoMapper.insertByCondation(userInfo);}

会出现如下的报错信息；

使用 <trim>标签，代码如下：

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

结果如下：

1、基于 prefix 配置，开始部分加上 (

2、基于 suffix 配置，结束部分加上 )

3、多个组织的语句都以 " , " 开头，拼接好的字符串还会以 " , " 开头，会基于 suffixOverrides 配置去掉开头的一个 " , " （prefixOverrides则相反，是结尾）

4、注意 < if test="username != null"> 中的 username 是传入对象的属性

3. <where> 标签

一般网页都会有筛选条件，系统会根据我们的筛选条件，动态组装 where 条件。

UserInfoMapper接口代码：

@Mapper
public interface UserInfoMapper {List<UserInfo> queryUserByCondition(UserInfo userInfo);
}

xml配置信息如下：

  <select id="queryUserByCondition" resultType="com.example.zxslzw_mybaties.model.UserInfo">select * from userinfo where<if test="age != null">age = #{age}</if><if test="gender != null">and gender = #{gender}</if><if test="deleteFlag != null">and delete_flag = #{deleteFlag}</if></select>

测试类代码：

 @Testvoid queryUserByCondition() {UserInfo userInfo = new UserInfo();
//        userInfo.setAge(15);userInfo.setGender(2);userInfo.setDeleteFlag(0);System.out.println(userInfoMapper.queryUserByCondition(userInfo));}

把设置age给注释掉，是可以成功运行的，但注释掉后，就会报错，报错信息如下：

现在给XML内容加上<trim>标签，内容如下：

 <select id="queryUserByCondition" resultType="com.example.zxslzw_mybaties.model.UserInfo">select * from userinfo whereselect * from userinfo<trim prefix="where" prefixOverrides="and"><if test="age != null">age = #{age}</if><if test="gender != null">and gender = #{gender}</if><if test="deleteFlag != null">and delete_flag = #{deleteFlag}</if></trim></select>

结果如下：

Mybatis提供了<where>标签，所以可以使用<where>标签，xml内容如下：

 <select id="queryUserByCondition" resultType="com.example.zxslzw_mybaties.model.UserInfo">select * from userinfo<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>

可以看到，<where>标签代替了<trim>标签的prefix和prefixOverrides，运行刚才的测试类代码，运行结果如下：

注意：<where> 只会在子元素有内容的情况下才插入where子句，而且会自动去除子句开头（或结尾）的and或or。上面标签虽然可以使用<trim prefix="where" prefixOverrides="and">替换，但是此种情况下，当子元素都没有内容时，where 关键字也会保留。

4. <set> 标签

SQL语句：

update userinfo set username = ?, age = ?, delete_flag = ? where id = ?

UserInfoMapper接口代码：

@Mapper
public interface UserInfoMapper {Integer updateUserByCondition(UserInfo userInfo);
}

xml内容如下：（使用<trim>标签）

  <update id="updateUserByCondition">update userinfo<trim prefix="set" prefixOverrides=","><if test="username != null">username = #{username}</if><if test="age != null">,age = #{age}</if><if test="deleteFlag != null">,delete_flag = #{deleteFlag}</if></trim>where id = #{id}</update>

测试类代码：

  @Testvoid updateUserByCondition() {UserInfo userInfo = new UserInfo();userInfo.setId(5);userInfo.setUsername("wanger");userInfo.setAge(18);userInfo.setDeleteFlag(0);userInfoMapper.updateUserByCondition(userInfo);}

运行测试类，结果如下：

起初数据库表信息：

之后数据库表信息：

上面使用<trim>标签，针对update(改)可以使用<set>标签

改变xml内容：（使用<set>标签）

<update id="updateUserByCondition">update userinfo<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>

其中where关键字可以使用<where>标签，但是因为where后的id是肯定必须要有的，可以直接写上；

测试类代码如下：

 @Testvoid updateUserByCondition() {UserInfo userInfo = new UserInfo();userInfo.setId(1);userInfo.setUsername("zhongxia");userInfo.setAge(18);userInfo.setDeleteFlag(0);userInfoMapper.updateUserByCondition(userInfo);}

结果如下：

可以看到，<set>标签代替了<trim prefix="set" prefixOverrides=",">，说明动态SQL语句中插入set关键字，并且会删掉额外的逗号。（用于update语句中）

5. <foreach> 标签

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

1、collection：绑定方法参数中的集合，如 List、Set、Map或数组对象。

2、item：遍历时的每一个对象。

3、open：语句块开头的字符串。

4、close：语句块结束的字符串。

5、separator：每次遍历之间间隔的字符串。

原本SQL语句：：

delete from userinfo where id in (1, 2, 3, 4)

UserInfoMapper接口代码如下：

@Mapper
public interface UserInfoMapper {Integer batchDelete(List<Integer> ids);
}

xml内容如下：

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

测试代码如下：

 @Testvoid batchDelete() {List<Integer> ids = Arrays.asList(new Integer[]{1, 5});userInfoMapper.batchDelete(ids);}

结果如下：

我们不仅可以批量删除，也可以批量插入。

6. <include> 标签

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

我们可以对重复的片段进行抽取，将其通过<sql>标签封装到一个SQL片段，然后再通过<include>标签进行引用。

<sql>：定义可重用的SQL片段。

<include>：通过属性refid，指定包含的SQL片段。

 <sql id="allColumn">id, username, age, gender, phone, delete_falg, create_time, update_time</sql><select id="queryAllUser" resultType="com.example.mybatis.model.UserInfo">select <include refid="allColumn"></include>  from userinfo</select><select id="queryById" resultType="com.example.mybatis.model.UserInfo">select <include refid="allColumn"></include> from userinfo where id=#{id}</select>

通过<include>标签，去除掉了重复出现的代码。

ps：本文就写到这里了，如果对你有所帮助的话，就请一键三连哦！！！