Access数据库怎么实现分页显示

Question

给你一段代码：我实现了的：
    /**Access数据库连接*/
    String sDBDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
    String url = "jdbc:odbc:gogoSource";
    Connection con = null;
    Statement Stmt = null;
    Connection tempCon = null;
    Statement tempStmt = null;    
    ResultSet rsFirst = null;
    ResultSet rsTotal = null;       
    ResultSet rs = null;
    String sql = null;
    
    try
    {
        Class.forName(sDBDriver);
    }
    catch(java.lang.ClassNotFoundException e)
    {
        out.println("数据库连接初始化错误"+e.getMessage());
    }               
           
    try
    {
        if (addFlag.equals("Yes"))
        {
            con = DriverManager.getConnection(url,"","");
            Stmt = con.createStatement();
            sql = "select Id from Tb_monthCollection where collYearMonth=(DateValue('"+endDate+"')-1)";
            rsFirst = Stmt.executeQuery(sql);      
            
            if(rsFirst.next()) 
            {
                tempCon = DriverManager.getConnection(url,"","");
                tempStmt = tempCon.createStatement();            
                sql = "delete from Tb_monthCollection where collYearMonth=(DateValue('"+endDate+"')-1)";       
                tempStmt.executeUpdate(sql);
                tempStmt.close();
                tempCon.close();
            }
            
            rsFirst.close();
            Stmt.close();
            con.close();
            
            tempCon = DriverManager.getConnection(url,"","");
            tempStmt = tempCon.createStatement();  
            sql = "Insert into Tb_monthCollection (collYearMonth,payOutKindId,MonthCharge,re) ";
            sql = sql + " select (DateValue('"+endDate+"')-1), payOutKindId,sum(Charge),'"+re+"'";
            sql = sql + " from Tb_payOut where occurDate>=DateValue('"+startDate+"') and occurDate<DateValue('"+endDate+"')" ;       
            sql = sql + " group by payOutKindId ";
            tempStmt.executeUpdate(sql);
            tempStmt.close();   
            tempCon.close();               
        }
    }
    catch(SQLException e)    
    {
        out.println("出错信息："+e.toString());
    }            
%>
<table border="0" align="center">
<tr>
<td>
<%
        try
        {
            con = DriverManager.getConnection(url,"","");
            Stmt = con.createStatement();
            if (addFlag.equals("No"))
            {  
                sql = "select a.Id,a.collYearMonth,b.payOutKindName,a.MonthCharge,a.re from Tb_monthCollection a,tb_payOutKind b where a.payOutKindId=b.payOutKindId";
                sql = sql + " and a.collYearMonth>=DateValue('"+startDate+"') and a.collYearMonth<DateValue('"+endDate+"') and a.payOutKindId like '%"+payOutKindId+"%' order by a.collYearMonth desc";  
            }
            else
            {
                sql = "select a.Id,a.collYearMonth,b.payOutKindName,a.MonthCharge,a.re from Tb_monthCollection a,tb_payOutKind b where a.payOutKindId=b.payOutKindId order by a.collYearMonth desc";
            }        
            
            rsTotal = Stmt.executeQuery(sql);
            //System.out.println(sql);
            int count=0;
            int pagecount=0;
            int pages=0;
            int pagesize=10;
            float totalCout = 0;               //总花费额
            while(rsTotal.next())
            {
             count++;
             totalCout = totalCout + rsTotal.getFloat(4);
            }
            rsTotal.close();
            if(count%pagesize==0)
                pagecount = count/pagesize;
            else 
                pagecount = count/pagesize+1;
            
            String strpages = request.getParameter("pages");
            //out.println("strpages: "+strpages);
            if((strpages==null)||strpages.equals(""))
                pages=1;
            else
            {
                pages = new Integer(strpages).intValue();
                if(pages<=0)
                    pages=1;
            }
            
            rs = Stmt.executeQuery(sql); 
            int i;
            for(i=1;i<=(pages-1)*pagesize;i++)
            {
                rs.next();
            }       
%>
<form name="submit_frm" method="post" target="main" >
<table border="0" align="center">
<tr>
    <td>
        <table border="0" align="center" style="font-size:10pt">
        <tr>
        <td align="center">
        <font style="font-size:18" color="green">公司支出汇总表</font>
        <br><br>
        <table id="tbl_select" border="1" cellspacing="0" cellpadding="0" style="font-size:10pt" algin="center">
        <tr align="center" bgcolor="#D4D0C8">
            <!--<td>编号</td>-->
            <td width="80px">汇总年月</td>
            <td width="80px">支出类型</td>
            <td width="140px">月总费用</td>
            <td width="200px">备注</td>
        </tr>
<%        
            for(i=1;i<=pagesize;i++)
            {    
                if(rs.next())
                {
%>
        <tr onClick="javascript:selectedRow(<%=i%>)">
            <!--<td><%=rs.getInt(1)%></td>-->
            <td align="center"><%=(rs.getString(2)).substring(0,7)%>&nbsp;</td>
            <td><%=rs.getString(3)%>&nbsp;</td>
            <td align="right"><%=rs.getFloat(4)%>&nbsp;</td>            
            <td><%=rs.getString(5)%>&nbsp;</td>
        </tr>
<%            
                }
            }
%>
        <tr >        
            <td colspan="2" align="right">总花费额：</td>
            <td colspan="2" align="left"><%=totalCout%>元</td>
        </tr>
        </table>

Answer 1

:)没有过试...Access中没有rownum ,LIMIT 是mysql的吧?还是微软的也有? access好像是支持top的吧

