---

T04BF
👋专栏: 算法|JAVA|MySQL|Spring
🫵 与天斗其乐无穷

---

---

1. 动态SQL

动态sql能够实现不同条件下的sql拼接

标签

我们在新增数据的时候,有时候要指定列赋值

INSERT INTO userinfo (username,`password`) VALUES (?,?)INSERT INTO userinfo (username,`password`) VALUES (?,?,?)

大多数情况需要我们根据参数的个数完善sql语句
这时候就要使用动态标签来判断了:

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

测试用例:
测试当gender为空:

@Test
void insertUser() {UserInfo userInfo = new UserInfo();userInfo.setUsername("wangba");userInfo.setPassword("123456");userInfo.setAge(18);userInfo.setPhone("123456789");System.out.println(userInfoMapper.insertUser(userInfo));
}

但是实际上,单纯这样写会出现问题:
当我们的phone为空时:

发现会多一个逗号,导致sql语句出错
此时就要使用标签

标签

<trim>标签主要有4个属性:

• prefix:表示被<trim>括起来的整个语句,以prefix值作为前缀
• suffix:表示被<trim>括起来的整个语句,以suffix值作为后缀
• prefixOverrides:表示被<trim>括起来的整个语句,去除prefixOverrides前缀
• suffixOverrides:表示被<trim>括起来的整个语句,去除suffixOverrides前缀

此时就能就解决上面的问题:

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

标签

<where>只会在子元素有内容的情况下才会插入where字句,并且会自动去除字句开头的and或or

    <select id="selectUserList" resultMap="XMLBaseMap">select * from userinfo<where><if test="age != null">and age = #{age}</if><if test="gender != null">and gender = #{gender}</if><if test="phone != null">and phone = #{phone}</if></where></sel

标签

<set> 标签会动态的在sql语句里面插入set关键字,并且会删除额外的逗号

<update id="updateUser" keyProperty="id" useGeneratedKeys="true">update userinfo<set><if test="username != null">username = #{username},</if><if test="password != null">password = #{password},</if><if test="age != null">age = #{age},</if><if test="phone != null">phone = #{phone},</if><if test="gender != null">gender = #{gender},</if></set>where id = #{id}
</update>

标签

对集合进行遍历时可以使用foreach 标签.有以下属性:

• collection:绑定方法参数中的集合
• item:遍历的每一个对象
• open:语句块开头的字符串
• close:语句块结束的字符串
• separator:每次遍历之间间隔的字符串

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

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

标签

在xml映射文件中配置的sql,可能会存在很多重复的片段,此时就会存在很多冗余的代码

可以对重复的代码片段进行抽取,将其通过<sql> 标签封装到一个sql片段.再通过<include>标签进行引用

• sql 定义可重复的sql片段
• <include> 通过属性refid,指定包含的sql片段

<sql id="allColumn">select * from userinfo
</sql><select id="queryAllUser" resultMap="XMLBaseMap"><include refid="allColumn"></include>
</select>

注解方式

注解方式动态sql比较麻烦,可读性不太好,不推荐使用

@Insert("<script>" +
"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>"+
"</script>")
Integer insertUserByCondition(UserInfo userInfo);

---