jsp数据库分页技术

数据库分页分为2种：真分页和假分页,这里以mysql作为数据库,库名：login  表名：users。

页面分量:

  pageSize:每页显示的记录数

  currentPage:当前页码（从1开始计算）

  recoderCount:记录总条数(通过“select count(*) from users”)

  pageCount:总页数(pageCount=(recoderCount-1)/pageSize+1)

1,假分页：通过执行sql语句获得的结果集，获得所有记录，sqlSelect="select * from users";

代码如下：

  1 <%@ page language="java" contentType="text/html; charset=gbk"
  2     pageEncoding="gbk"%>
  3 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
  4 
  5 <%@page import="java.sql.Connection"%>
  6 <%@page import="java.sql.Statement"%>
  7 <%@page import="java.sql.ResultSet"%>
  8 <%@page import="java.sql.DriverManager"%>
  9 <%@page import="java.sql.SQLException"%><html>
 10 <head>
 11 <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
 12 <title>Insert title here</title>
 13 </head>
 14 <body>
 15 <%
 16     Connection conn=null;
 17     Statement stmt=null;
 18     ResultSet rs=null;
 19     String driverClass="com.mysql.jdbc.Driver";
 20     String url="jdbc:mysql://127.0.0.1:3306/login";
 21     String dbUser="root";
 22     String dbPassword="root";
 23     String sql="select * from users";
 24     int pageSize=3;
 25     int recoderCount=0;
 26     int pageCount=0;
 27     int currentPage=1;
 28     String current=request.getParameter("currentPage");
 29     try{
 30        if(current!=null){
 31           currentPage=Integer.parseInt(current);
 32        }
 33        }catch(NumberFormatException e){
 34            e.getStackTrace();
 35        }
 36     try{
 37        Class.forName(driverClass);
 38        conn=DriverManager.getConnection(url,dbUser,dbPassword); 
 39        stmt=conn.createStatement();
 40        rs=stmt.executeQuery(sql);
 41        rs.last();
 42        recoderCount=rs.getRow();
 43        pageCount=(recoderCount-1)/pageSize+1;
 44        rs.first();
 45        if(currentPage<=1){
 46            currentPage=1;
 47            rs.beforeFirst();
 48        }
 49        else if(currentPage>=pageCount){
 50            currentPage=pageCount;
 51            rs.absolute((pageCount-1)*pageSize);
 52        }
 53        else 
 54            rs.absolute((currentPage-1)*pageSize);
 55        
 56        %>
 57         <table border=2>
 58           <tr>
 59             <td>ID</td>
 60             <td>Username</td>
 61             <td>Password</td>
 62           </tr>
 63         <%
 64           int i=0;
 65           while(rs.next()&&i<pageSize){
 66         %>
 67             <tr>
 68               <td><%=rs.getInt(1)%></td>
 69               <td><%=rs.getString(2)%></td>
 70               <td><%=rs.getString(3) %></td>
 71             </tr>
 72         <%        
 73          i++;
 74           }
 75         %>
 76          </table>
 77           <a href=test2.jsp?currentPage=1>首页</a>&nbsp;
 78           <a href=test2.jsp?currentPage=<%=currentPage-1 %>>上一页</a>&nbsp;
 79           <a href=test2.jsp?currentPage=<%=currentPage+1 %>>下一页</a>&nbsp;
 80           <a href=test2.jsp?currentPage=<%=pageCount %>>尾页</a>
 81        
 82        <% 
 83        }catch(SQLException e){
 84            e.getStackTrace();
 85        }catch(ClassNotFoundException e){
 86            e.getStackTrace();
 87        }
 88        finally{
 89            try{
 90                if(rs!=null){
 91                    rs.close();
 92                    rs=null;
 93                }
 94                if(stmt!=null){
 95                    stmt.close();
 96                    stmt=null;
 97                }
 98                if(conn!=null){
 99                    conn.close();
100                    conn=null;
101                }
102            }catch(SQLException e){
103                e.getStackTrace();
104            }
105        }
106        
107 
108 %>
109 </body>
110 </html>

