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 {
			/**
			 * 默认分页
			 * cid	cbrand	cmodel	corigin	cproduce	cbuy	cpripal	cvolume	capac
			 * */
			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.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" ο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();
			}
		}
	}
	
}

原图


小傅哥 CSDN认证博客专家 ASM 设计模式 面经手册
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页
实付 39.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值