MyBatis操作数据库（动态SQL）

1 动态SQL

动态SQL是MyBatis的特征之一，能够完成不同条件下不同的SQL拼接

1.1 <if>标签

在注册用户的时候，可能会有这样一个问题，由于注册分为两种字段：必填字段和非必填字段，如果在添加用户的时候有不确定的字段传入，程序应该如何实现，此时就需要用到动态标签来判断了

例如添加的时候性别gender为非必填字段

@Mapper
public interface UserInfoXMLMapper {Integer insertByXML(UserInfo userInfo);
}

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

@Slf4j
@SpringBootTest
class UserInfoXMLMapperTest {@Autowiredprivate UserInfoXMLMapper userInfoXMLMapper;@Testvoid insertByXML() {UserInfo userInfo = new UserInfo();userInfo.setUsername("222");userInfo.setPassword("222");userInfo.setAge(10);userInfo.setGender(1);userInfo.setPhone("123456");Integer integer = userInfoXMLMapper.insertByXML(userInfo);log.info(integer.toString());}
}

首先观察填写gender的情况

@Slf4j
@SpringBootTest
class UserInfoXMLMapperTest {@Autowiredprivate UserInfoXMLMapper userInfoXMLMapper;@Testvoid insertByXML() {UserInfo userInfo = new UserInfo();userInfo.setUsername("222");userInfo.setPassword("222");userInfo.setAge(10);//userInfo.setGender(1);userInfo.setPhone("123456");Integer integer = userInfoXMLMapper.insertByXML(userInfo);log.info(integer.toString());}
}

没有填写gender的情况，此时gender这一选项就没有被拼接

1.2 <trim>标签 

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

标签中有如下属性：

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

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

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

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

<insert id="insertByXML">insert into userinfo<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>

上述代码在整个语句前加'（'，在整个语句后加'）'，去掉'，'的后缀

@Slf4j
@SpringBootTest
class UserInfoXMLMapperTest {@Autowiredprivate UserInfoXMLMapper userInfoXMLMapper;@Testvoid insertByXML() {UserInfo userInfo = new UserInfo();userInfo.setUsername("222");userInfo.setPassword("222");userInfo.setAge(10);//userInfo.setGender(1);//userInfo.setPhone("123456");Integer integer = userInfoXMLMapper.insertByXML(userInfo);log.info(integer.toString());}
}

让性别和电话号码两项编程非必填项，再次观察结果

可以看出，左右括号都添加上了，并且去掉了最后面的'，' 

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

1）基于prefix配置，开始部分加上 （

2）基于suffix配置，结尾部分加上   ）

3）多个组织语句都以','结尾，在最后拼接好的字符串还会以','结尾的，会基于suffixOverrides配置去掉最后一个','

1.3 <where>标签 

在以下场景，系统会根据我们筛选的条件，动态的组装where条件

List<UserInfo> selectByXML(UserInfo userInfo);

<select id="selectByXML" resultType="com.example.demo.UserInfo">select * from userinfo<where><if test="username != null">username = #{username}</if><if test="age != null">and age = #{age}</if><if test="gender != null">and gender = #{gender}</if></where>
</select>

@Test
void selectByXML() {UserInfo userInfo = new UserInfo();userInfo.setUsername("222");userInfo.setAge(10);userInfo.setGender(1);List<UserInfo> userInfoList = userInfoXMLMapper.selectByXML(userInfo);log.info(userInfoList.toString());
}

去掉username和gender再次观察 

@Test
void selectByXML() {UserInfo userInfo = new UserInfo();//userInfo.setUsername("222");userInfo.setAge(10);//userInfo.setGender(1);List<UserInfo> userInfoList = userInfoXMLMapper.selectByXML(userInfo);log.info(userInfoList.toString());
}

可以看到，<where>自动帮我们去除了开头的and， 当查询的条件都为空时，<where>标签还会自动去掉where关键字

以上标签也可以使用 <trim prefix="where" prefixOverrides="and"> 替换，但是有些情况下，当子元素都没有内容时，where关键字也会保留

1.4 <set>标签

根据传入的用户对象属性来更新用户数据，可以使用标签指定动态内容

使用trim标签

Integer updateByXML(UserInfo userInfo);

<update id="updateByXML">update userinfo set<trim prefix="set" suffixOverrides=","><if test="username != null">username = #{username},</if><if test="age != null">age = #{age},</if><if test="gender != null">gender = #{gender},</if></trim>where id = 14
</update>

@Test
void updateByXML() {UserInfo userInfo = new UserInfo();userInfo.setUsername("456");userInfo.setAge(4);userInfo.setGender(2);Integer integer = userInfoXMLMapper.updateByXML(userInfo);log.info(integer.toString());
}

将username和gender去掉，再观察 

使用<set>标签 

@Test
void updateByXML() {UserInfo userInfo = new UserInfo();//userInfo.setUsername("456");userInfo.setAge(4);//userInfo.setGender(2);Integer integer = userInfoXMLMapper.updateByXML(userInfo);log.info(integer.toString());
}

<update id="updateByXML">update userinfo<set><if test="username != null">username = #{username},</if><if test="age != null">age = #{age},</if><if test="gender != null">gender = #{gender},</if></set>where id = 14
</update>

使用<set>标签可以再动态SQL语句中插入set关键字，并且会删除额外的逗号（用于update语句中）

1.5 <foreach>标签

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

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

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

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

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

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

Integer deleteByIds(List<Integer> ids);

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

@Test
void deleteByIds() {Integer integer = userInfoXMLMapper.deleteByIds(Arrays.asList(12, 13, 14, 15));log.info(integer.toString());
}

1.6 <include>标签 

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

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

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

2）<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 userinfo
</select>
<select id="queryById" resultType="com.example.demo.model.UserInfo">select<include refid="allColumn"></include>from userinfo where id= #{id}
</select>