mybatis 查询小结

来源：互联网发布：网络用语丫丫编辑：程序博客网时间：2024/06/14 06:58

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）大小写匹配查询

SELECT * FROM TABLENAME WHERE UPPER(SUBSYSTEM) LIKE '%' || UPPER('jz') || '%'
--或者是
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)

0 0