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

巴特西

使用rownum对oracle分页

以Student表为例进行分页

建表及插入

表内容

纯sql分页

mybatis分页

为何一定要用3层select分析:

3层时sql分页语句:

3层时图解查询顺序:

2层时sql语句:

2层时图解查询顺序:

开发过程异常

无法转换为内部表示

最新文章

热门文章