Mybatis+Oracle实现按条件分页查询代码,Java代码库,德仔网

代码语言

代码分类

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

作者:晶岩 / 发布于2017/2/10/ 912

<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!=""'>  
lt;![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;

试试其它关键字

同语言下

可能有用的

晶岩贡献的其它代码(7)