使用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分页

 SELECT * FROM (
       SELECT ROWNUM RN,A.* FROM (
            SELECT *
            FROM STUDENT ORDER BY SNAME ASC
       )A WHERE ROWNUM <= 20 )
  WHERE RN >=11;

mybatis分页

<?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>

　　

		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):