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

来源：互联网发布：靠谱助手网络异常编辑：程序博客网时间：2024/06/02 09:38

<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.OPERATOR
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
) 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 1