package zxc.utils;
/**
* 这是一个关于分页信息的封装,其中页面大小pageSize,总记录数totalSize是必须提供的数据.pageSize可以从客户端提供,也可以通过配置文件提供
* 还可以在程序中设置;而totalSize只能在服务进行提供.另外必须提供的一个变量是可以选择的一般可以是:cp当前页或者是startRecordNO开始编号
*
*
* @author kht *
*/
public class Page {
/** 每页记录数据(须提供的数据) */
private Integer pageSize = 15;
/** 总记录数(须提供的数据) */
private Integer totalSize;
/** 总页数(须计算的数据) */
private Integer totalPages;
/** 当前页起始记录编号(须计算的数据) */
private Integer startRecordNO;
/** 当前页结束记录编号(须计算的数据) */
private Integer endRecordNO;
/** 当前页号(须提供的数据) */
private Integer cp = 1;
public boolean hasPreviews() {
return cp > 1;
}
public boolean hasNext() {
return cp * pageSize < totalSize;
}
public Integer getCp() {
return this.cp;
}
public void setCp(Integer cp) {
this.cp = cp < 1 ? 1 : cp;
}
public Integer getPageSize() {
return this.pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Integer getTotalSize() {
return this.totalSize;
}
public Page setTotalSize(Integer totalSize) {
this.totalSize = totalSize;
return this;
}
public Integer getTotalPages() {
return this.totalPages;
}
public Integer getEndRecordNO() {
return this.endRecordNO;
}
public Integer getStartRecordNO() {
return this.startRecordNO;
}
/**
* 计算一些需要计算的字段(此计算方法主针对提供cp变量的计算)
*/
public void calPage1() {
this.totalPages = (int) Math.ceil(totalSize * 1.0 / pageSize);
this.cp = this.cp > this.totalPages ? this.totalPages : this.cp;
this.cp = this.cp < 1 ? 1 : this.cp;
this.startRecordNO = (this.cp - 1) * this.pageSize + 1;
this.endRecordNO = Math.min(this.cp * this.pageSize, totalSize);
this.start = this.startRecordNO -1;
this.limit = this.pageSize;
}
/**此变量与startRecordNO有相近的含义,它相当于一个记录的游标,与startRecordNO在值上面板相差1(start+1=startRecordNO)*/
private Integer start = 0;
/**与pageSize是相同的含义*/
private Integer limit = 15;
public Integer getLimit() {
return limit;
}
public void setLimit(Integer limit) {
this.limit = limit;
}
public Integer getStart() {
return start;
}
public void setStart(Integer start) {
this.start = start;
}
/**
* 计算一些需要计算的字段(此计算方法主针对提供start与limit变量的计算)
*/
public void calPage2() {
this.totalPages = (int) Math.ceil(totalSize * 1.0 / limit);
this.startRecordNO = this.start + 1;
this.endRecordNO = Math.min(this.start + this.limit, totalSize);
this.cp = this.start / this.limit + 1;
this.pageSize = this.limit;
}
//分页类型(可以为oracle<==>cp或mysql<==>st)//其中cp表示currentPage;st表示startLimit
private String pt = "mysql";//page type
public void setPt(String pt){
this.pt = pt;
}
public void calPage() {
if ("mysql".equals(this.pt) || "st".equals(this.pt)) {
this.calPage2();
} else if("oracle".equals(this.pt)||"cp".equals(this.pt)){
this.calPage1();
}
}
@Override
public String toString() {
StringBuffer buffer = new StringBuffer();
buffer.append("\n");
buffer.append("\tpageSize:" + pageSize);
buffer.append("\ttotalSize:" + totalSize);
buffer.append("\ttotalPages:" + totalPages);
buffer.append("\tstartRecordNO:" + startRecordNO);
buffer.append("\n");
buffer.append("\tendRecordNO:" + endRecordNO);
buffer.append("\tcp:" + cp);
buffer.append("\tstart:" + start);
buffer.append("\tlimit:" + limit);
return buffer.toString();
}
public static void main(String[] args) {
System.out.println("19/10\t" + Math.floor(19.0 / 10));
System.out.println("19/10\t" + Math.ceil(19.0 / 10));
System.out.println("9/10\t" + Math.floor(9.0 / 10));
System.out.println("9/10\t" + Math.ceil(9.0 / 10));
System.out.println("10/10\t" + Math.floor(10.0 / 10));
System.out.println("10/10\t" + Math.ceil(10.0 / 10));
System.out.println("20/10\t" + Math.floor(20.0 / 10));
System.out.println("20/10\t" + Math.ceil(20.0 / 10));
}
/**
* SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A
* WHERE ROWNUM <= 40 ) WHERE RN >= 21 --1:无ORDER BY排序的写法。(效率最高)
* --(经过测试，此方法成本最低，只嵌套一层，速度最快！即使查询的数据量再大，也几乎不受影响，速度依然！) <br> SELECT * FROM
* (Select ROWNUM AS ROWNO, T.* from k_task T where AND ROWNUM <= 20)
* TABLE_ALIAS WHERE TABLE_ALIAS.ROWNO >= 10; --2:有ORDER BY排序的写法。(效率最高)
* --(经过测试，此方法随着查询范围的扩大，速度也会越来越慢哦！) <br> SELECT * FROM (SELECT TT.*, ROWNUM
* AS ROWNO FROM (Select t.* from k_task T where ORDER BY FACT_UP_TIME,
* flight_no) TT WHERE ROWNUM <= 20) TABLE_ALIAS where TABLE_ALIAS.rowno >=
* 10; --3:无ORDER BY排序的写法。(建议使用方法1代替) --(此方法随着查询数据量的扩张，速度会越来越慢哦！) <br>
* SELECT * FROM (Select ROWNUM AS ROWNO, T.* from k_task T where
* Flight_date between to_date('20060501', 'yyyymmdd') and
* to_date('20060731', 'yyyymmdd')) TABLE_ALIAS WHERE TABLE_ALIAS.ROWNO <=
* 20 AND TABLE_ALIAS.ROWNO >= 10; --TABLE_ALIAS.ROWNO between 10 and 100;
* --4:有ORDER BY排序的写法.(建议使用方法2代替) --(此方法随着查询范围的扩大，速度会越来越慢哦！) <br> SELECT *
* FROM (SELECT TT.*, ROWNUM AS ROWNO FROM (Select * from k_task T where
* flight_date between to_date('20060501', 'yyyymmdd') and
* to_date('20060531', 'yyyymmdd') ORDER BY FACT_UP_TIME, flight_no) TT)
* TABLE_ALIAS where TABLE_ALIAS.rowno BETWEEN 10 AND 20; --5另类语法。(有ORDER
* BY写法） --(语法风格与传统的SQL语法不同，不方便阅读与理解，为规范与统一标准，不推荐使用。) <br> With partdata as(
* SELECT ROWNUM AS ROWNO, TT.* FROM (Select * from k_task T where
* flight_date between to_date('20060501', 'yyyymmdd') and
* to_date('20060531', 'yyyymmdd') ORDER BY FACT_UP_TIME, flight_no) TT
* WHERE ROWNUM <= 20) Select * from partdata where rowno >= 10; --6另类语法
* 。(无ORDER BY写法） With partdata as( Select ROWNUM AS ROWNO, T.* From K_task
* T where Flight_date between to_date('20060501', 'yyyymmdd') and
* To_date('20060531', 'yyyymmdd') AND ROWNUM <= 20) Select * from partdata
* where Rowno >= 10 SQL> Create TABLE TEST AS Select ROWNUM ID, A.* FROM
* DBA_OBJECTS A; 表已创建。 SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,
* 'TEST') PL/SQL 过程已成功完成。 SQL> SET AUTOT ON EXP SQL> Select ID,
* OBJECT_NAME, OWNER 2 FROM 3 ( 4 Select ROWNUM RN, ID, OBJECT_NAME, OWNER
* 5 FROM 6 ( 7 Select OWNER, OBJECT_NAME, ID 8 FROM TEST orDER BY OWNER 9 )
* 10 Where ROWNUM <= 10 11 ) 12 Where RN >= 1; ID OBJECT_NAME OWNER
* ---------- ------------------------------ ------------------------------
* 6231 AL CCC 6232 AL_I_2 CCC 6233 AL_I_FNAME_STATUS CCC 6236 BCB CCC 6235
* AL_U1 CCC 6234 AL_P CCC 6240 BCF_U1 CCC 6239 BCF_P CCC 6238 BCF CCC 6237
* BCB_U1 CCC 已选择10行。 Execution Plan
* ---------------------------------------------------------- 0 Select
* STATEMENT Optimizer=CHOOSE (Cost=72 Card=10 Bytes=1090) 1 0 VIEW (Cost=72
* Card=10 Bytes=1090) 2 1 COUNT (STOPKEY) 3 2 VIEW (Cost=72 Card=6363
* Bytes=610848) 4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363
* Bytes=165438) 5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363
* Bytes=165438) 上面例子给出的就是分页查询的标准写法，对于查询前N条数据具有最高的效率。 但是这种分页排序语句存在一个问题： SQL>
* Select ID, OBJECT_NAME, OWNER 2 FROM 3 ( 4 Select ROWNUM RN, ID,
* OBJECT_NAME, OWNER 5 FROM 6 ( 7 Select OWNER, OBJECT_NAME, ID 8 FROM TEST
* orDER BY OWNER 9 ) 10 Where ROWNUM <= 20 11 ) 12 Where RN >= 11; ID
* OBJECT_NAME OWNER ---------- ------------------------------
* ------------------------------ 6249 BP_P CCC 6248
* BP_I_DEVICE_HANDLE_STATUS CCC 6247 BP CCC 6245 BDF_P CCC 6243
* BDF_I_BS_KEY CCC 6241 BCF_U2 CCC 6239 BCF_P CCC 6237 BCB_U1 CCC 6236 BCB
* CCC 6235 AL_U1 CCC 已选择10行。 Execution Plan
* ---------------------------------------------------------- 0 Select
* STATEMENT Optimizer=CHOOSE (Cost=72 Card=20 Bytes=2180) 1 0 VIEW (Cost=72
* Card=20 Bytes=2180) 2 1 COUNT (STOPKEY) 3 2 VIEW (Cost=72 Card=6363
* Bytes=610848) 4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363
* Bytes=165438) 5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363
* Bytes=165438)
* 对比这次的结果和第一次的结果，就会发现ID为6235的数据出现了两次。第一次在前10条返回记录中，6235出现了，而第二次在11到第20条记录中，6235又出现了。一条数据重复出现两次，就必然意味着有数据在两次查询中都不会出现。
* 其实造成这个问题的原因很简单，是由于排序列不唯一造成的。Oracle这里使用的排序算法不具有稳定性，也就是说，对于键值相等的数据，这种算法完成排序后，不保证这些键值相等的数据保持排序前的顺序。
* 在这个例子中，OWNER列包含了大量键值为CCC的数据，而且Oracle的排序算法不具有稳定性，因此前10行记录和前20行记录中键值的顺序不能保证一致。因此，就造成某些数据会重复出现，而有些数据不会出现的现象。
* 解决这个问题其实也很简单。有两种方法可以考虑。 一，在使用不唯一的字段排序时，后面跟一个唯一的字段。 SQL> Select ID,
* OBJECT_NAME, OWNER 2 FROM 3 ( 4 Select ROWNUM RN, ID, OBJECT_NAME, OWNER
* 5 FROM 6 ( 7 Select OWNER, OBJECT_NAME, ID 8 FROM TEST orDER BY OWNER, ID
* 9 ) 10 Where ROWNUM <= 10 11 ) 12 Where RN >= 1; ID OBJECT_NAME OWNER
* ---------- ------------------------------ ------------------------------
* 6231 AL CCC 6232 AL_I_2 CCC 6233 AL_I_FNAME_STATUS CCC 6234 AL_P CCC 6235
* AL_U1 CCC 6236 BCB CCC 6237 BCB_U1 CCC 6238 BCF CCC 6239 BCF_P CCC 6240
* BCF_U1 CCC 已选择10行。 Execution Plan
* ---------------------------------------------------------- 0 Select
* STATEMENT Optimizer=CHOOSE (Cost=72 Card=10 Bytes=1090) 1 0 VIEW (Cost=72
* Card=10 Bytes=1090) 2 1 COUNT (STOPKEY) 3 2 VIEW (Cost=72 Card=6363
* Bytes=610848) 4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363
* Bytes=165438) 5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363
* Bytes=165438) SQL> Select ID, OBJECT_NAME, OWNER 2 FROM 3 ( 4 Select
* ROWNUM RN, ID, OBJECT_NAME, OWNER 5 FROM 6 ( 7 Select OWNER, OBJECT_NAME,
* ID 8 FROM TEST orDER BY OWNER, ID 9 ) 10 Where ROWNUM <= 20 11 ) 12 Where
* RN >= 11; ID OBJECT_NAME OWNER ---------- ------------------------------
* ------------------------------ 6241 BCF_U2 CCC 6242 BDF CCC 6243
* BDF_I_BS_KEY CCC 6244 BDF_I_DF_KEY CCC 6245 BDF_P CCC 6246 BDF_U1 CCC
* 6247 BP CCC 6248 BP_I_DEVICE_HANDLE_STATUS CCC 6249 BP_P CCC 6250 BP_U1
* CCC 已选择10行。 Execution Plan
* ---------------------------------------------------------- 0 Select
* STATEMENT Optimizer=CHOOSE (Cost=72 Card=20 Bytes=2180) 1 0 VIEW (Cost=72
* Card=20 Bytes=2180) 2 1 COUNT (STOPKEY) 3 2 VIEW (Cost=72 Card=6363
* Bytes=610848) 4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363
* Bytes=165438) 5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363
* Bytes=165438) 一般在排序字段后面跟一个主键就可以了，如果表不存在主键，跟ROWID也可以。
* 这种方法最简单，且对性能的影响最小。另一种方法就是使用前面给出过多次的BETWEEN AND的方法。 SQL> Select ID,
* OBJECT_NAME, OWNER 2 FROM 3 ( 4 Select ROWNUM RN, ID, OBJECT_NAME, OWNER
* 5 FROM 6 ( 7 Select OWNER, OBJECT_NAME, ID 8 FROM TEST orDER BY OWNER 9 )
* 10 ) 11 Where RN BETWEEN 1 AND 10; ID OBJECT_NAME OWNER ----------
* ------------------------------ ------------------------------ 6231 AL CCC
* 6232 AL_I_2 CCC 6233 AL_I_FNAME_STATUS CCC 6234 AL_P CCC 6238 BCF CCC
* 6240 BCF_U1 CCC 6242 BDF CCC 6244 BDF_I_DF_KEY CCC 6246 BDF_U1 CCC 6255
* BRL_U1 CCC 已选择10行。 Execution Plan
* ---------------------------------------------------------- 0 Select
* STATEMENT Optimizer=CHOOSE (Cost=72 Card=6363 Bytes=693567) 1 0 VIEW
* (Cost=72 Card=6363 Bytes=693567) 2 1 COUNT 3 2 VIEW (Cost=72 Card=6363
* Bytes=610848) 4 3 SORT (ORDER BY) (Cost=72 Card=6363 Bytes=165438) 5 4
* TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438) SQL>
* Select ID, OBJECT_NAME, OWNER 2 FROM 3 ( 4 Select ROWNUM RN, ID,
* OBJECT_NAME, OWNER 5 FROM 6 ( 7 Select OWNER, OBJECT_NAME, ID 8 FROM TEST
* orDER BY OWNER 9 ) 10 ) 11 Where RN BETWEEN 11 AND 20; ID OBJECT_NAME
* OWNER ---------- ------------------------------
* ------------------------------ 6254 BRL_P CCC 6253 BRL_I_DTS CCC 6252
* BRL_I_BS_KEY CCC 6251 BRL CCC 6250 BP_U1 CCC 6249 BP_P CCC 6248
* BP_I_DEVICE_HANDLE_STATUS CCC 6247 BP CCC 6264 CCF CCC 6263 CCB_U1 CCC
* 已选择10行。 Execution Plan
* ---------------------------------------------------------- 0 Select
* STATEMENT Optimizer=CHOOSE (Cost=72 Card=6363 Bytes=693567) 1 0 VIEW
* (Cost=72 Card=6363 Bytes=693567) 2 1 COUNT 3 2 VIEW (Cost=72 Card=6363
* Bytes=610848) 4 3 SORT (ORDER BY) (Cost=72 Card=6363 Bytes=165438) 5 4
* TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)
* 这种方式由于采用表数据的全排序，每次只取全排序中的某一部分数据，因此不会出现上面提到的重复数据问题。<br>
* 但是正是由于使用了全排序，而且ROWNUM信息无法推到查询内部，导致这种写法的执行效率很低。对比二种效率的例子，<br>
* 前面分页查询的文章中以及有很多了，这里就不在重复描述了。虽然这种方式也可以避免重复数据问题，<br> 但是不推荐使用这种方式。 In the
* following example, the inner select query which is named as 'a' can be
* any thing with joins, order by clause etc.. The outer selec clause should
* be as given below. The gHigherBound and gLowerBound can be like 10 and 1
* respectively. and these will be changed on click of a next button to
* display the next set of rows. query := 'select * from ( select a.*,
* rownum r from ( select E.Name, E.ID, from Employee E group by E.Name,E.ID )
* a where rownum <= '|| gHigherBound ||' ) b where r > '|| gLowerBound
* ||'';
*/
}
