jsp 分页 oracle数据库

分页效果演示图

1、建立一个分页类 model

/** * 分页类 * @author 付政委 * */public class FenPage extends SerachType{private String pageS;//获取数据能分出多少页private String pageCount;//数据库数据总条数private String pageNow;//当前第几页private String pageSize;//每页大小多少条数据private String pageNum;//每页显示几个页1、2、3、4、5例如这样五页public String getPageCount() {return pageCount;}public void setPageCount(String pageCount) {this.pageCount = pageCount;}public String getPageNow() {return pageNow;}public void setPageNow(String pageNow) {this.pageNow = pageNow;}public String getPageSize() {return pageSize;}public void setPageSize(String pageSize) {this.pageSize = pageSize;}public String getPageNum() {return pageNum;}public void setPageNum(String pageNum) {this.pageNum = pageNum;}public String getPageS() {return pageS;}public void setPageS(String pageS) {this.pageS = pageS;}}

2、凡是想分页的其他model（数据库持久化类）只要继承这个定义好的分也类，就有里面的属性了

/** * 车辆管理持久化类 * @author Administrator * 继承分页类 */public class _Car extends FenPage{/*10 *车辆管理 aps_car car SQL> create table aps_car(  2  cid number(10) primary key,  3  cbrand varchar2(20) not null,  4  cmodel varchar2(20),  5  corigin varchar2(50),  6  cproduce date,  7  cbuy date,  8  cpripal varchar2(10) not null,  9  cvolume number(10) not null, 10  capac number(10) not null); */private String cid;//车辆idprivate String cbrand;//车牌号private String cmodel;//型号private String corigin;//产地private String cproduce;//出厂时间private String cbuy;//购车时间private String cpripal;//购车负责人private String cvolume;//体积private String capac;//容量public String getCid() {return cid;}//略去get set

3、定义一个分页的操作类把oracle的分页操作，封装起来

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import com.aps.db.conn.OracleConn;public class CopyLimit {private Connection conn;private PreparedStatement ps;private ResultSet rs;private String table;public String getTable() {return table;}public void setTable(String table) {this.table = table;}/** * 定义分页信息 * pageSize 每页5条记录 * pageNum 每页5个小分页项 * */public final static String pageSize = "5";public final static String pageNum = "5";/******************************** * 模仿limit * @param table 表名 * @param odby  以谁排序、建议id * @param start 从数字几开始 1代表第一条数据 * @param sum   取出多少数据 * @throws SQLException  ********************************/public ResultSet doLimit(String table,String odby,String start,String sum) throws SQLException{/*这样保证table能被下面用*/this.table = table;setTable(table);String sql = "select a.* from "+table+" a where rowid in (" +"select rd from (" +"select rownum rm,rd from (" +"select rowid rd from "+table+" order by "+odby+") where rownum <= ?+?) where rm > ?)";/*获得连接*/conn = OracleConn.getConn();ps = conn.prepareStatement(sql);//起始位置start = String.valueOf((Integer.parseInt(start) - 1) * Integer.valueOf(pageSize));//赋值ps.setString(1, start);ps.setString(2, sum);ps.setString(3, start);return ps.executeQuery();}/** * 视图里面不能用rowid所以单写出来一个rownum分页 * @param table * @param start * @param sum * @return * @throws SQLException */public ResultSet doVLimit(String table,String odby,String start,String sum) throws SQLException{/*这样保证table能被下面用*/this.table = table;setTable(table);String sql = "select v.* from (" +"select rownum rm,v.* from "+table+" v " +"where rownum <= ?+? order by "+odby+") v where rm > ?";/*获得连接*/conn = OracleConn.getConn();ps = conn.prepareStatement(sql);//起始位置start = String.valueOf((Integer.parseInt(start) - 1) * Integer.valueOf(pageSize));//赋值ps.setString(1, start);ps.setString(2, sum);ps.setString(3, start);return ps.executeQuery();}/** * 获得总数据条数 * @return * @throws SQLException */public String doCount() throws SQLException{String count = "";conn = OracleConn.getConn();ps = conn.prepareStatement("select count(*) from "+getTable());rs = ps.executeQuery();if(rs.next()){count = rs.getString(1);}return count;}/********************** * 获取到能分出来多少页 * @throws SQLException  ********************** */public String doPageS() throws SQLException{String pages = doCount();double pc = Double.parseDouble(pages)/5.0;if(pc*10%10 > 0){pc = (int)pc+1;}pages = String.valueOf((int)pc);return pages;}}

4、使用上面定义的方法

public class CarDAO extends GetSelectQurey implements Car{private Connection conn;private PreparedStatement ps;private ResultSet rs;/** * 分页效果查询 * */@SuppressWarnings("static-access")@Overridepublic ArrayList<_Car> doSelectPage(FenPage fpage) {// TODO Auto-generated method stubArrayList<_Car> alcar = new ArrayList<_Car>();/*判断用以第一次取数据库信息时候把分页信息加载进去*/boolean pdFp = false;try {/** * 默认分页 * cidcbrandcmodelcorigincproducecbuycpripalcvolumecapac * */CopyLimit limit = new CopyLimit();rs = limit.doLimit("aps_car", "cid", fpage.getPageNow(), CopyLimit.pageSize);while(rs.next()){_Car car = new _Car();car.setCid(rs.getString(1));car.setCbrand(rs.getString(2));car.setCmodel(rs.getString(3));car.setCorigin(rs.getString(4));car.setCproduce(rs.getString(5));car.setCbuy(rs.getString(6));car.setCpripal(rs.getString(7));car.setCvolume(rs.getString(8));car.setCapac(rs.getString(9));if(!pdFp){limit.doCount();car.setPageCount(limit.doCount());//显示一共有多少条数据car.setPageNow(String.valueOf(Integer.valueOf(fpage.getPageNow()) + 1));//当前页为第几页car.setPageNum(limit.pageNum);//每页显示的 下面的 几个数字1 2 3 4 5car.setPageSize(limit.pageSize);//每页大小，既是显示出来多少条car.setPageS(limit.doPageS());//显示一共能分出来多少页pdFp = true;}alcar.add(car);}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return alcar;}}

5、jsp页面使用数据

<%@ page language="java" import="java.util.*" pageEncoding="GB18030"%><%@ page import="com.aps.db.model._Car" %><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml">  <head> <title>查看车辆</title><link type="text/css" rel="stylesheet" href="<%=basePath %>ZzCorporation/car/css/list.css" /><script type="text/javascript" language="javascript" src="<%=basePath %>ZzCorporation/car/js/jquery-1.4.2.js"></script><script type="text/javascript" language="javascript" src="<%=basePath %>ZzCorporation/car/js/list.js"></script></head><body><div class="mainDiv"><div class="topDiv"><div class="soso"><form action="<%=basePath%>Corporation?type=1&key=6" method="post"><input type="text" class="sosotext" name="sosotext"/><input type="submit" value="" id="subsoso" onclick="return Pderr()"/></form></div></div><div class="bodyDiv"><table border="1"><tr align="center" id="ssv"><td>选取操作</td><td>序号</td><td>车牌号</td><td>型号</td><td>产地</td><td id="dne" class="dn">出厂时间</td><td id="dne" class="dn">购车时间</td><td id="dne" class="dn">购车负责人</td><td>体积</td><td>容量</td><td>操作</td></tr><%ArrayList<_Car> alcar = (ArrayList<_Car>)request.getAttribute("alcar");_Car cpage = alcar.get(0);int i = 1;if(!"1".equals(cpage.getPageNow())){i += ((Integer.valueOf(cpage.getPageNow()) - 2) * Integer.valueOf(cpage.getPageSize()));}for(_Car c:alcar){%><tr><td><input type="checkbox" /></td><td><%=i++%></td><td><%=c.getCbrand() %></td><td><%=c.getCmodel() %></td><td><%=c.getCorigin() %></td><td id="dne" class="dn"><%=c.getCproduce() %></td><td id="dne" class="dn"><%=c.getCbuy() %></td><td id="dne" class="dn"><%=c.getCpripal() %></td><td><%=c.getCapac() %></td><td><%=c.getCvolume() %></td><td>删除|修改|<span class="detail">详情</span></td></tr><%}%><tr><td colspan="11" align="center"><span><%=cpage.getPageCount() %>条记录</span><span>共<%=cpage.getPageS() %>页</span><span id="dqpage"><%int pageDq = 1;if(!"1".equals(cpage.getPageNow())){pageDq = (Integer.valueOf(cpage.getPageNow())-1); }%>当前第<%=pageDq %>页</span><a href="<%=basePath%>Corporation?type=1&key=5&pageNow=1">首页</a><%String urlUp = "#",urlDown = "#";int pageUp = 0;if((pageUp = Integer.valueOf(cpage.getPageNow())-2) > 0){urlUp = basePath + "Corporation?type=1&key=5&pageNow="+pageUp;}int pageDown = 0;if((pageUp = Integer.valueOf(cpage.getPageNow())) < Integer.valueOf(cpage.getPageS())){urlDown = basePath + "Corporation?type=1&key=5&pageNow="+pageUp;}%><a href="<%=urlUp %>">上一页</a><%int pageNow = Integer.valueOf(cpage.getPageNow());String fcolor = "red";if(pageNow - 3 > 0){//每页下面的数字数for(int p = 1,g = pageNow-3,z = Integer.valueOf(cpage.getPageNum()); p <= z && g <= Integer.valueOf(cpage.getPageS()); p++,g++){if(g == pageDq){fcolor = "yellow";}else{fcolor = "red";}%><a href="<%=basePath%>Corporation?type=1&key=5&pageNow=<%=g %>"><font id="cco" color="<%=fcolor%>"><%=g %></font></a><%}}else{for(int p = 1,z = Integer.valueOf(cpage.getPageNum()); p <= z; p++){if(p == pageDq){fcolor = "yellow";}else{fcolor = "red";}%><a href="<%=basePath%>Corporation?type=1&key=5&pageNow=<%=p %>"><font id="cco" color="<%=fcolor%>"><%=p %></font></a><%}}%><a href="<%=urlDown %>">下一页</a><a href="<%=basePath%>Corporation?type=1&key=5&pageNow=<%=cpage.getPageS() %>">末页</a></td></tr></table></div></div></body></html>

6、数据库类获得conn

import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class OracleConn {private static Connection conn;private static String OJDO = "oracle.jdbc.driver.OracleDriver";private static String URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";private static String USER = "drdg";private static String PASSWORD = "zxcvbnm";static{try {Class.forName(OJDO);conn = DriverManager.getConnection(URL, USER, PASSWORD);conn.setAutoCommit(false);System.out.println("连接成功... ...");} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public static Connection getConn() {return conn;}public static void CloseConn(){if(conn != null){try {conn.close();conn = null;} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}

原图