一、数据库中sql实现分页
--建立表
CREATE TABLE [TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
--出入数据
SET IDENTITY_INSERT TestTable ON
declare @i int
set @i=1
while @i<=20000
begin
insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
set @i=@i+1
end
SET IDENTITY_INSERT TestTable OFF
--分页方案一
/*
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id))
ORDER BY ID
*/
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 0 id
FROM TestTable
ORDER BY id))
ORDER BY ID
--分页方案二(利用ID大于多少和SELECT TOP分页)
/*
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id) AS T))
ORDER BY ID
*/
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 20 id
FROM TestTable
ORDER BY id) AS T))
ORDER BY ID
--此方法有bug,第一页的数据差不出来
--分页方案三:(利用SQL的游标存储过程分页)
create procedure XiaoZhengGe
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
二、web实例
前台代码:
pagination.jsp
<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<%@ page import="java.sql.ResultSet" %>
<jsp:useBean id="db" scope="page" class="com.pagination.db.MsManager">
</jsp:useBean>
<jsp:useBean id="jsp" scope="page" class="com.pagination.servlet.Pagination">
</jsp:useBean>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%
String s=request.getParameter("page");
if(s==null)
{
s="1";
}
else
{
int i = Integer.parseInt(s);
if(i <= 0)
{
out.print("数据不合法!");
}
else
{
//首先必须得用request.getParameter("page");获取到当前的页码
//可能有人会问了 page是怎么出来的啊?其实要是细心看那个类的朋友就
//就不会有这么的疑问了,没错我们的分页组件的动态生成的
ResultSet rs =db.query(jsp.getSqlASC(10,"TestTable",s));
//db. Query()这个方法是我封装好的,如果你没有的话 那么你就写一个方法
//传进去一个查询的sql语句返回一个ResultSet 很简单 这里不提了
// jsp.getSqlASC(10,"love",s) 这个才是终点和大家说的 这里面的10 代表是你
//想一页显示几条数据 “love”这则是你的biao的名字 比如说你在数据库的 test表
//test s则是当前的页码 因为我们的分页是自动生成的嘛 :)
while(rs.next())
{
out.println(rs.getString("ID"));
out.println(rs.getString("FirstName"));
out.println(rs.getString("LastName"));
out.println(rs.getString("Country"));
out.println(rs.getString("Note")+"<br>");
}
out.println(jsp.top(request,"")+jsp.shang(request,"",s)+jsp.xia(request,"",s)+jsp.bottom(request,""));
//这里给给大家解说一下 jsp.top(request,"") 这个方法,这个方法会返回一个String类型
//的字符串 <a href=?page=0>首页</>