【原】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;//车辆id
private 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")
@Override
public ArrayList<_Car> doSelectPage(FenPage fpage) {
// TODO Auto-generated method stub
ArrayList<_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 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页面使用数据

<%@ 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>产地</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 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();
}
}
}

}

原图