Answer 2

access支持top分页：
<html>
<head>
<%@ page contentType="text/html;charset=gb2312" %>
<title>会员管理</title>
<link rel=stylesheet href="../style.css" type="text/css">
<script LANGUAGE="javascript">
function newwin(url) {
  var newwin=window.open(url,"newwin","toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=yes,width=600,height=450");
  newwin.focus();
  return false;
}
</script>
</head>
<body>
<script LANGUAGE="javascript">
function submit10()
{
self.location.replace("admin_user.jsp")
}
</script>
<jsp:include page="../top.jsp" flush="true"/>
<center><p><h4><font color="blue">会员管理</font></h4></p>
<%@ page language="java" import="java.sql.*" %>
<jsp:useBean id="adminBean" scope="page" class="firm.firm" />
<%
//指定行数
int pageLine=10;
int totalRec=0;
int totalSub=0;
int intPage=1;
int i;
if (request.getParameter("page")!=null)
intPage=Integer.parseInt(request.getParameter("page"));try{
ResultSet countrs=null;//取得总记录数
countrs=adminBean.executeQuery("select count(*) as cnt from user where name<>'administrator'");
if (countrs.next())
 totalRec=countrs.getInt("cnt");
countrs.close();
adminBean.closeStmt();}
catch(Exception e){
   e.printStackTrace();
}//取得总页数
int intPageCount=0;
intPageCount = (totalRec+pageLine-1) / pageLine;
%>
<%
ResultSet RS = adminBean.executeQuery("SELECT * FROM user  where name<>'administrator' order by id desc");
String name;
String userid;%>
<table border='1' cellspacing='0' width='500' bgcolor='#d7e3b9' bordercolorlight='#green' bordercolordark='#ECF5FF'>
<tr bgcolor='#c8cc98' align='center'>
   <td width='8%'>ID</td>
   <td width='14%'>会员名</td>
   <td width='8%' >删除</td>
</tr>
<%
if (intPageCount>0)
{
           for(i=1;i<=(intPage-1)*pageLine;i++)
RS.next();
   for(i=1;i<=pageLine;i++) {
if (RS.next()) {                        name=RS.getString("name");
                        userid=RS.getString("id");
%>
<tr>
   <td width='8%' align='center'><%=userid%></td>
   <td width='14%' align='center'><%=name%></td>
   <td width='8%' align='center'><a href="delete.jsp?id=<%=userid%>" onClick="return newwin(this.href);">删除</a></td>
</tr>
<%
  }
}
RS.close();
}
%>
<!--以下用于分页显示 -->
<%
 out.print("<tr>");  
 if (intPageCount*pageLine<totalRec) 
   intPageCount++;
 if (intPage>intPageCount )
intPage=intPageCount;
 if (intPage < 1 )
intPage=1;
out.print("<form method='POST'  name=fPageNum  action='admin_user.jsp'>");
out.print("<p align='left'>&gt;&gt;分页&nbsp;");
  
   if (intPage<2)
     out.print("<font color='999966'>首页 上一页</font>&nbsp;");
   else{   
   out.print("<a href='admin_user.jsp?page=1'>首页</a>&nbsp;");   
     out.print("<a href='admin_user.jsp?page=" + (intPage-1) + "'>上一页</a>&nbsp;"   );
     }
  
     
   if( intPage-intPageCount>=0 )
     out.print("<font color='999966'>下一页 尾页</font>"   );
   else{   
  
     out.print("<a href='admin_user.jsp?page=" + (intPage+1)+ "'>下一页</a> <a href='admin_user.jsp?page=" + intPageCount + "'>尾页</a>");
     }
 
        out.print("&nbsp;页次：<strong><font color=red>"+intPage+"</font>/"+intPageCount+"</strong>页 "   );
    out.print("&nbsp;共<b>"+totalRec+"</b>条记录 <b>"+pageLine+"</b>条/页 "   );
    out.print(" 转到第<input type='text' name='page' size=2 maxlength=10 class=smallInput value="+intPage+">");   
    out.print("页<input class=buttonface type='submit'  value='GO'  name='cndok'></span></p></form>"  );  out.print("</td>");
out.print("</tr>"); 

%>
</table></p>
<a href="javascript:window.close();">[关闭]</a>
</center>
<jsp:include page="../bottom.jsp" flush="true"/>
</body>
</html>