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);