mybits中的模糊查询和条件查询时条件为空时条件作废

SELECT
tpp.ID,
categoryInfo.`NAME` AS CATEGORY,
MAX(tppr.OPERATE_TIME) AS OPERATE_TIME,
tppr.REMARK,
tu.NICKNAME,
tu.PHONE,
tp.PHOTO_URL,
MAX(tp.CREATE_DATE) AS CREATE_DATE
FROM
t_p_product tpp
LEFT JOIN t_dictionary category ON category.ID = tpp.CATEGORY
LEFT JOIN t_dictionary_attach categoryInfo ON categoryInfo.NAME_CODE = category.NAME_CODE
AND categoryInfo.LANG_ID = #{LANG_ID}
LEFT JOIN t_dictionary KEYCODE ON KEYCODE.ID = tpp.KEY_CODE
LEFT JOIN t_dictionary_attach KEYCODEInfo ON KEYCODEInfo.NAME_CODE = KEYCODE.NAME_CODE
AND KEYCODEInfo.LANG_ID = #{LANG_ID}
LEFT JOIN t_dictionary DESTINATION ON DESTINATION.ID = tpp.DESTINATION
LEFT JOIN t_dictionary_attach DESTINATIONInfo ON DESTINATIONInfo.NAME_CODE = DESTINATION.NAME_CODE
AND DESTINATIONInfo.LANG_ID = #{LANG_ID}
LEFT JOIN t_p_product_record tppr ON tppr.ACTION = tpp.PRODUCT_STATE
AND tppr.PRODUCT_ID = tpp.ID
LEFT JOIN t_user tu ON tu.ID = tppr.OPERATE_USER
LEFT JOIN t_photo tp ON tp.ITEM_ID = tpp.ID
AND tp.ITEM_TYPE =#{ITEM_TYPE}
WHERE
tpp.IS_DEL = 0
AND tpp.PRODUCT_STATE = #{PRODUCT_STATE}
**<if test="DESTINATION != null and DESTINATION != ''">
    AND  DESTINATIONInfo.`NAME`=#{DESTINATION}
</if>
<if test="KEY_CODE != null and  KEY_CODE != ''">
    AND   KEYCODEInfo.`NAME` LIKE '%${KEY_CODE}%'
</if>**
GROUP BY tpp.ID
ORDER BY tpp.CREATE_DATE DESC
LIMIT #{begin},#{pageSize}

条件查询时条件为空时条件作废

<if test="DESTINATION != null and DESTINATION != ''">
    AND  DESTINATIONInfo.`NAME`=#{DESTINATION}
</if>
<if test="KEY_CODE != null and  KEY_CODE != ''">
    AND   KEYCODEInfo.`NAME` LIKE '%${KEY_CODE}%'
</if>

mybits中的模糊查询
1.用${…}代替#{…}

<if test="KEY_CODE != null and  KEY_CODE != ''">
    AND   KEYCODEInfo.`NAME` LIKE '%${KEY_CODE}%'
</if>
`注：使用${…}不能有效防止SQL注入，所以这种方式虽然简单但是不推荐使用
2.把’%#{name}%’改为”%”#{name}”%”

AND KEY_CODELIKE "%"#{KEY_CODE }"%" ``` 3.使用sql中的字符串拼接函数

<if test="KEY_CODE!= null and KEY_CODE!= ''">
     AND KEY_CODELIKE CONCAT(CONCAT('%',#{KEY_CODE},'%'))
  </if>