Oracle 分页

来源：互联网发布：圣元包邮淘宝网折编辑：程序博客网时间：2024/05/02 22:08

<%@ page language="java" import="java.util.*" pageEncoding="GBK"%>
<%@ page language="java" import="wfc.service.database.DB"%>
<%@ page import="java.sql.Connection"%>
<%@ page import="java.sql.ResultSet"%>
<%@ page import="java.sql.Statement"%>
<%@ include file="./../include/stat_common_head.inc"%>
<%
//分页开始
String strKey=session.getId();

String beginTime=(String)session.getAttribute(strKey+"beginTime");
String endTime=(String)session.getAttribute(strKey+"endTime");

String staType = request.getParameter("staType");//统计类型：颁发、注销、作废、恢复注销、遗失
String passTarget = request.getParameter("passTarget");//证件类型

String selectSql="select P.ST_PASS_CODE gb1,P.ST_NAME gb2,(select ST_NAME_SHORT from T_PASS_ISSUE_DEPT where ST_PASS_ISSUE_DEPT_CODE = P.ST_ISSUE_DEPT_ID) gb3,to_char(P.DT_ISSUE,'yyyy-MM-dd') gb4,to_char(P.DT_EXPIRE,'yyyy-MM-dd') gb5,to_char(P.DT_NULLIFY,'yyyy-MM-dd') gb6,p.ST_COMMENT gb7";
String fromSql=" from T_PASS P";

String whereSql=" where P.DT_ISSUE >= to_date('" + beginTime + "', 'yyyy-MM-dd') and P.DT_ISSUE <= to_date('" + endTime + "', 'yyyy-MM-dd') and P.ST_ISSUE_DEPT_ID = '24'";

if(staType.equals("颁发")){
   whereSql += " and P.ST_PASS_TYPE='" + passTarget + "'";
}else if(staType.equals("注销")){
   whereSql=" where P.DT_NULLIFY >= to_date('" + beginTime + "', 'yyyy-MM-dd') and P.DT_NULLIFY <= to_date('" + endTime + "', 'yyyy-MM-dd') and P.ST_ISSUE_DEPT_ID = '24'";
   whereSql += " and P.ST_PASS_TYPE='" + passTarget + "' and P.ST_STATE ='注销'";
}else{
  whereSql=" where P.DT_NULLIFY >= to_date('" + beginTime + "', 'yyyy-MM-dd') and P.DT_NULLIFY <= to_date('" + endTime + "', 'yyyy-MM-dd') and P.ST_ISSUE_DEPT_ID = '24'";
  whereSql += " and P.ST_PASS_TYPE='" + passTarget + "' and P.ST_STATE ='注销' and P.ST_NULLIFY_TYPE ='" + staType + "'";
}
String select=selectSql + fromSql + whereSql;
//List<StatReport> reportList = StatReportHelper.getReportList(select);
%>
<%
   int pageSize = 23;//每页显示的记录数
   int pageCount = 0;//总页数
   Connection con;
   Statement sql;
   ResultSet rs;
      try {
        con = DB.getConnection();
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
        request.setCharacterEncoding("GB2312");
        rs = stmt.executeQuery(select);
          rs.last();
        int rowCount = rs.getRow(); //获取记录总数.
        pageCount = (rowCount % pageSize == 0) ? (rowCount / pageSize ) : (rowCount / pageSize +1);
        int showPage = 1;//当前页
     String goToPage = request.getParameter("showPage");//取得用户所指定的页
        if (goToPage == null){
           goToPage = "1";
        }
       try{
            showPage = Integer.parseInt(goToPage);//转换成整形
        }
       catch (NumberFormatException ex){
            showPage = 1;
        }

          //当前页小于等于第一页则按第一页算如果当前页大于等于总页数则为最后页
       if(showPage <=1){
           showPage = 1;
        }
        else if(showPage >= pageCount){
           showPage = pageCount;
        }

        int posion = (showPage -1 ) * pageSize + 1;//游标的位置 (当前页 - 1) * 页面大小 + 1

         rs.absolute(posion);//设置游标的位置

%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
  <link href="../../../style/css.css" rel="stylesheet" type="text/css" />
  <script type="text/javascript" src="../../../../share/jquery/jquery.js"></script>
  <title>分类统计明细</title>
</head>
<body>
<div id="content">
<center>
  <div style="margin:0 auto;width:800px;text-align:center;padding-top:10px;height:30px !important;font-size:18px;font-weight:bold">分类统计明细</div>
  <div style="margin:0 auto;width:800px;text-align:center;padding-top:10px;height:30px !important;font-size:12px;"><%=beginTime%> 到 <%=endTime%></div>
  <table width="98%" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#d0d7da" id="table_01" >
      <tr>
        <td width="8%" nowrap align="center">证件号码</td>
        <td width="5%" nowrap align="center">姓名</td>
        <td width="10%" nowrap align="center">发证机关</td>
        <td width="10%" nowrap align="center">发证日期</td>
        <td width="10%" nowrap align="center">有效期</td>
        <td width="10%" nowrap align="center">注销日期</td>
        <td width="10%" nowrap align="center">备注</td>
      </tr>
      <%
        int i =0;
        //循环显示表中的数据 pageSize(每页所显示的记录)
         //rs.isAfterLast() 游标是否在最后一行之后说明后面已经没记录
         while(i<pageSize && !rs.isAfterLast()){
       %>
      <tr>
       <%
         for(int j=0;j<7;j++){
      %>
      <td width="10%" nowrap align="center"><%=rs.getString(j+1)==null?"":rs.getString(j+1)%></td>
     <%
       }
     %>
      </tr>
     <%
         rs.next();
         i++;
        }

     %>


<form action="pass_pass_detail.jsp" method="get">
<input type="hidden" name="staType" value='<%=staType %>'/>
<input type="hidden" name="passTarget" value='<%=passTarget %>'/>
<table width="98%" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#d0d7da" id="table_01" >
   <tr>
    <td width="8%" align="center">当前第<label style="color:red"> <%=showPage%> </label>页</td>
    <td align="center">
     <a href="pass_pass_detail.jsp?showPage=1&staType=<%=staType%>&passTarget=<%=passTarget%>">首页</a>
      <a href="pass_pass_detail.jsp?showPage=<%=showPage-1%>&staType=<%=staType%>&passTarget=<%=passTarget%>">上一页</a>
     <a href="pass_pass_detail.jsp?showPage=<%=showPage+1%>&staType=<%=staType%>&passTarget=<%=passTarget%>">下一页</a>
     <a href="pass_pass_detail.jsp?showPage=<%=pageCount%>&staType=<%=staType%>&passTarget=<%=passTarget%>">尾页</a>
    </td>
    <td width="20%" align="center">转到
        <input type="text" name="showPage" size="4"/>
          <input type="submit" name="go" value="提交"/>页
    </td>
    <td width="10%" align="center">共<label style="color:red"> <%=pageCount%> </label>页</td>
    <td width="10%" align="center"> 共<label style="color:red"> <%=rowCount%> </label>条记录 </td>
   </tr>
</table>
</form>
</table>

</center>
</div>
<div style="width:98%;height:30px;padding-top:15px;text-align:center;margin:0px auto">
<input id="submitButton" type="button" value="关闭" onclick="window.close()" />
<input id="submitButton2" type="button" value="导出成Excel" onclick="" />
</div>
</body>
</html>
<%
//分页结束
   con.close() ;
}
catch(Exception e)
{
   out.println(e) ;
}
%>