MyBatis SQLServer 模糊查询LIKE
方法一:在SQL中拼接字符串
SELECT ID, MESSAGE_ID,MESSAGE_RESULT ,CREATE_TIME,MESSAGE_STATUS, FINISH_TIMEFROM USER_MESSAGE
WHERE 1 = 1
<if test="messageStatus != null and messageStatus != '' " >
AND MESSAGE_STATUS = #{messageStatus}
</if>
<if test="messageResult != null and messageResult != '' " >
AND MESSAGE_RESULT LIKE '%'+#{messageResult}+'%'
</if>
<choose>
<when test="finishTimeType==4"> <!-- 本年-->
AND DATEDIFF(YEAR,FINISH_TIME,GETDATE())=0
</when>
<when test="finishTimeType==3"> <!-- 近3月-->
AND FINISH_TIME >= DATEADD(MONTH,-3,GETDATE())
</when>
<when test="finishTimeType==2"><!-- 近2月-->
AND FINISH_TIME >= DATEADD(MONTH,-2,GETDATE())
</when>
<when test="finishTimeType==1"><!-- 本月-->
AND DATEDIFF(MONTH, FINISH_TIME, GETDATE())= 0
</when>
<otherwise><!-- 全部-->
AND FINISH_TIME IS NOT NULL
</otherwise>
</choose>
</select>
方法二:在Java代码中拼接字符串
Map<String,Object> paramMap=new HashMap<String,Object>();
paramMap.put("messageStatus", messageStatus);
paramMap.put("messageStatus", "%"+messageStatus+"%");
注意:使用${…}不能有效防止SQL注入,所以这种方式虽然简单但不推荐。
paramMap.put("messageStatus", messageStatus);
paramMap.put("messageStatus", "%"+messageStatus+"%");