分頁效果演示圖
1、建立一個分頁類 model
/**
* 分頁類
* @author 付政委
*
*/
public class FenPage extends SerachType{
private String pageS;//獲取數(shù)據(jù)能分出多少頁
private String pageCount;//數(shù)據(jù)庫數(shù)據(jù)總條數(shù)
private String pageNow;//當前第幾頁
private String pageSize;//每頁大小多少條數(shù)據(jù)
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（數(shù)據(jù)庫持久化類）只要繼承這個定義好的分也類，就有里面的屬性了
/**
* 車輛管理持久化類
* @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;//車輛id
private String cbrand;//車牌號
private String cmodel;//型號
private String corigin;//產(chǎn)地
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 從數(shù)字幾開始 1代表第一條數(shù)據(jù)
* @param sum 取出多少數(shù)據(jù)
* @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();
}
/**
* 獲得總數(shù)據(jù)條數(shù)
* @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")
@Override
public ArrayList<_Car> doSelectPage(FenPage fpage) {
// TODO Auto-generated method stub
ArrayList<_Car> alcar = new ArrayList<_Car>();
/*判斷用以第一次取數(shù)據(jù)庫信息時候把分頁信息加載進去*/
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());//顯示一共有多少條數(shù)據(jù)
car.setPageNow(String.valueOf(Integer.valueOf(fpage.getPageNow()) + 1));//當前頁為第幾頁
car.setPageNum(limit.pageNum);//每頁顯示的 下面的 幾個數(shù)字1 2 3 4 5
car.setPageSize(limit.pageSize);//每頁大小，既是顯示出來多少條
car.setPageS(limit.doPageS());//顯示一共能分出來多少頁
pdFp = true;
}
alcar.add(car);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return alcar;
}
}
5、jsp頁面使用數(shù)據(jù)
<%@ 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./TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www./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" οnclick="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>產(chǎn)地</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){
//每頁下面的數(shù)字數(shù)
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、數(shù)據(jù)庫類獲得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 block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.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 block
e.printStackTrace();
}
}
}
}
原圖
