a problem with the use of labels in mybatis and the use of labels

Welcome to visit my blog to see the original: Http://wangnan.tech

Today, a scene needs to write a query that says:

The user Object UserInfo contains the following fields:
UserName Phone email qqid weiboid WXID

Now the new registered user, passed over a register UserInfo object, now to the user in the database to verify the status Status=1 (means the active user), whether there is a user, as long as it has at least one of these fields with the newly registered object corresponding to the same field content, that is to say duplicate registration.

The meaning of translating into SQL statements is probably:
SELECT * FROM TableName where
(
Username= "XXX"
or phone = "xxx"
Or ...
)
and Status=1

That's what I wrote in the beginning, the code in MyBatis is this:

<select id= "selectbyselective" resulttype= "xxx". UserInfo ">
   select 
    <include refid=" Base_column_list "/> from
    uc_user 
    <where>
    ( <if test= "userName!= null" >
        user_name = #{username}
      </if>
      <if test= "email!= null" >
        or email = #{email}
      </if>
      <if test= "phone!= null" >
        or phone = #{phone}
      </if>
      <if test= "weiboid!= null" >
        or weibo_id = #{weiboid}
      </if>
      <if test= "Wxid!= null" & gt;
        or wx_id = #{wxid}
      </if>
      <if test= "qqid!= null" >
        or qq_id = #{qqid}
      </if>)
     </where> and
     status = 1
</select>

So the code looks like there's no problem but it's actually problematic. Why, then?
If the username is empty, a later field is not empty, and the last SQL language becomes this:

SELECT * from Uc_user where (or email = "xxx") and status = 1

Using MyBatis < where > tags is to prevent this, MyBatis will be in the first
UserName is empty, help us remove the first "or" of the following statement

But when I add the where tag (), the statement complains. Because automatically removing "or" will fail.

Check out the MyBatis Official document found another tab < trim > You can customize the trim element to tailor the features we want

The trim label surrounds the content to set several properties:
Prefix: prefix preceded by content
Suffix: suffix appended to content
Prefixoverrides: Property ignores a pipe-delimited sequence of text (note that the spaces in this example are also necessary, and multiple ignore sequences with "|" Separated). The result is that all the content specified in the Prefixoverrides attribute will be removed.

So my revised code is:

<select id= "selectbyselective" resulttype= "xxx". UserInfo ">
   select 
    <include refid=" Base_column_list "/> from Uc_user <trim-prefix= 
    " WHERE ("  suffix=") "prefixoverrides=" and |or "> 
    <if test=" userName!= null ">
        user_name = #{ UserName}
      </if>
      <if test= "email!= null" >
        or email = #{email}
      </if>
      <if test= "phone!= null" >
        or phone = #{phone}
      </if>
      <if test= "weiboid!= null" >
        or Weibo _id = #{weiboid}
      </if>
      <if test= "Wxid!= null" >
        or wx_id = #{wxid}
      </if>
      <if test= "qqid!= null" >
        or qq_id = #{qqid}
      </if>  
     </trim> and
     status = 1
</select>

OK, it's done.