JSP+Oracle 实现分页

一、oracle来实现分页的sql语句如下分析：

--每页显示6条数据
select * from emp where rownum<=6;

--总个数
select count(1) from emp;


--第1页
select * from (select e.*, rownum r from emp  e where rownum<=6*1)
where r>(1-1)*6


--第2页
select * from (select e.*, rownum r from emp  e where rownum<=6*2)
where r>(2-1)*6

--第3页
select * from (select e.*, rownum r from emp  e where rownum<=6*3)
where r>(3-1)*6

--第4页
select * from (select e.*, rownum r from emp  e where rownum<=6*4)
where r>(4-1)*6

二、具体实现步骤如下：

1、封装数据Bean

/**
 * 实体类
 * @author Administrator
 *
 */
public class Emp {
	// shift+ctrl+Y 小写
	// shift+ctrl+X 大写
	private int empno;
	private String ename;
	private String job;
	private int mgr;
	private Date hiredate;
	private double sal;
	private double comm;
	private int deptno;

	public int getEmpno() {
		return empno;
	}

	public void setEmpno(int empno) {
		this.empno = empno;
	}.......}

2、封装分页Bean

package com.hlx.entity;
/**
 * 分页Bean
 * @author Administrator
 *
 */
public class PageInfo {
	private int pageSize=6; // 每页显示的个数
	private int pageCount; // 总个数
	private int pageTotal; // 总页数
	private int pageCurrent; // 当前页

	public int getPageSize() {
		return pageSize;
	}

	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}

	public int getPageCount() {
		return pageCount;
	}

	/**
	 * 设置总个数，计算出总页数
	 * @param pageCount
	 */
	public void setPageCount(int pageCount) {
		this.pageCount = pageCount;
		// 计算总页数
		this.pageTotal = (pageCount%pageSize==0)?(pageCount/pageSize):(pageCount/pageSize+1);
	}

	public int getPageTotal() {
		return pageTotal;
	}



	public int getPageCurrent() {
		return pageCurrent;
	}

	public void setPageCurrent(int pageCurrent) {
		this.pageCurrent = pageCurrent;
	}

}

3、封装业务Bean

/**
 * 底层数据继承基类(BaseDao)
 * 
 * @author Administrator
 * 
 */
public class EmpDao extends BaseDao {

	// 声明对象
	private String sql = "";
	private Connection con;
	private PreparedStatement ps;
	private ResultSet rs;

	private CallableStatement cs;

	

	/**
	 * --(1)总个数 select count(1) from emp;
	 * 
	 * @return
	 */
	public int getCount() {
		sql = "select count(1) from emp";

		// 1)获得连接
		con = this.getConnection();

		// 2)获得ps对象
		try {
			ps = con.prepareStatement(sql);

			// 3)执行
			rs = ps.executeQuery();

			// 4）光标往下移
			rs.next();

			// 5)获得值
			return rs.getInt(1);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.closeAll(rs, ps, con); // 关闭
		}

		return 0;
	}

	/**
	 * /** --(2)返回具体的数据(每页) select * from (select e.*, rownum r from emp e where
	 * rownum<=6*2) where r>(2-1)*6
	 * 
	 * @param pageSize
	 *            每页显示个数
	 * @param pageCurrent
	 *            当前页
	 * @return 返回集合
	 */
	public List<Emp> getPageInfo(int pageSize, int pageCurrent) {

		// 存放结果
		List<Emp> lists = new ArrayList<Emp>();

		sql = " select * from (select e.*, rownum r from emp  e where rownum<="
				+ pageCurrent + "*" + pageSize + ") where r>(" + pageCurrent
				+ "-1)*" + pageSize + "";

		// 1)获得连接
		con = this.getConnection();

		// 2)获得ps对象
		try {
			ps = con.prepareStatement(sql);

			// 3)执行
			rs = ps.executeQuery();

			// 4）遍历数据
			while (rs.next()) {
				// 封装对象
				Emp emp = new Emp(rs.getInt(1), rs.getString(2),
						rs.getString(3), rs.getInt(4), rs.getDate(5),
						rs.getDouble(6), rs.getDouble(7), rs.getInt(8));

				// 存入集合中
				lists.add(emp);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.closeAll(rs, ps, con); // 关闭
		}

		return lists;
	}
	}

4、页面来实现分页

<table border="1" width="738" height="65">
		<tbody>
			<tr>
				<td> 编号</td>
				<td> 姓名</td>
				<td> 工种</td>
				<td> 上司</td>
				<td> 日期</td>
				<td> 工资</td>
				<td> 奖金</td>
				<td> 部门编号</td>
			</tr>

			<jsp:useBean id="dao" class="com.hlx.dao.EmpDao"></jsp:useBean>
			<jsp:useBean id="pageInfo" class="com.hlx.entity.PageInfo"></jsp:useBean>


			<%
				  //获得总个数
				   int count = dao.getCount();
				   
				   //获得当前页//
				   String pageIndex=request.getParameter("pageIndex");
				   //判断
				   int pageCurrent = (pageIndex==null)?(1):(Integer.parseInt(pageIndex));
				   /
				   
				   //每页显示的个数
				   int pageSize = 6;
				   
				   
				   //计算出总页数
				   pageInfo.setPageCount(count);
				   
				   
				   //调用方法
				   List<Emp> lists = dao.getPageInfo(pageSize, pageCurrent);
				   
				   for(Emp emp :lists){
			%>


			<tr>
				<td><%=emp.getEmpno()%></td>
				<td><%=emp.getEname()%></td>
				<td><%=emp.getJob()%></td>
				<td><%=emp.getMgr()%></td>
				<td><%=emp.getHiredate()%></td>
				<td><%=emp.getSal()%></td>
				<td><%=emp.getComm()%></td>
				<td><%=emp.getDeptno()%></td>
			</tr>


			<%
				}
			%>
		</tbody>
	</table>
	第<%=pageCurrent%>/<%=pageInfo.getPageTotal()%>页
	<%
		if(pageCurrent>1){
	%>
	<a href="index.jsp?pageIndex=1">首页</a>
	<a href="index.jsp?pageIndex=<%=pageCurrent-1%>">上一页</a>
	<%
		}
	%>

	<%
		if(pageCurrent<pageInfo.getPageTotal()){
	%>
	<a href="index.jsp?pageIndex=<%=pageCurrent+1%>">下一页</a>
	<a href="index.jsp?pageIndex=<%=pageInfo.getPageTotal()%>">末页</a>

	<%
		}
	%>


	<p />
	<%
		for(int i=1;i<=pageInfo.getPageTotal(); i++){
	%>

	<a href="index.jsp?pageIndex=<%=i%>"><%=i%>  </a>

	<%
		}
	%>

	<p />
	<select id="number" οnchange="fun(this.value)">
		<%
			for(int i=1;i<=pageInfo.getPageTotal(); i++){
		%>
		<option value="<%=i%>"><%=i%></option>

		<%
			}
		%>
	</select>
</body>
</html>

三、总结

    　用三种不同的形式完成了分页（ＪＳＰ＋Ｏｒａｃｌｅ）。