分頁顯示數(shù)據(jù)庫表中的記錄

1、利用以下技術來實現(xiàn)對Oracle數(shù)據(jù)庫中的表Emp中的全部記錄實行分頁顯示。技術點：(JSP+Servlet+JavaBean+c3p0+Oracle)1、com.sun.pojo類代碼：package com.sun.pojo;import java.io.Serializable;import java.util.Date;public class Emp implements Serializable private static final long serialVersionUID = 1L; private Integer empno;/ 員工編號 private String e

2、name;/ 員工姓名 private String job;/ 工種 private Integer mgr;/ 上級經(jīng)理編號 private Date hiredate;/ 入職日期 private Double sal;/ 薪水 private Double comm;/ 津貼 private Integer deptno;/ 部門編號 public Emp() / no op public Integer getEmpno() public void setEmpno(Integer empno) public String getEname() public void setEnam

3、e(String ename) public String getJob() public void setJob(String job) public Integer getMgr() public void setMgr(Integer mgr) return mgr; this.job = job; return job; this.ename = ename; return ename; this.empno = empno; return empno; this.mgr = mgr; public Date getHiredate() public void setHiredate(

4、Date hiredate) public Double getSal() public void setSal(Double sal) public Double getComm() public void setComm(Double comm) public Integer getDeptno() return deptno; m = comm; return comm; this.sal = sal; return sal; this.hiredate = hiredate; return hiredate; public void setDeptno(Integer deptno)

5、this.deptno = deptno;2、分頁封裝類package com.sun.page;import java.util.List;/* 分頁封裝類.可以適用于任何一張表* author Administrator*/SuppressWarnings("unchecked")public class Pager private static final int size = 5;/設置每頁顯示幾行 private int totalpage;/設置總頁數(shù)-即(表的記錄數(shù)/行數(shù))+1或者(表的記錄數(shù)/size)private List list;/設置把分頁后的數(shù)據(jù)

6、放到此List集合/* return 返回每頁顯示幾行*/public static int getSize() return size;/* return 返回的總頁數(shù)*/public int getTotalpage() return totalpage;/* 設置總頁數(shù)-即(表的記錄數(shù)/行數(shù))+1或者(表的記錄數(shù)/行數(shù)) * param totalpage* 總頁數(shù)*/public void setTotalpage(int totalpage) this.totalpage = totalpage;public List getList() return list;/* 設置把分頁后的

7、數(shù)據(jù)放到此List集合* param list*/public void setList(List list) this.list = list;3、dao類-3.1-連接數(shù)據(jù)庫的工具類(使用了連接池) package com.sun.dao;import java.beans.PropertyVetoException;import java.sql.Connection;import java.sql.Statement;import java.sql.ResultSet;import java.sql.SQLException;import com.mchange.v2.c3p0.Com

8、boPooledDataSource;public class DBUtil private static ComboPooledDataSource cpd;/ 創(chuàng)建c3p0連接池的數(shù)據(jù)源對象/ 設置連接池管理屬性 cpd = new ComboPooledDataSource(); try / 使用連接池連接數(shù)據(jù)庫 cpd.setDriverClass(driverName); cpd.setJdbcUrl(url); cpd.setUser(user); cpd.setPassword(password); / 使用單例模式創(chuàng)建連接池 static String driverName =

9、 "oracle.jdbc.driver.OracleDriver" String url = "jdbc:oracle:thin::1521:orcl" String user = "scott" String password = "tiger"cpd.setMaxPoolSize(10);/ 設置連接池中最大連接數(shù)目 cpd.setInitialPoolSize(5);/ 設置連接池中初始化連接數(shù)目 cpd.setAcquireIncrement(3);/ 自動增長數(shù)目 cpd.setMax

10、Statements(50);/ 最大執(zhí)行 sql語句(一次可以執(zhí)行多少條sql語句)/* * 獲得數(shù)據(jù)庫的連接 * * return Connection對象 */ public static Connection getConnection() Connection conn = null; try conn = cpd.getConnection();/ 從c3p0連接池獲得數(shù)據(jù)庫連接 catch (PropertyVetoException pve) pve.printStackTrace(); catch (SQLException sqle) return conn; sqle.p

11、rintStackTrace(); /* * 關閉數(shù)據(jù)庫連接 * * param conn * Connection對象 * param ps * Statement對象 * param rs * ResultSet對象 */ public static void close(Connection conn, Statement ps, ResultSet rs) try if (rs != null) if (ps != null) if (conn != null) conn.close(); ps.close(); rs.close(); catch (SQLException e) e

12、.printStackTrace();3.2-表的操作類 package com.sun.dao;import java.sql.Connection;import java.sql.PreparedStatement; import java.sql.ResultSet;import java.sql.SQLException; import java.util.ArrayList; import java.util.List;import com.newer.page.Pager; import com.newer.pojo.Emp;/* 把emp表按照固定的行數(shù)分頁 * author A

13、dministrator */public class EmpDao /* * * param pageno * 表示查詢emp表中的第幾頁 * return 返回一個List集合，集合中存放的是emp表中的第幾頁的記錄*/ public List<Emp> find(int pageno) Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; List<Emp> list = new ArrayList<Emp>(); try conn = DBUtil.getC

14、onnection(); String sql = "select * from (select row_number() over (order by empno) rn,emp.* from emp) where rn>? and rn<=?"/此頁面開始行數(shù)的位置(不含) /Pager.getSize()表示 每一頁要顯示的行數(shù)。 pageno表示第幾頁int begin = Pager.getSize() * (pageno - 1); /此頁面結束行數(shù)的位置(含)int end = Pager.getSize()*pageno ; System.out

