1.场景还原
在实际项目中,有很多需求需要通过设定一个具体的时间段来搜索或过滤所需的数据,今天笔者就mybatis中时间比较涉及到的大于,小于号的应用方法作个详尽的讲解。
2.实现方案
以下介绍两种可行方法:
①转义法
大于:>
小于:<
大于等于:>=
小于等于:<=
笔者案例:
<select id="view" parameterType="map" resultMap="BaseResultMap"> SELECT * FROM task t,staff s,product p WHERE t.staff_id = s.id AND t.product_id = p.id <if test="companyId != null "> AND t.company_id = #{companyId} </if> <if test="workshopId != null"> AND t.workshop_id = #{workshopId} </if> <if test="opunitId != null"> AND t.opunit_id = #{opunitshopId} </if> <if test="processId != null"> AND t.process_id = #{processId} </if> <if test="@Ognl@isNotEmpty(equipmentId)"> AND t.equipment_id = #{equipmentId} </if> <if test="dateStart != null and dateStart !='' "> AND UNIX_TIMESTAMP(t.date_work) >= UNIX_TIMESTAMP(#{dateStart}) </if> <if test="dateEnd != null and dateEnd !='' "> AND UNIX_TIMESTAMP(t.date_work) <= UNIX_TIMESTAMP(#{dateEnd}) </if> GROUP BY t.order_no ORDER BY t.date_work </select>运行效果:
注意:这里的 日期入参类型为String
②<![CDATA[ sql语句 ]]>
<![CDATA[ sql语句 ]]>中的<![CDATA[ ]]>在mybatis中自动注释
笔者案例:
<select id="selectByTime" resultType="Date" parameterType="map"> SELECT r.stop_time FROM rtg r <![CDATA[ WHERE UNIX_TIMESTAMP(r.stop_time) >= UNIX_TIMESTAMP(#{startTime}) AND UNIX_TIMESTAMP(r.stop_time) <= UNIX_TIMESTAMP(#{endTime}) ]]> </select>运行效果:
ok,以上全是笔者实际需求提炼的心得,望能够帮助更多的伙伴