mybatis Dynamic sql if test Instructions

The parameter is String, and if test reads the parameter code

<select id="getMaxDepartId" parameterType="java.lang.String" resultType="java.lang.String">
    SELECT MAX(DEPART_ID) FROM T_P_DEPART 
    <where>
      <if test="_parameter!=null and _parameter!=''"> 
        AND DEPART_PID = #{departId,jdbcType=VARCHAR} 
      </if>
      <if test="_parameter==null or _parameter==''"> 
        AND DEPART_PID IS NULL
      </if>
    </where>
  </select>

The parameter is pojo, and if test reads the parameter code

<select id="findShopByName" parameterType="ShopVo" resultType="ShopCustomer">
  select * from shop 
  <where>
      <if test="shopCustomer.shopname!=null and shopCustomer.shopname!=''">
        shop.shopname like '%${shopCustomer.shopname}%'
      </if>
      <if test="shopCustomer.shopname==null or shopCustomer.shopname==''"> 
        AND shop.shopname is null
      </if>
  </where>
</select>

Add: How to write the conditions of if test in mybatis

1. if test writing in mybatis

1.1 This is what the official document says about if

<if test="title != null">
  AND title like #{title}
</if>

Refer to official documents:

The actual project will have this kind of situation: The title field on the page input a certain value to query, manually delete the value in the input box, and then query again, found that the result is incorrect, the reason is that the title passed in an empty string "", so in the mybatis configuration file will use an empty string to query, resulting in wrong results

1.2 Suggested writing

<if test="title != null and title != ''" >
  AND title like #{title}
</if>

2. Leave fields blank when making modifications using mybatis

If the parameter passed in if is null, the statement in if will not be executed

Solution:

<update id="updateObject" parameterType="*.*.Object" >
update table
 <set>
  <if test="Object.fullName == null or Object.fullName ==''">
  full_name = null,
  </if>
  <if test="Object.fullName != null and Object.fullName !=''">
  full_name = #{companyOrg.fullName},
  </if>
  <if test="Object.level == null or Object.level ==''">
  level = null,
  </if>
  <if test="Object.level == 0 ">
  level = null,
  </if>
  <if test="Object.level != null and Object.level !='' and Object.level != 0 ">
  level = #{companyOrg.level},
  </if>
 
 </set>
 where 1=1 and id =#{companyOrg.id}
</update>