MyBatis extra comma in update query

Question

In the Mybatis xml mapper file, I tried to write update query for User table as shown below. Each of the input parameter could be null and I only update when it isn't null. You don't know which 'if' condition could fall through and which one could be the last one, thus comma has to be added in each statement.

The problem is that the extra ',' causes query exception. It seems Mybatis doesn't ignore extra comma.

My workaround is to put "id = #{id}" at the end which fixed the problem but it is redundant.

What is the real solution?

The code:

<update id="update" parameterType="User">
    UPDATE user SET

    <if test="username != null">
        username = #{username},
    </if>
    <if test="password != null">
        password = #{password},
    </if>
    <if test="email != null">
        email = #{email},
    </if>
    id= #{id} // this is redundant

    WHERE id = #{id}
</update>

PS: The environment I am using is: Java Spring + MyBatis + MySQL.

Answer 1

Thanks to MyBatis Generator's mapper.xml files, I've learnt how to suppress the commas. MyBatis has a tag <set> that erases the last comma. It's also written in MyBatis - Dynamic Sql:

Here, the set element will dynamically prepend the SET keyword, and also eliminate any extraneous commas that might trail the value assignments after the conditions are applied.

You can write it as:

<update id="update" parameterType="User">
    UPDATE user
    <set>
        <if test="username != null">
            username = #{username},
        </if>
        <if test="password != null">
            password = #{password},
        </if>
        <if test="email != null">
            email = #{email},
        </if>
    </set>
    WHERE id = #{id}
</update>