Oracle分页

Oracle分页有许多形式，查询效率有高低之分，现在写其中的一种。

一、分页SQL
注：在创建相关表时字段名不能重复，如t_empl表中有个name，在t_jop中也来个name--这样的话查询会出异常。

SELECT * FROM (
SELECT A.*, ROWNUM RN from (
select e.eid,e.ename,e.sex,to_char(e.birthday,'yyyy-mm-dd'),d.dname,j.jname,to_char(e.hiredate,'yyyy-mm-dd'),js.status, d.did,j.jid,js.jsid from t_empl e,t_dept d,t_job j,t_jobstatus js where e.deptno=d.did and e.job=j.jid and e.status=js.jsid  and e.eid!=42 and j.jname!='董事长' order by eid
) A 
) WHERE RN BETWEEN ? AND ?

二、结合项目
如果在项目中模型层有抽取baseDao,则分页SQL自然会分离，下面是这样情况下的写法。
1.查询员工的SQL。需注意的是：由于这里的查询有可能加条件，所以排序要加在最后（sql.append(" order by eid) A ");）

public Pager<Object[]> search(Empl empl,Job job,Dept dept,JobStatus jobStatus,int currId,int offset) {
		List<Object> params=new ArrayList<>();
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT A.*, ROWNUM RN from (select e.eid,e.ename,e.sex,to_char(e.birthday,'yyyy-mm-dd'),d.dname,j.jname,to_char(e.hiredate,'yyyy-mm-dd'),js.status, d.did,j.jid,js.jsid "
				+ "from t_empl e,t_dept d,t_job j,t_jobstatus js "
				+ "where e.deptno=d.did and e.job=j.jid and e.status=js.jsid  and e.eid!="+currId+" and j.jname!='董事长'");
		if(empl!=null){
			if(empl.getEname()!=null&&!"".equals(empl.getEname().trim())){
				sql.append(" and e.ename like ?");
				params.add("%"+empl.getEname()+"%");
			}
		}
		if(job!=null){
			if(job.getJname()!=null&&!"请选择该用户所属职位".equals(job.getJname().trim())){
				sql.append(" and j.jname like ?");
				params.add("%"+job.getJname()+"%");
			}
		}
		if(dept!=null){
			if(dept.getDname()!=null&&!"请选择该用户所属部门".equals(dept.getDname().trim())){
				sql.append(" and d.dname like ?");
				params.add("%"+dept.getDname()+"%");
			}
		}
		if(jobStatus!=null){
			if(jobStatus.getStatus()!=null&&!"请选择该用户所属态".equals(jobStatus.getStatus().trim())){
				sql.append(" and js.status like ?");
				params.add("%"+jobStatus.getStatus()+"%");
			}
		}
		sql.append(" order by eid) A ");
		
		return listAsObjectArrayHaveParam(sql.toString(), offset, 10, params.toArray());
	}

注：实际上这个SQL只是去除了原SQL中最外层的嵌套"SELECT * FROM (..) WHERE RN BETWEEN ? AND ?"
2.baseDao中的SQL
a.查询从行数的sql_count：这里采用StringBuffer装SQL，更加灵活。b.查询数据的sql_select：这里只是把需要的最外层SQL加入就好。

public Pager<Object[]> listAsObjectArrayHaveParam(String sql, int offset, int pageRows,
			Object... args) {
			Connection conn = C3P0DBUtils.getConnection();
			QueryRunner run = new QueryRunner();
			Pager<Object[]> pager = new Pager<Object[]>();
			try {
				// 统计总行数
				/*String sql_count = "select count(*) "
						+ sql.substring(sql.indexOf("from"));*/
				StringBuffer sql_count = new StringBuffer();
				sql_count.append("select count(*) from ("+sql+")");
				BigDecimal sumRows = null;
				if (args == null || args.length == 0) {
					sumRows = run.query(conn, sql_count.toString(),
							new ScalarHandler<BigDecimal>());
				} else {
					sumRows = run.query(conn, sql_count.toString(),
							new ScalarHandler<BigDecimal>(), args);
				}
				// 总页数
				int sumPages = (int) ((Integer.parseInt(String.valueOf(sumRows))
						+ pageRows - 1) / pageRows);
				// 判断当前页
				if (offset > sumPages) { // 大于总页数就等于总页数
					offset = sumPages;
				}
				if (offset < 1) {
					offset = 1;
				}
				// 数据
				List<Object[]> list = null;
				if (args == null || args.length == 0) {
					String sql_select = "SELECT * FROM (" + sql + ") WHERE RN BETWEEN ? AND ?";
					System.out.println(sql_select);
					list = run.query(conn, sql_select, new ArrayListHandler(),
							(offset - 1) * pageRows + 1, (offset - 1) * pageRows
									+ pageRows);
				} else {
					// 数据
					// 直接把页面和查询的行数写在sql后面
					String sql_select = "SELECT * FROM (" + sql + ") WHERE RN BETWEEN "+((offset - 1) * pageRows + 1)+" AND "+(pageRows + (offset - 1) * pageRows)+"";
					list = run.query(conn, sql_select, new ArrayListHandler(), args);
				}
				// 数据封装
				pager.setList(list);
				pager.setOffset(offset);
				pager.setPageRows(pageRows);
				pager.setSumPages(sumPages);
				pager.setSumRows(Integer.parseInt(String.valueOf(sumRows)));
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				C3P0DBUtils.close(conn);
			}
			return pager;
		
	}

大功告成！