mybatis 查询小结

mybatis 查询

一、模糊查询：

（1）sql字符串拼接

select * from t_user where name like  CONCAT(CONCAT('%',#{name},'%'))

（2）使用${...}代替#{...}

select * from t_user where name like '%'${name}'%'

（3）程序中拼接

String name = "%"+name+"%";

（4）大小写匹配查询

1. SELECT *  FROM TABLENAME  WHERE UPPER(SUBSYSTEM) LIKE '%' || UPPER('jz') || '%'  
2.  --或者是  
3. SELECT *   FROM TABLENAME  WHERE LOWER(SUBSYSTEM) LIKE '%' || LOWER('jz') || '%'

二、日期查询

<select id="showUser" parameterType="java.util.Map" resultType="User">
		select * from t_user where 1=1  
		<if test="uname!=null and unmae!='' " >
			and uname like '%${uname}%'
		</if>
		<if test="searchCreateTimeStart !=null and searchCreateTimeStart !=''">
			<![CDATA[ and createTime >=#{searchCreateTimeStart} ]]>
		</if>
		<if test="searchCreateTimeEnd !=null and searchCreateTimeEnd !=''">
			<![CDATA[ and createTime <=#{searchCreateTimeEnd} ]]>
		</if>
		<if test="searchModifyTimeStart !=null and searchModifyTimeStart !=''">
			<![CDATA[ and ModifyTime >=#{searchModifyTimeStart} ]]>
		</if>
		<if test="searchModifyTimeEnd !=null and searchModifyTimeEnd !=''">
			<![CDATA[ and modifyTime <=#{searchModifyTimeEnd} ]]>
		</if>
		<if test="sort!=null and order!=null">
			order by ${sort}  ${order}
		</if>
		limit #{start} , #{rows}
	</select>

模糊查询部分转载自( http://blog.csdn.net/luqin1988/article/details/7865643)