oracle分页

Oracle分页实现

pageSize=3//暂时
分页实例：
select  s.*  from(select t.* ,rownum rn from  (select *  from users order by id) t  where rownum<=6)s where rn>=4;

带参：
select  s.*  from(select t.* ,rownum rn from  (select *  from users order by id) t  where rownum<=pageNow*pageSize)s where rn>=(pageNow-1)*pageSize+1;

servlet分页代码：

package com.xqh.view;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


public class ManagerUserView extends HttpServlet {


public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charSet=utf-8");
PrintWriter out = response.getWriter();
Connection ct=null;
PreparedStatement ps=null;
ResultSet rs=null;
int  pageNow=1;
int  pageCount=1;
int  pageSize=3;//暂时确定每页3条数据
int  rowCount=1;//查询数据库得出
//操作数据库，查询出结果
try{
//操作数据库1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2得到连接。
ct = DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl", "sysman", "orcl");
//分页处理
 
//得到要查询那一页
String pn=request.getParameter("pageNow");
if(pn!=null&&!"".equals(pn)){
pageNow=Integer.parseInt(pn);
}
//得到总共多少行
ps=ct.prepareStatement("select count(*) from users");
rs=ps.executeQuery();
rs.next();
rowCount=rs.getInt(1);
//得到总共多少页PageCount
if(rowCount%pageSize==0){
pageCount=rowCount/pageSize;
}else{
pageCount=rowCount/pageSize+1;
}

//分页详细SQL
String sql="select  s.*  from(select t.* ,rownum rn from  (select *  from users) t  where rownum<="
+ pageNow*pageSize
+ ")s where rn>="
+ (pageSize*(pageNow-1)+1);
ps=ct.prepareStatement(sql);
System.out.print(sql);
rs=ps.executeQuery();
 
out.println("<h1>管理用户</h1>");
out.println("<table border='1' bgcolor='cyan'>");
out.println("<tr><th>用户ID</th><th>用户姓名</th><th>Email</th><th>级别</th></tr>");
while(rs.next()){
out.println("<tr>"
+ "<td>"+rs.getInt(1)+"</td>"
+ "<td>"+rs.getString(2)+"</td>"
+ "<td>"+rs.getString(3)+"</td>"
+ "<td>"+rs.getInt(4)+"</td>"
+ "</tr>");
}
out.println("</table>");

//上一页 下一页的实现
if(pageNow==1){
out.println("上一页");
}else{
out.println("<a href='/userManager/ManagerUserView?pageNow="+(pageNow-1)+"'"+">上一页</a>");
}
if(pageNow==pageCount){
out.println("下一页");
}else{
out.print("<a href='/userManager/ManagerUserView?pageNow="+(pageNow+1)+"'"+">下一页</a>");
}

out.print("<a href='/userManager/ManagerUserView?pageNow="+(1)+"'"+">  首页</a>");
out.println("<a href='/userManager/ManagerUserView?pageNow="+(pageCount)+"'"+"> 尾页</a>");
out.println(pageNow+"/"+pageCount+"页");

}catch(Exception e){
e.printStackTrace();
}finally{
//关闭资源
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
rs=null;

if(ps!=null){
try{}catch(Exception e){
e.printStackTrace();
}
}else{
ps=null;
}

if(ct!=null){
try {
ct.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
ct=null;
}
}

public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doGet(request, response);
}

}