11.14工作总结（ Mybatis中sql条件查询）

1. Mybatis中sql条件查询

（1）多条件使用<where>和<if>标签
注意：也可以单独前面是where 后面跟<if>标签，但用双判断时容易报错，建议<where>和<if>集合使用
(2) 可以使用双<if>嵌套判断
mybatis里mapper.xml中SQL语句if语句嵌套if语句
（3）Mybatis中的sql语句中的 “<” 和 “>” 号要用转义字符 “<” 和 ”>“ ，否则会报错！

原符号	  <	   <=	   >	   >=	    &	     '	      "
替换符号	&lt;	&lt;=	&gt;	&gt;=	&amp;	&apos;	&quot;

（4） 当数据库中时间字段是varchar类型，想要比较，mysql中需要将字段进行转换：使用函数：STR_TO_DATE(str, format)
mysql中如何操作varchar类型的日期进行比较、排序等操作

SELECT id,nickname,createtime 
FROM member
WHERE str_to_date(createtime, '%Y-%m-%d') BETWEEN str_to_date('2018-08-01', '%Y-%m-%d') AND str_to_date('2018-08-31', '%Y-%m-%d')
ORDER BY str_to_date(createtime, '%Y-%m-%d')

下面代码包含分页

<select id="pageList" resultMap="userappMap">
  		select * from(
  			SELECT *  FROM fx_userapp rw 
  		<where>	
  		     1 =1      	    	    	
			<if test = " username != null and username !='' and username !='null' ">
	   	 		and rw.username = #{username} 
	   	 	</if>
			<if test = " idcardno != null and idcardno !='' and idcardno !='null' ">
	   	 		and rw.idcardno = #{idcardno} 
	   	 	</if>
			<if test = " sex != null and sex !='' and sex !='null' ">
	   	 		and rw.sex = #{sex} 
	   	 	</if>
			<if test = " start_time != null and start_time !='' and start_time !='null' and end_time != null and end_time !='' and end_time !='null'">
	   	 		and str_to_date(rw.userbirth,'%Y-%m-%d') BETWEEN str_to_date(#{start_time},'%Y-%m-%d') and str_to_date(#{end_time},'%Y-%m-%d')
	   	 	</if>
	   	 	<if test = "start_time == null or start_time=='' or start_time=='null'"> 	 	
	   	 		<if test = " end_time != null and end_time !='' and end_time !='null' ">
	   	 		      and str_to_date(rw.userbirth,'%Y-%m-%d') &lt;=str_to_date(#{end_time},'%Y-%m-%d')
	   	 		</if>
	   	 	</if> 
	   	 	<if test = "end_time == null or end_time=='' or end_time=='null'"> 	 	
	   	 		<if test = " start_time != null and start_time !='' and start_time !='null' ">
	   	 		      and str_to_date(rw.userbirth,'%Y-%m-%d') &gt;str_to_date(#{start_time},'%Y-%m-%d')
	   	 		</if>
	   	 	</if> 
   	 	</where>
  		) PAGE LIMIT ${pageno},${pagesize}
  	</select>