SSM（八）使用动态SQL实现更新操作

一、学习目标

掌握使用 if + set 完成更新操作

掌握使用 if + trim 完成更新操作

二、if + set

更新用户表数据时，若某个参数为null，会导致更新错误。

原因：SQL语句

处理：if + set

UserMapper.java

	public int modifyDSQLM(User user);

UserMapper.xml

<update id="modifyDSQLM">
		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="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>
		</set>
		where id={id}
	</update>

UserMapperTest.java

	@Test
	public void testGetUserListDSQLM() {
		SqlSession sqlSession = null;
		int count = 0;
		try {
			User user = new User();
			user.setId(13);
			user.setUserCode("_MMMM");
			user.setUserName(new String("测试用户001_M".getBytes("UTF-8"),"UTF-8"));
			user.setAddress("测试地址_M");
			user.setModifyBy(1);
			user.setModifyDate(new Date());
			sqlSession = MyBatisUtil.createSqlSession();
			count = sqlSession.getMapper(UserMapper.class).modifyDSQLM(user);
			sqlSession.commit();
		} catch(Exception e) {
			e.printStackTrace();
			sqlSession.rollback();
			count = 0;
		} finally {
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("modify count = " + count);
	}

三、if + trim

<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>
    </trim>
</update>

四、在实际项目中，系统设计需要全部字段的更新操作，所以if + set很少使用。