使用rownum对oracle分页【原】 编辑编辑编辑编辑编辑
以Student表为例进行分页
建表及插入
-- 有表结构如下 create table STUDENT ( sno INTEGER, sname VARCHAR2(100), sage INTEGER ); -- 插入数据 insert into student (SNO, SNAME, SAGE) values (19, 'Bob', 39); insert into student (SNO, SNAME, SAGE) values (15, 'Bob', 35); insert into student (SNO, SNAME, SAGE) values (3, 'Bob', 23); insert into student (SNO, SNAME, SAGE) values (11, 'Bob', 31); insert into student (SNO, SNAME, SAGE) values (7, 'Bob', 27); insert into student (SNO, SNAME, SAGE) values (6, 'Kite', 26); insert into student (SNO, SNAME, SAGE) values (2, 'Kite', 22); insert into student (SNO, SNAME, SAGE) values (18, 'Kite', 38); insert into student (SNO, SNAME, SAGE) values (14, 'Kite', 34); insert into student (SNO, SNAME, SAGE) values (10, 'Kite', 30); insert into student (SNO, SNAME, SAGE) values (4, 'Mike', 24); insert into student (SNO, SNAME, SAGE) values (12, 'Mike', 32); insert into student (SNO, SNAME, SAGE) values (16, 'Mike', 36); insert into student (SNO, SNAME, SAGE) values (8, 'Mike', 28); insert into student (SNO, SNAME, SAGE) values (20, 'Mike', 40); insert into student (SNO, SNAME, SAGE) values (5, 'T&%T', 25); insert into student (SNO, SNAME, SAGE) values (17, 'T&%T', 37); insert into student (SNO, SNAME, SAGE) values (9, 'T&%T', 29); insert into student (SNO, SNAME, SAGE) values (21, 'T&%T', 41); insert into student (SNO, SNAME, SAGE) values (1, 'T&%T', 21); insert into student (SNO, SNAME, SAGE) values (13, 'T&%T', 33);
表内容
纯sql分页
1 2 3 4 5 6 | SELECT * FROM ( SELECT ROWNUM RN,A.* FROM ( SELECT * FROM STUDENT ORDER BY SNAME ASC )A WHERE ROWNUM <= 20 ) WHERE RN >=11; |
mybatis分页
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | <? xml version="1.0" encoding="UTF-8"?> <! DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" > < sqlMap namespace="student"> < typeAlias alias="Student" type="com.bobo.code.model.Student" /> < select id="select" resultClass="Student"> SELECT * FROM ( SELECT A.*,ROWNUM RN FROM (SELECT * FROM STUDENT) A WHERE <![CDATA[ ROWNUM <= #maxRowNum#]]> ) WHERE <![CDATA[RN >= #minRowNum#]]> </ select > < select id="count" resultClass="java.lang.Integer"> SELECT count(*) FROM STUDENT </ select > </ sqlMap > |
1 2 | queryMap.put( "minRowNum" , (pageNumber - 1 )*Pager.DEFAULT_PAGE_SIZE + 1 ); //oracle rownum起 queryMap.put( "maxRowNum" , pageNumber * Pager.DEFAULT_PAGE_SIZE ); //oracle rownum止 |
为何一定要用3层select分析:
3层时sql分页语句:
SELECT * FROM (SELECT ROWNUM RN, A.* FROM (SELECT * FROM STUDENT ORDER BY SNO ASC) A WHERE ROWNUM <= 10) WHERE RN >= 1;
3层时图解查询顺序:
2层时sql语句:
SELECT * FROM (SELECT ROWNUM RN, A.* FROM STUDENT A WHERE ROWNUM <= 10 ORDER BY SNO ASC) WHERE RN >= 1;
2层时图解查询顺序:
开发过程异常
无法转换为内部表示
当Student的属性id 为Integer,和数据库中的字段ID为VARCHAR2 导致不对应会取不出数据,报错(无法转换为内部表示):
如果sql语句错误,比如多了一个逗号之类的,会报错(ORA-00936):
作者:whatlonelytear
本文地址:https://www.cnblogs.com/whatlonelytear/p/4848552.html
欢迎转载,请在明显位置标明出处及链接。
感觉空虚寂寞,只是因为你无所关注,无处付出。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步