【原】jsp 分頁(yè) oracle數(shù)據(jù)庫(kù)

小傅哥 2021-12-13

展開全文

分頁(yè)效果演示圖

1、建立一個(gè)分頁(yè)類 model

/**
 * 分頁(yè)類
 * @author 付政委
 *
 */
public class FenPage extends SerachType{

private String pageS;//獲取數(shù)據(jù)能分出多少頁(yè)
private String pageCount;//數(shù)據(jù)庫(kù)數(shù)據(jù)總條數(shù)
private String pageNow;//當(dāng)前第幾頁(yè)
private String pageSize;//每頁(yè)大小多少條數(shù)據(jù)
private String pageNum;//每頁(yè)顯示幾個(gè)頁(yè)1、2、3、4、5例如這樣五頁(yè)

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、凡是想分頁(yè)的其他model（數(shù)據(jù)庫(kù)持久化類）只要繼承這個(gè)定義好的分也類，就有里面的屬性了

/**
 * 車輛管理持久化類
 * @author Administrator
 * 繼承分頁(yè)類
 */
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;//車牌號(hào)
private String cmodel;//型號(hào)
private String corigin;//產(chǎn)地
private String cproduce;//出廠時(shí)間
private String cbuy;//購(gòu)車時(shí)間
private String cpripal;//購(gòu)車負(fù)責(zé)人
private String cvolume;//體積
private String capac;//容量
public String getCid() {
return cid;
}
//略去get set

3、定義一個(gè)分頁(yè)的操作類把oracle的分頁(yè)操作，封裝起來(lái)

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;
}

/**
 * 定義分頁(yè)信息
 * pageSize 每頁(yè)5條記錄
 * pageNum 每頁(yè)5個(gè)小分頁(yè)項(xiàng)
 * */
public final static String pageSize = "5";
public final static String pageNum = "5";

/********************************
 * 模仿limit
 * @param table 表名
 * @param odby  以誰(shuí)排序、建議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所以單寫出來(lái)一個(gè)rownum分頁(yè)
 * @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;
}
/**********************
 * 獲取到能分出來(lái)多少頁(yè)
 * @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;
/**
 * 分頁(yè)效果查詢
 * */
@SuppressWarnings("static-access")
@Override
public ArrayList<_Car> doSelectPage(FenPage fpage) {
// TODO Auto-generated method stub
ArrayList<_Car> alcar = new ArrayList<_Car>();
/*判斷用以第一次取數(shù)據(jù)庫(kù)信息時(shí)候把分頁(yè)信息加載進(jìn)去*/
boolean pdFp = false;

try {
/**
 * 默認(rèn)分頁(yè)
 * 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));//當(dāng)前頁(yè)為第幾頁(yè)
car.setPageNum(limit.pageNum);//每頁(yè)顯示的 下面的 幾個(gè)數(shù)字1 2 3 4 5
car.setPageSize(limit.pageSize);//每頁(yè)大小，既是顯示出來(lái)多少條
car.setPageS(limit.doPageS());//顯示一共能分出來(lái)多少頁(yè)
pdFp = true;
}

alcar.add(car);
}

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

return alcar;
}

}

5、jsp頁(yè)面使用數(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>序號(hào)</td>
<td>車牌號(hào)</td>
<td>型號(hào)</td>
<td>產(chǎn)地</td>
<td id="dne" class="dn">出廠時(shí)間</td>
<td id="dne" class="dn">購(gòu)車時(shí)間</td>
<td id="dne" class="dn">購(gòu)車負(fù)責(zé)人</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() %>頁(yè)
</span>
<span id="dqpage">
<%
int pageDq = 1;
if(!"1".equals(cpage.getPageNow())){
pageDq = (Integer.valueOf(cpage.getPageNow())-1); 
}
%>
當(dāng)前第<%=pageDq %>頁(yè)

</span>
<a href="<%=basePath%>Corporation?type=1&key=5&pageNow=1">首頁(yè)</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 %>">上一頁(yè)</a>


<%
int pageNow = Integer.valueOf(cpage.getPageNow());
String fcolor = "red";
if(pageNow - 3 > 0){
//每頁(yè)下面的數(shù)字?jǐn)?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 %>">下一頁(yè)</a>
<a href="<%=basePath%>Corporation?type=1&key=5&pageNow=<%=cpage.getPageS() %>">末頁(yè)</a>
</td>
</tr>
</table>
</div>
</div>
</body>
</html>

6、數(shù)據(jù)庫(kù)類獲得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();
}
}
}

}

原圖