Mybatis+Oracle实现按条件分页查询

来源：互联网发布：dijkstra算法堆优化编辑：程序博客网时间：2024/06/02 09:39

原文地址：http://m.blog.csdn.net/article/details?id=50152795

<select id="selectTrackInfoListByPage" parameterType="com.niwopay.dto.manage.TrackInfoDto"resultMap="TrackInfoResultMap">select *from (select rownum rn, t.*from (select tti.TRACK_ID,tti.ORDER_ID,tti.TRACK_INFO,tti.TRACK_TIME,tti.OPERATORfrom TB_TRACK_INF tti<where><if test='orderId != null and orderId!=""'>tti.ORDER_ID like '%'||#{orderId}||'%'</if><if test='startTime != null and startTime!="" '>            <![CDATA[and to_date(tti.TRACK_TIME,'yyyy/mm/dd hh24:mi:ss') >=to_date(#{startTime},'yyyy/mm/dd hh24:mi:ss')]]></if><if test='endTime != null and endTime!="" '>            <![CDATA[and to_date(tti.TRACK_TIME,'yyyy/mm/dd hh24:mi:ss') <=to_date(#{endTime},'yyyy/mm/dd hh24:mi:ss')]]></if></where>order by tti.TRACK_ID desc, tti.ORDER_ID desc) t<if test='endIndex != null and endIndex!=""'><![CDATA[where rownum <=#{endIndex}]]></if>)<if test='startIndex != null and startIndex!=""'>where rn>=#{startIndex}</if></select><select id="selectTrackInfoListByPageCount" parameterType="com.niwopay.dto.manage.TrackInfoDto"resultType="java.lang.String">select count(*) from TB_TRACK_INF tti<where><if test='orderId != null and orderId!=""'>tti.ORDER_ID like '%'||#{orderId}||'%'</if><if test='startTime != null and startTime!="" '>            <![CDATA[and to_date(tti.TRACK_TIME,'yyyy/mm/dd hh24:mi:ss') >=to_date(#{startTime},'yyyy/mm/dd hh24:mi:ss')]]></if><if test='endTime != null and endTime!="" '>            <![CDATA[and to_date(tti.TRACK_TIME,'yyyy/mm/dd hh24:mi:ss') <=to_date(#{endTime},'yyyy/mm/dd hh24:mi:ss')]]></if></where>order by tti.TRACK_ID desc, tti.ORDER_ID desc</select>

其中

startInde=(page-1)*limit+1; //oracle是从1开始计数的
endIndex=page*limit;

0 0