实现分页显示
以SQLServer2000为数据库服务器,在test数据库下建立一个数据表Mdatas。
新建一JAVA类InsertDatas.java,插入一些测试数据。
package com.qixin.chpt13; import java.sql.*; public class InsertDatas { public static void main(String[] args) { Connection conn = null; Statement stmt = null; ResultSet rs = null; String url = "jdbc:sqlserver://localhost:1433;databasename=test"; String username = "sa"; String password = ""; try { conn = DriverManager.getConnection(url,username,password); stmt = conn.createStatement(); for (int i = 0; i < 105; i++) { stmt.executeUpdate("insert into Mdatas values(" + i + ",'data_" + i + "')"); } stmt.close(); conn.close(); } catch (SQLException ex) { ex.printStackTrace(System.out); } } }
需要在页面中使用特定功能的时候,建议将业务逻辑封装在一个JavaBean中,然后在JSP页面中使用该JavaBean的方式实现功能。
package com.qixin.chpt13; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class PageDivBean { String tabName = null; ResultSet rs = null; Connection con = null; Statement stmt = null; int pageRecord = 10; int requestPage = 1; int totalPages = 1; public int getTotalPages() { int totalRecords = getTotalRecords(); if (totalRecords % pageRecord == 0) { totalPages = totalRecords / pageRecord; } else { totalPages = totalRecords / pageRecord + 1; } return totalPages; } public int getPageRecord() { return pageRecord; } public void setPageRecord(int pageRecord) { this.pageRecord = pageRecord; } public int getRequestPage() { return requestPage; } public void setRequestPage(int requestPage) { this.requestPage = requestPage; } public ResultSet getRs() { int requestRecord = requestPage * pageRecord; String sql = "select A.* from (select top " + pageRecord + " B.* from " + "(select top " + requestRecord + " * from " + tabName + " order by id) AS B " + "order by B.id desc) AS A order by A.id"; try { rs = stmt.executeQuery(sql); } catch (SQLException e) { e.printStackTrace(System.out); } return rs; } public void setRs(ResultSet rs) { this.rs = rs; } public int getTotalRecords() { int totalRecords = 0; try { ResultSet rs = stmt.executeQuery("select count(*) from " + tabName); rs.next(); totalRecords = rs.getInt(1); } catch (Exception e) { e.printStackTrace(System.out); } return totalRecords; } public String getTabName() { return tabName; } public void setTabName(String tabName) { this.tabName = tabName; } public Connection getCon() { return con; } public void setCon(Connection con) { this.con = con; try { stmt = con.createStatement(); } catch (SQLException e) { e.printStackTrace(System.out); } } }
下面,实现分页显示算法将Mdatas表中的这些记录在dataPagesBean.jsp页面中显示出来。每页显示10条记录。
分页显示的MSSQL方法:
方法一: select top 10 * from Mdatas where id not in (select top 80 id from Mdatas order by id) order by id 方法二: select A.* from (select top 10 B.* from (select top 90 * from Mdatas order by id) AS B order by B.id desc) AS A order by A.id 比较效率:解答二效率更高。
<%@ page contentType="text/html; charset=gb2312" %> <%@ page import="java.sql.*" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=gb2312"> <title>分页显示数据</title> </head> <body> <form name="form1" method="post" action=""> <table width="30%" border="0" align="center"> <tr> <td><div align="center"> 当前页数据显示如下:</div> </td> <td> </td> </tr> <tr> <td><div align="center">id</div></td> <td><div align="center">data</div></td> </tr> <% String rp = request.getParameter("rp"); if (rp == null || rp.equals("")) { rp = "1"; } int requestPage = Integer.parseInt(rp); %> <jsp:useBean id="pd" scope="request" class="com.qixin.chpt13.PageDivBean"> <jsp:setProperty name="pd" property="requestPage" value="<%= requestPage%>"/> </jsp:useBean> <% try { String url = "jdbc:sqlserver://localhost:1433;databasename=test"; Connection con = DriverManager.getConnection(url, "sa", ""); pd.setCon(con); pd.setTabName("Mdatas"); pd.setPageRecord(10); ResultSet rs = pd.getRs(); while (rs.next()) { %> <tr> <td><div align="center"><%=rs.getString(1)%></div></td> <td><div align="center"><%=rs.getString(2)%></div></td> </tr> <% } %> <tr> <td> </td> <td> <div align="right"> <select name="rp"> <% int totalpages = pd.getTotalPages(); for (int p = 1; p <= totalpages; p++) { %> <option value="<%=p%>" <% if (pd.getRequestPage() == p) { out.print("selected"); } %> ><%=p%></option> <% } %> </select> <input type="submit" name="Submit" value="go"> </div></td> </tr> <% } catch (Exception e) { out.print("有错误发生了……"); } %> </table> </form> </body> </html>