【mybatis】mybatis查询发生条件传入值但是查询并没有这个条件的查询,Integer类型查询条件需要注意事项...

有下面这样一个查询：

下面标紫色的查询条件，type的类型为Integer

<select
            id="findDealerInfo"
            parameterType="com.pisen.cloud.luna.ms.dealer.api.beans.DealerInfoBean"
            resultType="com.pisen.cloud.luna.ms.dealer.api.beans.DealerInfoBean">


        SELECT
        dea.uid uid,
        dea.enabled_flag enabledFlag,
        dea.delete_flag deleteFlag,
        dea.tenement_id tenementId,
        dea.parent_id parentId,
        pd.name parentName,
        dea.name name,
        dea.type type,
        dea.bar_code barCode,
        dea.outer_code outerCode,
        dea.outer_id outerId,
        dea.mne_code  mneCode,
        dea.address address,
        dea.address_xy addressXy,
        dea.business_area businessArea,
        dea.business_area_xy businessAreaXy,
        con.uid cantactUid,
        con.name  contactName,
        con.mobile mobile,
        dpo.uid depotUid,
        dpo.name depotName,
        dpo.address depotAddress,
        dpo.depot_code depotCode,
        dpo.ship_scope shipScope

        FROM
        dealer AS dea
        LEFT JOIN  (SELECT a.* FROM contact AS a where tenement_id = #{tenementId}  and main_contact = ${@com.pisen.cloud.luna.ms.dealer.base.domain.Contact@IS_MAIN} AND a.delete_flag = ${@com.pisen.cloud.luna.ms.dealer.base.common.BaseDomain@DELETE_FLAG_NO} AND a.enabled_flag = ${@com.pisen.cloud.luna.ms.dealer.base.common.BaseDomain@ENABLED_FLAG_EN}) con ON dea.uid = con.dealer_id
        LEFT JOIN  (SELECT b.* FROM depot AS b where  b.delete_flag = ${@com.pisen.cloud.luna.ms.dealer.base.common.BaseDomain@DELETE_FLAG_NO} AND b.enabled_flag = ${@com.pisen.cloud.luna.ms.dealer.base.common.BaseDomain@ENABLED_FLAG_EN}) dpo on dpo.dealer_id = dea.uid
        LEFT JOIN  dealer pd on pd.uid = dea.parent_id

        WHERE
        dea.tenement_id = #{tenementId}
        AND
        dea.delete_flag = ${@com.pisen.cloud.luna.ms.dealer.base.common.BaseDomain@DELETE_FLAG_NO}

        <if test="name != null and name != '' ">
            AND
            dea.name LIKE '%' #{name} '%'
        </if>
        <if test="type != null and type != '' ">
            AND
            dea.type = #{type}
        </if>
        <if test="outerCode != null and outerCode != '' ">
            AND
            dea.outer_code = #{outerCode}
        </if>
        <if test="mneCode != null and mneCode != '' ">
            AND
            dea.mne_code = #{mneCode}
        </if>
        <if test="address != null and address != '' ">
            AND
            dea.address LIKE '%' #{address} '%'
        </if>
        <if test="businessArea != null and businessArea != '' ">
            AND
            dea.business_area LIKE '%' #{businessArea} '%'
        </if>
        <if test="parentName != null and parentName != '' ">
            AND
            pd.name LIKE '%' #{parentName} '%'
        </if>
        <if test="contactName != null and contactName != '' ">
            AND
            con.name LIKE '%' #{contactName} '%'
        </if>
        <if test="mobile != null and mobile != '' ">
            AND
            con.mobile = #{mobile}
        </if>
        
        
       
order by dea.parent_id ,dea.create_date
            
        
    </select>

 

后来经过排查，真实的原因是因为：

　　integer类型的查询条件在判断是否为null的时候，只需要判断 !=null即可，否则本判断条件会出现问题，正确的写法如下：

<select
            id="findDealerInfo"
            parameterType="com.pisen.cloud.luna.ms.dealer.api.beans.DealerInfoBean"
            resultType="com.pisen.cloud.luna.ms.dealer.api.beans.DealerInfoBean">


        SELECT
        dea.uid uid,
        dea.enabled_flag enabledFlag,
        dea.delete_flag deleteFlag,
        dea.tenement_id tenementId,
        dea.parent_id parentId,
        pd.name parentName,
        dea.name name,
        dea.type type,
        dea.bar_code barCode,
        dea.outer_code outerCode,
        dea.outer_id outerId,
        dea.mne_code  mneCode,
        dea.address address,
        dea.address_xy addressXy,
        dea.business_area businessArea,
        dea.business_area_xy businessAreaXy,
        con.uid cantactUid,
        con.name  contactName,
        con.mobile mobile,
        dpo.uid depotUid,
        dpo.name depotName,
        dpo.address depotAddress,
        dpo.depot_code depotCode,
        dpo.ship_scope shipScope

        FROM
        dealer AS dea
        LEFT JOIN  (SELECT a.* FROM contact AS a where tenement_id = #{tenementId}  and main_contact = ${@com.pisen.cloud.luna.ms.dealer.base.domain.Contact@IS_MAIN} AND a.delete_flag = ${@com.pisen.cloud.luna.ms.dealer.base.common.BaseDomain@DELETE_FLAG_NO} AND a.enabled_flag = ${@com.pisen.cloud.luna.ms.dealer.base.common.BaseDomain@ENABLED_FLAG_EN}) con ON dea.uid = con.dealer_id
        LEFT JOIN  (SELECT b.* FROM depot AS b where  b.delete_flag = ${@com.pisen.cloud.luna.ms.dealer.base.common.BaseDomain@DELETE_FLAG_NO} AND b.enabled_flag = ${@com.pisen.cloud.luna.ms.dealer.base.common.BaseDomain@ENABLED_FLAG_EN}) dpo on dpo.dealer_id = dea.uid
        LEFT JOIN  dealer pd on pd.uid = dea.parent_id

        WHERE
        dea.tenement_id = #{tenementId}
        AND
        dea.delete_flag = ${@com.pisen.cloud.luna.ms.dealer.base.common.BaseDomain@DELETE_FLAG_NO}

        <if test="name != null and name != '' ">
            AND
            dea.name LIKE '%' #{name} '%'
        </if>
        <if test="type != null ">
            AND
            dea.type = #{type}
        </if>
        <if test="outerCode != null and outerCode != '' ">
            AND
            dea.outer_code = #{outerCode}
        </if>
        <if test="mneCode != null and mneCode != '' ">
            AND
            dea.mne_code = #{mneCode}
        </if>
        <if test="address != null and address != '' ">
            AND
            dea.address LIKE '%' #{address} '%'
        </if>
        <if test="businessArea != null and businessArea != '' ">
            AND
            dea.business_area LIKE '%' #{businessArea} '%'
        </if>
        <if test="parentName != null and parentName != '' ">
            AND
            pd.name LIKE '%' #{parentName} '%'
        </if>
        <if test="contactName != null and contactName != '' ">
            AND
            con.name LIKE '%' #{contactName} '%'
        </if>
        <if test="mobile != null and mobile != '' ">
            AND
            con.mobile = #{mobile}
        </if>
        
        
       
order by dea.parent_id ,dea.create_date
            
        
    </select>