15、.println("從第 " + (begin+1) + " 條到第" + end + " 條數(shù)據(jù)");ps = conn.prepareStatement(sql); ps.setInt(1, begin); ps.setInt(2, end); rs = ps.executeQuery(); while (rs.next() / 循環(huán)結果集。 / 從結果集中獲得一條(行)記錄，且封裝到POJO類 emp Emp emp = new Emp(); emp.setEmpno(rs.getInt("empno");

16、emp.setEname(rs.getString("ename"); emp.setJob(rs.getString("job"); emp.setMgr(rs.getInt("mgr"); emp.setHiredate(rs.getDate("hiredate"); emp.setSal(rs.getDouble("sal"); emp.setComm(rs.getDouble("comm"); emp.setDeptno(rs.getInt("deptno&

17、quot;); list.add(emp);/ 把一行記錄的對象添加到集合 catch (SQLException e) e.printStackTrace(); finally return list; DBUtil.close(conn, ps, rs); /* * 查詢?nèi)魏我粡埍碇械乃杏涗洈?shù)需要多少頁能夠全部顯示出來(每頁顯示固定的行數(shù):Pager類中已經(jīng)指定了)-實現(xiàn)分頁功能的基礎 * 例如:一張表中有54條記錄，現(xiàn)在每一頁顯示10條記錄，則需要6頁來顯示。* * param tablename * 要查詢的表名 * return 返回所查詢表需要分頁顯示的總頁數(shù) */ public

18、 int getTotalPage(String tablename) Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; int total = 0;/所查詢表的全部記錄數(shù) try conn = DBUtil.getConnection(); String sql = "select count(*) from " + tablename; ps = conn.prepareStatement(sql); rs = ps.executeQuery(); if (rs.next(

19、) /所查詢表當中的全部記錄數(shù)目->即一張表有多少條記錄 total = rs.getInt(1); catch (SQLException e) e.printStackTrace(); finally / Pager.getSize()表示:每頁顯示幾行 return (total % Pager.getSize() = 0) ? (total / Pager.getSize() : (total / Pager.getSize() + 1); DBUtil.close(conn, ps, rs);4、控制器-控制流程的Servlet類 package com.sun.servlet

20、;import java.io.IOException;import java.io.PrintWriter;import javax.servlet.ServletException; import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.newer.dao.EmpDao;import com.newer.page.Pager;public class EmpServl

21、et extends HttpServlet private static final long serialVersionUID = 1L;public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException this.doPost(request, response);public void doPost(HttpServletRequest request, HttpServletResponse response)throws Ser

22、vletException, IOException request.setCharacterEncoding("utf-8");response.setContentType("text/html;charset=utf-8");PrintWriter out = response.getWriter();/從請求頁面獲得要查看第幾頁String spageno = request.getParameter("pageno"); int pageno = 1;trypageno = Integer.parseInt(spageno)

23、;catch(NumberFormatException e)e.printStackTrace();pageno = 1;EmpDao empDao = new EmpDao();int totalpage = empDao.getTotalPage("emp");/獲得emp表的總頁數(shù)if(pageno<=0)pageno = 1 ;/首頁else if(pageno > totalpage)pageno = totalpage;/尾頁Pager pager = new Pager();pager.setTotalpage(totalpage);/設置頁面的

24、總頁數(shù) pager.setList(empDao.find(pageno);/設置第幾頁的數(shù)據(jù)集合request.setAttribute("pager", pager);/把pager對象綁定到request對象/轉(zhuǎn)發(fā)->參數(shù)：第幾頁request.getRequestDispatcher("listEmp.jsp?pageno="+pageno).forward(request, response);out.flush();out.close();5、視圖層-jsp頁面顯示文件名：index.jsp<% page language=&qu

25、ot;java" pageEncoding="UTF-8"%><% taglib prefix="c" uri="<!DOCTYPE HTML PUBLIC "-/W3C/DTD HTML 4.01 Transitional/EN"><html><head><title>分頁</title></head><body><h1>分頁 (JSP+Servlet+JavaBean+c3p0+Oracle)</h

26、1> <form action="EmpServlet" method="post" name="f"> <table border="1" width="80%"><tr><th>ID</th><th>empno</th><th>ename</th><th>job</th><th>mgr</th><th>hiredate&

27、lt;/th><th>sal</th><th>comm</th><th>deptno</th></tr><c:forEachvarStatus="rows"><c:choose><c:when test="$(rows.index+1)%2=0"> <%pageContext.setAttribute("color","yellow"); %> </c:when> v

28、ar="emp" items="$pager.list"<c:otherwise><%pageContext.setAttribute("color","white"); %> </c:otherwise></c:choose><tr bgcolor="$pageScope.color"><td>$rows.index+1</td><td>$emp.empno</td><td>

29、$emp.ename</td><td>$emp.job</td><td>$emp.mgr</td><td>$emp.hiredate</td><td>$emp.sal</td><td>$m </td><td>$emp.deptno</td></tr></c:forEach><tr><td colspan="9" align="right">當前是第$pa

30、ram.pageno頁&nbsp;<input type="text" name="pageno" size="4"/> <input type="submit" value="GO"/>&nbsp;<a href="EmpServlet?pageno=1">首頁</a>&nbsp; <a href="EmpServlet?pageno=$param.pageno-1">上一頁</a>&nbsp;<a href="EmpServlet?pageno=$param.pageno+1">