对于不同数据库分页分查询析

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
	 * ||'';
	 */

}