jsp+oracle分页实现
今天做了个基于jsp+oracle分页的实现，对于初学者来说这是好的（看了后绝对可以自己实现，动手试试把），但是对于有基础的只是温故下sql语句（没涉及到很好的分层），好了，我们开始把它实现把：
1.首先建立一个web项目。（如图）
2.导入oracle驱动包到lib目录下，开编写数据库连接类DBMamager。
package com.page.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class DBManager { private static Connection connection = null; static { try { Class.forName("oracle.jdbc.driver.OracleDriver"); connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","******","******");//自己oracle数据库的帐号密码
} catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } protected static Connection getConnection() { return connection; } public int update(String sql) { //boolean flag = false; int row = 0; Connection connection = DBManager.getConnection(); PreparedStatement statement = null; try { statement = connection.prepareStatement(sql); row= statement.executeUpdate(); // System.out.println(sql); } catch (SQLException e) { e.printStackTrace(); } return row; } public ResultSet find(String sql) { Connection connection = getConnection(); ResultSet result = null; PreparedStatement statement = null; try { System.out.println(sql); statement = connection.prepareStatement(sql); result = statement.executeQuery(); } catch (SQLException e) { e.printStackTrace(); } return result; } }
3.具体实现分页的代码如下（先看代码后面有注释别太心急慢慢看）
<%@page import="com.sun.crypto.provider.RSACipher"%> <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <%@ page import="com.page.util.*"%> <%@ page import="java.sql.*" %> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title>用户信息列表</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page"> <!-- <link rel="stylesheet" type="text/css" href="styles.css"> --> </head> <body> <table align="center" width="1000px" style="margin:100px" border="1" cellspacing="0" cellpadding="0" > <tr align="center" bgcolor="#3270E5" height="30px"> <th>编号</th> <th>用户帐号</th> <th>用户姓名</th> <th>用户密码</th> <th>用户信息</th> </tr> <% int i; int page_size=3; //分页单位 int all_pages; //总页数 int pages; //接受的页码变量 int cur_page=1; //当前页 int start_page; //本页记录开始 int count_row; //总记录数 int end_page;//本页记录结束 String sql_row="select count(id) as count_row from page"; DBManager dbManager=new DBManager(); ResultSet count_rs=dbManager.find(sql_row); count_rs.next(); count_row=count_rs.getInt("count_row"); all_pages=(int)Math.ceil((count_row+page_size-1)/page_size);//计算总页数 //判断参数pages是否为空 if(request.getParameter("pages")==null){ pages=1; }else{ pages= new Integer(request.getParameter("pages")).intValue(); } //判断当前页 if(pages > all_pages || pages == 0){ cur_page = 1; } else { cur_page = pages; } start_page=(cur_page-1)*page_size; //本页开始的记录编号数（数据库中的第几条数据） end_page=start_page+page_size;//本页显示的最后一条编号数 String sql="select * from(select rownum rn,p.* from(select * from page )p where rownum<= '"+end_page+"')where rn>'"+start_page+"'"; ResultSet rsSet=dbManager.find(sql); int t_row=1; String color="#FFFFFF"; while(rsSet.next()){ if(t_row%2==0){ //让表格更加好看双数行数时显示不同颜色 color="#EDF5FC"; }else{ color="#FFFFFF"; } %> <tr bgcolor=<%=color %>> <td><%=rsSet.getString(1) %></td> <td><%=rsSet.getString(2) %></td> <td><%=rsSet.getString(3) %></td> <td><%=rsSet.getString(4) %></td> <td><%=rsSet.getString(5) %></td> </tr> <% t_row++; } %> <tr> <td colspan="5" align="right"> <%if(cur_page>1){%>//不在第一页时显示上一页 <a href="index.jsp?pages=<%=cur_page-1%>">上一页</a> <% } if(cur_page<all_pages){//不在最后一行时显示下一页 %> <a href="index.jsp?pages=<%=cur_page+1%>">下一页</a> <a href="index.jsp?pages=<%=all_pages%>">末页</a>//显示最后一页 <% } %> <% for (i=1;i<=all_pages;i++) {%>// 循环显示每一页，本页时不显示超链接（没有下划线） <% if (i != pages) {%> <a href="index.jsp?pages=<%= i %>"><%= i %></a> <% } else{%> <%=i %> <%} %> <%}%> 共<%=all_pages %>页 </td> </tr> </table> </body> </html>
4.好了分页已经完成了，部署好tomcat运行网站吧！（如图）
第二页：
第三页：
第四页：
注意：
总页数的求取是：all_pages=(int)Math.ceil((count_row+page_size-1)/page_size);//计算总页数
sql语句是：String sql="select * from(select rownum rn,p.* from(select * from page )p where rownum<= '"+end_page+"')where rn>'"+start_page+"'";
例如：select *
from
(
select rownum rn,p.*
from
(select *
from page order by id
)p where rownum<= 4
)where rn>3;//要用伪列！！
最后附上我的sql代码：
create table page ( id varchar2(6) not null, username varchar2(20) not null, password varchar2(20) not null, info varchar2(200) default '大家好，很高兴认识你们！', constraints pk_id primary key(id) ); select * from page; delete page; drop table page; insert into page (id,username,password) values('000001','黄凯','111111'); insert into page (id,username,password,info) values('000002','肖旺','222222','我是JJ,林俊杰！'); insert into page (id,username,password) values('000003','申俊杰','qqqq'); insert into page (id,username,password,info) values('000004','杨小宇','444444','我班长！'); insert into page (id,username,password) values('000005','许世群','xxxxxx'); insert into page (id,username,password,info) values('000006','王东宝','666666','我宝爷！'); insert into page (id,username,password,info) values('000007','admin','admin','我管理员！'); insert into page (id,username,password,info) values('000008','刘鹏','666666','我爱游戏！'); insert into page (id,username,password,info) values('000009','刘永军','liu666','我少夜哈哈！'); update page set info='我是少爷哈哈！！' where id='000009'; select rownum,p.* from page p where rownum between 1 and 4; select count(id) as a from page; select count(id) as count_row from page; select * from ( select rownum rn,p.* from (select * from page order by id )p where rownum<= 4 )where rn>3; select * from ( select rownum rn,p.* from (select * from page )p where rownum<= 6 )where rn>3
1.在这里我们的任务完成了，在如果有什么问题可以联系我QQ：541817557（一起交流）。
2.同时我也希望其他人能提供给我些分层的意见。
3.同时这里没有关闭数据库的连接，在自己的测试中出现过一个打开游标数超过最大值的问题，大家能说说是不是没关闭数据库连接的问题？我是重新启动下tomcat后就可以了的，请指教！谢谢！
4.祝大家工作顺利，学业有成！谢谢阅读！
