Mybatis skipping update the specific column if its parameter is null

Question

I HAVE A FOLLOWING UPDATE QUERY IN A MAPPER

<update id="updTest" parameterType="TestDTO">
    <![CDATA[
        UPDATE   TEST_USER

        SET      
              EMAIL          =  #{email, jdbcType=VARCHAR}
            , USERNAME       =  #{username, jdbcType=VARCHAR}
            , PASSWORD       =  #{password, jdbcType=VARCHAR}
            , DOMAIN         =  #{domain, jdbcType=VARCHAR}
            , COMPANY        =  #{company, jdbcType=VARCHAR}
            , DEPARTMENT     =  #{department, jdbcType=VARCHAR}
            , JOBTITLE       =  #{jobtitle, jdbcType=VARCHAR}
            , SIZE           =  #{size, jdbcType=INTEGER}
            , SEX            =  #{sex, jdbcType=VARCHAR}
            , BIRTH          =  #{birth, jdbcType=VARCHAR}
            , MOBILE         =  #{mobile, jdbcType=VARCHAR}
            , STATUS         =  #{status, jdbcType=VARCHAR}
            , AUTH_KEY       =  #{authKey, jdbcType=VARCHAR}
            , UPDATE_DATE    =  SYSDATE
            , UPDATE_ID      =  #{updateId, jdbcType=VARCHAR}
            , CREATE_DATE    =  SYSDATE
            , CREATE_ID      =  #{createId, jdbcType=VARCHAR}
            , DEL_YN         =  #{delYn, jdbcType=VARCHAR}
            , DEL_ID         =  #{delId, jdbcType=VARCHAR}

        WHERE   USERNAME= #{username, jdbcType=VARCHAR} 
        OR      EMAIL = #{email, jdbcType=VARCHAR}
        OR      AUTH_KEY = #{authKey, jdbcType=VARCHAR}
    ]]>
</update>

WHAT I WANT TO ACHIEVE is to skip updating a column if its parameter is null.

I have a form that users will fill in these below out.

• JOBTITLE
• SIZE
• MOBILE
• BIRTH
• SEX
• STATUS

This is additional information, which comes after a successful login. But when a user fills out the form and submits it, updating process works great.

But the exisiting EMAIL, USERNAME, PASSWORD and others are set to null except for those 6 fields in the form. it drives me crazy, I always thought that Mybatis would skip updating the field if its parameter is null. Is there any special configuration for it?

Answer 1

Your gripe is with SQL, not MyBatis. If your update statement includes say SET mobile = null, the SQL will do just that.

But MyBatis provides good dynamic SQL facilities with the if statement. See https://mybatis.github.io/mybatis-3/dynamic-sql.html. That should put you on the right track for a custom solution.

Also, consider changing your schema (if it is under your control). There should be a numeric userId field as primary key, allocated by a sequence or similar. Alternatively USERNAME is common key in such situations. Either will simplify your WHERE clause, and is pretty much standard practice. As it stands, EMAIL appears to be part of the primary key, so changing an email address will mean they become a different user.

And a last comment. I have never used CDATA in a MyBatis SQL query, but it probably does no harm.

<editFollowingComment>

I think you need to revisit your requirements, as some don't make a lot of sense.

• Your WHERE clause is really odd. If a family of six all share the same email address, do you really want to update all six records when one logs in? You need to decide on a unique primary key and USERNAME makes perfect sense.
• Having decided on USERNAME as the unique primary key for the table, you can delete the last two lines of the WHERE clause and also take USERNAME out of the SET list, as it can never change.
• How are you going to handle the case of an existing user that decides they are no longer going to own a mobile? It seems you do need to set MOBILE to null in this case.
• The AUTH_TOKEN field is a strange one to find in a database. Authorisation tokens are usually only valid for the duration of a login session - but I might have misunderstood your use case.

So getting back to your comment, I suggest that you write a MyBatis update that only updates the six fields on your form (named something like updateLoginFields), with a WHERE clause that specifies only USERNAME. You don't need to worry about nulls, as you can control what is optional in the presentaion layer.

</editFollowingComment>