八月【sql】

今天遇到一个bug

原代码

    <update id="updateUserInfo" parameterType="java.util.Map">
        UPDATE t_fsans_record T
        SET T.bind_status = #{binding},
        T.bind_time = sysdate(),
        T.phone = #{phone},
        T.bind_form = #{bind_form},
        <if test="referee_phone !=null referee_phone and !=''">
            T.referee_phone = #{referee_phone},
        </if>
        <if test="county !=null and county !=''">
            T.county = #{county}
        </if>
        WHERE T.openid=#{openid} and T.city=#{cityCode}
        and ( T.bind_status <![CDATA[<>]]> 'binding' or T.bind_status is null)
    </update>

如果 if 标签为空，就不会插入，所以

T.bind_form = #{bind_form},

就会多出一个逗号，所以sql执行不通过

修改

    <update id="updateUserInfo" parameterType="java.util.Map">
        UPDATE t_fsans_record T
        SET T.bind_status = #{binding},
        T.bind_time = sysdate(),
        T.phone = #{phone},
        T.bind_form = #{bind_form},
        T.referee_phone = #{referee_phone},
        T.county = #{county}
        
        WHERE T.openid=#{openid} and T.city=#{cityCode}
        and ( T.bind_status <![CDATA[<>]]> 'binding' or T.bind_status is null)
    </update>

有 标签就可以

• 标签能够自动去掉最后一个逗号

    <update id="updateAuthor" parameterType="com.xwtec.sucai.bean.AuthorBean">
		update t_author
		<set>
			<if test="password!=null">
				password=#{password},
			</if>
			<if test="sex!=null">
				sex=#{sex},
			</if>
			<if test="professionalName!=null">
				professionalName=#{professionalName},
			</if>
		</set>
		<where>
			authorId= #{authorId}
		</where>
    </update>

• 或者

 <update id="updateUserInfo" parameterType="java.util.Map">
        UPDATE t_fsans_record T
        SET T.bind_status = #{binding},
        T.bind_time = sysdate(),
        T.phone = #{phone},
        T.bind_form = #{bind_form}
        <if test="referee_phone !=null referee_phone and !=''">
            ,T.referee_phone = #{referee_phone}
        </if>
        <if test="county !=null and county !=''">
            ,T.county = #{county}
        </if>
        WHERE T.openid=#{openid} and T.city=#{cityCode}
        and ( T.bind_status <![CDATA[<>]]> 'binding' or T.bind_status is null)
    </update>

将

T.bind_form = #{bind_form}

后面的逗号去掉,放到标签下。

好TM坑啊！！！！！！