以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>