我们都知道mysql分页很简单,limit语句即可。
select * from student limit pageNo,pageSize
但是oracle分页有点特殊,下面给出两种oracle分页mapper文件的写法,功能和上面mysql分页相同,可以互相转化。
1.第一种 CDATA方式
<select id="selectAllByPage" resultMap="BaseResultMap" parameterType="map">
select * from (select TMP_PAGE.*,ROWNUM ROW_ID from (
select * from student
<if test="pageNo!=null and pageSize!=null">
<![CDATA[)TMP_PAGE) where ROW_ID <=${pageSize*pageNo} AND ROW_ID > ${pageSize*(pageNo-1)}]]>
</if>
</select>
2.第二种方式
<select id="selectAllByPage" resultMap="BaseResultMap" parameterType="map">
select * from
<choose>
<when test="pageNo!=null and pageSize!=null">
(select t1.*,rownum rn from
(select * from student
<where>
<if test="country!=null">
country=#{country,jdbc=VARCHAR}
</if>
</where>
)t1
where rownum<=(#{pageNo,jdbc=INTEGER}*#{pageSize,jdbc=INTEGER})
where rn>(#{pageNo,jdbc=INTEGER}-1)*#{pageSize,jdbc=INTEGER}
<when>
<otherwise>
student
</otherwish>
</choose>
</select>