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 blocke.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 blocke.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" onchange="fun(this.value)"><%for(int i=1;i<=pageInfo.getPageTotal(); i++){%><option value="<%=i%>"><%=i%></option><%}%></select></body></html>

三、总结

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