2.真分页：只从数据库读取当前页面需要的记录放入结果集。sqlSelect="select * from users limit "+((currentPage-1)*pageSize)+","+pageSize;

我在真分页效果的基础上又加上了数字跳转及关键字查询的方式.代码如下：

<%@ page language="java" contentType="text/html; charset=gbk"
    pageEncoding="gbk"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.SQLException"%><html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
    <%
      int pageSize=3;
      int recoderCount=0;
      int pageCount=0;
      int currentPage=1;
    %>
   <table border=1>
     <tr>
        <td>ID</td>
        <td>Username</td>
        <td>Password</td>
     </tr>
     <%
      String driverClass="com.mysql.jdbc.Driver";
      String url="jdbc:mysql://127.0.0.1:3306/login";
      String dbUser="root";
      String dbPassword="root";
      Connection conn=null;
      PreparedStatement pstmt=null;
      ResultSet rs=null;
      String sqlCount="select count(*) from users";
      try{
          Class.forName(driverClass);
          conn=DriverManager.getConnection(url,dbUser,dbPassword);
          pstmt=conn.prepareStatement(sqlCount);
          rs=pstmt.executeQuery();
          if(rs.next())
              recoderCount=rs.getInt(1);
   //       System.out.print(recoderCount);
          pageCount=(recoderCount-1)/pageSize+1;
          try{
              if(request.getParameter("currentPage")!=null)
              currentPage=Integer.parseInt(request.getParameter("currentPage"));
          }catch(NumberFormatException e){
              e.printStackTrace();
          }
          if(currentPage<1)
              currentPage=1;
          if(currentPage>pageCount)
              currentPage=pageCount;
          String sqlSelect="select * from users limit "+(currentPage-1)*pageSize+","+pageSize;
          pstmt=conn.prepareStatement(sqlSelect);
          rs=pstmt.executeQuery();
          while(rs.next()){
        
        %>
             <tr>
                <td><%=rs.getInt(1)%></td>
                <td><%=rs.getString(2)%></td>
                <td><%=rs.getString(3)%></td>
             </tr>
        <%
          }
      }catch(ClassNotFoundException e){
          e.printStackTrace();
      }catch(SQLException e){
          e.printStackTrace();
      }finally{
          try{
             if(rs!=null){
                 rs.close();
                 rs=null;
             } 
             if(pstmt!=null){
                 pstmt.close();
                 pstmt=null;
             }
             if(conn!=null){
                 conn.close();
                 conn=null;
             }
          }catch(SQLException e){
              e.printStackTrace();
          }
      }
     %>
   </table><br/>
   <a href=truePaging2.jsp?currentPage=1>首页</a>&nbsp;
   <a href=truePaging2.jsp?currentPage=<%=currentPage-1 %>>上一页</a>&nbsp;
   <%
     for(int i=currentPage-4;i<=currentPage+5;i++){
         if(i<1||i>pageCount)
             continue;
         %>
           <a href=truePaging2.jsp?currentPage=<%=i%>><%=i %></a>&nbsp;
         <%
        
     }
   %>
   <a href=truePaging2.jsp?currentPage=<%=currentPage+1 %>>下一页</a>&nbsp;
   <a href=truePaging2.jsp?currentPage=<%=pageCount %>>尾页</a>&nbsp;
   <form action="truePaging2.jsp" method="post">
              跳到第<input type="text" name="currentPage" style="width:20px;height:20px">页&nbsp;
     <input type="submit" value="Go">           
   </form>
</body>
</html>

其实，仅仅从sqlSelect语句就可以看出来，真分页效率更高，用多少取多少。而假分页是一次把所有数据全部取出来。当数据库数据量很大时，真分页的优势就更加明显了。

posted @ 2012-10-18 20:02  Felicite  阅读(219)  评论(0)    收藏  举报