动态SQL实现更新操作
在之前例子中,更新(Update)时需要传入所有参数,当某一参数为null时,会导致更新错误,如下图
注释掉某些参数:
未传参的结果为null了,显然不是我们想实现的
因此需要用到if set标签:
1、if+set标签
修改Mapper.xml文件:
<!-- 修改用户信息 -->
<update id="modify" parameterType="User">
update smbms_user
<set>
<if test="userCode != null">userCode=#{userCode},</if>
<if test="userName != null">userName=#{userName},</if>
<if test="userPassword != null">userPassword=#{userPassword},</if>
<if test="gender != null">gender=#{gender},</if>
<if test="userRole != null">userRole=#{userRole},</if>
<if test="modifyBy != null">modifyBy=#{modifyBy},</if>
<if test="modifyDate != null">modifyDate=#{modifyDate},</if>
<if test="address != null">address=#{address},</if>
<if test="phone != null">phone=#{phone},</if>
<if test="birthday != null">birthday=#{birthday},</if>
</set>
where id = #{id}
</update>
结果如下
查询数据库发现未修改的数据保持不变,实现了我们所需的需求。
2、if trim标签
使用if+ trim标签对if set进行替换:
<!-- 修改用户信息 -->
<update id="modify" parameterType="User">
update smbms_user
<trim prefix="set" suffixOverrides="," suffix="where id = #{id}">
<if test="userCode != null">userCode=#{userCode},</if>
<if test="userName != null">userName=#{userName},</if>
<if test="userPassword != null">userPassword=#{userPassword},</if>
<if test="gender != null">gender=#{gender},</if>
<if test="birthday != null">birthday=#{birthday},</if>
<if test="phone != null">phone=#{phone},</if>
<if test="address != null">address=#{address},</if>
<if test="userRole != null">userRole=#{userRole},</if>
<if test="modifyBy != null">modifyBy=#{modifyBy},</if>
<if test="modifyDate != null">modifyDate=#{modifyDate},</if>
</trim>
</update>
运行结果与if-set一致。