JSP+Oracle 实现分页

左手的ㄟ右手 2024-02-17 16:45 100阅读 0赞

**一、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>

三、总结

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