我的Oracle分页实现
我的Oralce分页实现，主要使用了三个类一个接口！
具体代码如下：
DatabaseConnection.java
package com.founder.oraclepagination.dbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;/** * 数据库连接类用于连接或销毁数据库对象 * @author husong * */public class DatabaseConnection {/** * 数据库连接驱动 */private static final String DRIVER = "oracle.jdbc.driver.OracleDriver" ;/** * 数据库连接URL地址 */private static final String URL = "jdbc:oracle:thin:@localhost:1521:hsit" ;/** * 数据库连接用户名 */private static final String USERNAME = "scott" ;/** * 数据库连接密码 */private static final String PASSWORD = "tiger" ;private Connection conn = null ;static{try{Class.forName(DRIVER) ;}catch(ClassNotFoundException e){e.printStackTrace() ;}}/** * 获取数据库连接 * @return 可用的数据库连接对象 */public Connection getConnection(){try{conn = DriverManager.getConnection(URL,USERNAME,PASSWORD) ;}catch(SQLException e){e.printStackTrace() ;}return conn ;}/** * 销毁数据库各种对象 * @param 各种数据库对象如Connection Statement ResultSet */public void destroy(Object...params){if(null==params||params.length<1){System.err.println("没有要销毁的对象或要销毁的对象为空");System.exit(0);}else{try{for(int i=0;i<params.length;i++){Object obj = params[i] ;if(obj instanceof ResultSet){ResultSet rs = (ResultSet)obj ;if(null!=rs)rs.close() ;}else if(obj instanceof Statement){Statement stmt =(Statement)obj ;if(null!=stmt)stmt.close() ;}else if(obj instanceof Connection){Connection _conn = (Connection)obj ;if(null!=_conn)_conn.close() ;}else{System.err.println("params的类型不匹配，只能为Connection,Statement,ResultSet或其子类");System.exit(0) ;}}}catch(Exception e){e.printStackTrace() ;}}}}Emp.java
package com.founder.oraclepagination.vo;import java.util.Date;/** * 雇员实体类 * * @author husong * */public class Emp {/** * 雇员编号 */private int empno;/** * 雇员名称 */private String ename;/** * 工种 */private String job;/** * 领导工号 */private int mgr;/** * 入职日期 */private Date hiredate;/** * 薪酬 */private float sal;/** * 奖金 */private float comm;/** * 部门代码 */private int deptno;public int getEmpno() {return empno;}public void setEmpno(int empno) {this.empno = empno;}public String getEname() {return ename;}public void setEname(String ename) {this.ename = ename;}public String getJob() {return job;}public void setJob(String job) {this.job = job;}public int getMgr() {return mgr;}public void setMgr(int mgr) {this.mgr = mgr;}public Date getHiredate() {return hiredate;}public void setHiredate(Date hiredate) {this.hiredate = hiredate;}public float getSal() {return sal;}public void setSal(float sal) {this.sal = sal;}public float getComm() {return comm;}public void setComm(float comm) {this.comm = comm;}public int getDeptno() {return deptno;}public void setDeptno(int deptno) {this.deptno = deptno;}}Pagination接口
package com.founder.oraclepagination.pagination;import java.sql.SQLException;import java.util.List;/** * 分页接口 * @author husong * */public interface Pagination {/** * 获取总记录数 * @return 获取到的总记录数 * @throws SQLException */int getRowCount() throws SQLException;/** * 获取总页数 * @return 获取到的总页数 */int getPageCount() ;/** * 依据当前页号取得相应的数据 * @param 当前页号 * @return 当前页号取得相应的数据 * @throws SQLException */List<Object> getPage(int pageNum) throws SQLException;}分页具体实现
package com.founder.oraclepagination.pagination.impl;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.founder.oraclepagination.dbc.DatabaseConnection;import com.founder.oraclepagination.pagination.Pagination;import com.founder.oraclepagination.vo.Emp;/** * 雇员信息分页实现类 * @author husong * */public class EmpPaginationImpl implements Pagination {private DatabaseConnection dbc = null ;private int pageSize = 5 ;public EmpPaginationImpl(){this.dbc = new DatabaseConnection() ;}public void setPageSize(int pageSize){if(0>=pageSize){this.pageSize = 5 ;}else{this.pageSize = pageSize ;}}public int getPageSize(){return this.pageSize ;}@Overridepublic int getRowCount() throws SQLException {int count = 0 ;Connection conn = null ;PreparedStatement pstmt = null ;ResultSet rs = null ;String sql = "select count(*) as count from emp" ;try{conn = dbc.getConnection() ;pstmt = conn.prepareStatement(sql) ;rs = pstmt.executeQuery() ;if(null!=rs&&rs.next())count = rs.getInt("count") ;}catch(SQLException e){throw e ;}finally{dbc.destroy(rs,pstmt,conn) ;}return count ;}@Overridepublic int getPageCount() {int rowCount = 0 ;try {rowCount = getRowCount();} catch (SQLException e) {e.printStackTrace();}return (rowCount%pageSize==0)?(rowCount/pageSize):(rowCount/pageSize+1) ;}@Overridepublic List<Object> getPage(int pageNum) throws SQLException {List<Object> list = null ;String sql = "select * from (select a.*,rownum rn from (select * from emp)a where Rownum<=?) where rn>?" ;Connection conn = null ;PreparedStatement pstmt = null ;ResultSet rs = null ;try{if(0>=pageNum){pageNum = 1 ;}else if(pageNum>=getPageCount()){pageNum = getPageCount() ;}conn = dbc.getConnection() ;pstmt = conn.prepareStatement(sql) ;pstmt.setInt(1, pageSize*pageNum) ;pstmt.setInt(2, pageSize*(pageNum-1)) ;rs = pstmt.executeQuery() ;list = new ArrayList<Object>() ;/*System.out.println(pageSize*(pageNum));System.out.println(pageSize*(pageNum-1));System.out.println(sql);*/if(null!=rs){while(rs.next()){Emp emp = new Emp() ;emp.setEmpno(rs.getInt("empno")) ;emp.setEname(rs.getString("ename"));emp.setJob(rs.getString("job"));emp.setMgr(rs.getInt("mgr"));emp.setSal(rs.getFloat("sal"));emp.setComm(rs.getFloat("comm")) ;emp.setHiredate(rs.getDate("hiredate")) ;emp.setDeptno(rs.getInt("deptno")) ;list.add(emp);}}}catch(SQLException e){throw e ;}finally{dbc.destroy(rs,pstmt,conn) ;}return list ;}}测试页
<%@ page language="java" contentType="text/html; charset=gbk" pageEncoding="gbk"%><%@ page import="com.founder.oraclepagination.vo.*,com.founder.oraclepagination.pagination.*,com.founder.oraclepagination.pagination.impl.*,java.util.*" %><%String pageNumStr = request.getParameter("pageNum") ;int pageNum =1 ;List<Object> list = null ;Pagination pagination =new EmpPaginationImpl() ;if(null==pageNumStr){pageNumStr ="1" ;}try{//pageNumStr = request.getParameter("pageNum") ;pageNum = Integer.parseInt(pageNumStr) ;if(0>=pageNum){pageNum=1 ;}else if(pageNum>=pagination.getPageCount()){pageNum= pagination.getPageCount() ;}}catch(Exception e){pageNum =1 ;//e.printStackTrace() ;}list = pagination.getPage(pageNum) ;%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=gbk"><link rel="stylesheet" href="css/main.css" type="text/css"/><title>Employee detail</title></head><body><div id="container"><table><tr><th>雇员编号</th><th>雇员名称</th><th>工种</th><th>主管编号</th><th>部门代码</th><th>入职日期</th><th>薪酬</th><th>佣金</th></tr><%if(null!=list){for(Object obj:list){Emp emp = null ;if(obj instanceof Emp){emp = (Emp)obj ;}%><tr><td><%=emp.getEmpno() %></td><td><%=emp.getEname() %></td><td><%=emp.getJob() %></td><td><%=emp.getMgr() %></td><td><%=emp.getDeptno() %></td><td><%=emp.getHiredate() %></td><td><%=emp.getSal() %></td><td><%=emp.getComm() %></td></tr><%}}%></table><p>总记录数为<%=pagination.getRowCount() %>，共<%=pagination.getPageCount() %>页，当前第<%=pageNum %>页<%if(1==pageNum){%><a href="index.jsp?pageNum=<%=pageNum+1 %>">下一页</a><%}else if(pageNum==pagination.getPageCount()){%><a href="index.jsp?pageNum=<%= pageNum-1 %>">上一页</a><%}else{%><a href="index.jsp?pageNum=<%=pageNum+1 %>">下一页</a><a href="index.jsp?pageNum=<%= pageNum-1 %>">上一页</a><%}%></p></div></body></html>本人是初学者，各位有好的意见，请多多指教！谢谢！