Java中数据库自动分页完整实例

来源：互联网发布：晶晶改车软件最新版编辑：程序博客网时间：2024/06/06 08:37

一、数据库中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>首页</>在浏览器里就会首页这么一个超连接传参的说明
//我在类里都有明确的注释后头看看吧
db.closed();
}
}

%>

数据库连接部分

MsManger.java

package com.pagination.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MsManager
{
private Connection con;
private PreparedStatement pstm;
private String user = "sa";
private String password = "";
private String className = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private String url = "jdbc:sqlserver://localhost:1433;DatabaseName=db_register";

/** 构造方法，在该方法中加载数据库驱动 */
ResultSet rs = null;
public MsManager()
{
try
{
Class.forName(className);
}
catch (ClassNotFoundException e)
{
System.out.println("加载数据库驱动失败！");
e.printStackTrace();
}
}

/** 创建数据库连接 */
public Connection getCon()
{
if (con == null)
{
try
{
con = DriverManager.getConnection(url, user, password);
}
catch (SQLException e)
{
System.out.println("创建数据库连接失败！");
con = null;
e.printStackTrace();
}
}
return con;
}

/**
*@功能：对数据库进行增、删、改、查操作
*@参数：sql为SQL语句；params为Object数组，里面存储的是为sql表示的SQL语句中"?"占位符赋值的数据
*/
public void doPstm(String sql)
{
if (sql != null && !sql.equals(""))
{

getCon();
if (con != null)
{
try
{
System.out.println(sql);
pstm = con.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);

pstm.execute();
}
catch (SQLException e)
{
System.out.println("doPstm()方法出错！");
e.printStackTrace();
}
}
}
}

public ResultSet query(String sql)
{
if (sql != null && !sql.equals(""))
{

getCon();
if (con != null)
{
try
{
Statement stmt=con.createStatement();
rs = stmt.executeQuery(sql);
}
catch (SQLException e)
{
System.out.println("doPstm()方法出错！");
e.printStackTrace();
}
}
}
return rs;

}

/**
* @功能：获取调用doPstm()方法执行查询操作后返回的ResultSet结果集
* @返回值：ResultSet
* @throws SQLException
*/
public ResultSet getRs() throws SQLException
{
rs = pstm.getResultSet();
return rs;
}

/**
* @功能：获取调用doPstm()方法执行更新操作后返回影响的记录数
* @返回值：int
* @throws SQLException
*/
public int getCount() throws SQLException
{
return pstm.getUpdateCount();
}

/**
* @功能：释放PrepareStatement对象与Connection对象
*/
public void closed()
{
try
{
if (pstm != null)
pstm.close();
}
catch (SQLException e)
{
System.out.println("关闭pstm对象失败！");
e.printStackTrace();
}
try
{
if (con != null)
{
con.close();
}
}
catch (SQLException e)
{
System.out.println("关闭con对象失败！");
e.printStackTrace();
}
}
}

分页实现代码

panination.java

package com.pagination.servlet;

import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;

import com.pagination.db.MsManager;

public class Pagination extends HttpServlet
{

/** Creates a new instance of Pagination */
public Pagination()
{
}

private int pageSize = 0;//每页显示数据条数
private String table = "";//要操作的表名

private void setTable(String table)
{
this.table = table;
}

private String getTable()
{
return table;
}

/**
*首页参数说明 request 传入jsp内置的request对象 current 当前页例：比如你现在在index.jsp页那你就
* XX.top(request,"index.jsp"),当然你也可以懒的写法XX.top(request,"")这么写也可以实现相同的功能
*/
public String top(HttpServletRequest request, String current)
{
request.setAttribute("page", "1");
return "<a href=" + current + "?page="
+ (String) request.getAttribute("page") + ">首页</a> ";
}

/**
*上一页参数说明 request 传入jsp内置的request对象 current 当前页例：比如你现在在index.jsp页那你就
* XX.shang(request,"index.jsp"),当然你也可以懒的写法XX.shang(request,"")
* 这么写也可以实现相同的功能， page 这个参数就是你当前的页码，你可以在你的pagination.jsp 这么调用 String
* pages=request.getParameter("page") ，再把pages传到page 里就一切 OK 了
*/
public String shang(HttpServletRequest request, String current, String page)
{
int iPage = Integer.parseInt(page);
if (iPage > 0)
iPage--;
String strPage = "" + iPage;
request.setAttribute("page", strPage);
return "<a href=" + current + "?page="
+ (String) request.getAttribute("page") + ">上一页</a> ";
}

/**
*下一页参数说明 request 传入jsp内置的request对象 current 当前页例：比如你现在在index.jsp页那你就
* XX.xia(request,"index.jsp"),当然你也可以懒的写法XX.xia(request,"")这么写也可以实现相同的功能
* page 这个参数就是你当前的页码你可以在你的pagination.jsp 这么调用 String
* pages=request.getParameter("page") 再把pages传到page 里就一切 OK 了
*/
public String xia(HttpServletRequest request, String current, String page)
{
String strCount = null;
MsManager ms = new MsManager();
ms.doPstm("select count(*) as num from " + this.getTable());
ResultSet rs = null;
try
{
rs = ms.getRs();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}

try
{
if (rs.next())
{
strCount = rs.getString("num");
}
}
catch (SQLException ex)
{
ex.printStackTrace();
}
int iCount = 0;
int iLastPage = 0;
if (strCount != null)
{
iCount = Integer.parseInt(strCount);
iLastPage = iCount / this.getPageSize();
}

int iPage = Integer.parseInt(page);
if (iPage < iLastPage)
iPage++;
String strPage = "" + iPage;
request.setAttribute("page", strPage);
ms.closed();
return "<a href=" + current + "?page="
+ (String) request.getAttribute("page") + ">下一页</a> ";
}

/**
*尾页参数说明 request 传入jsp内置的request对象 current 当前页例：比如你现在在index.jsp页那你就
* XX.bottom(request,"index.jsp"),当然你也可以懒的写法XX.bottom(request,"")
* 这么写也可以实现相同的功能
*/
public String bottom(HttpServletRequest request, String current)
{
String strCount = null;
MsManager ms = new MsManager();
ms.doPstm("select count(*) as num from " + this.getTable());
ResultSet rs = null;
try
{
rs = ms.getRs();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
try
{
if (rs.next())
{
strCount = rs.getString("num");
}
}
catch (SQLException ex)
{
ex.printStackTrace();
}
int iCount = 0;
int iLastPage = 0;
if (strCount != null)
{
iCount = Integer.parseInt(strCount);
if(0 == iCount%this.getPageSize())
{
iLastPage = iLastPage / this.getPageSize();
}
else
{
iLastPage = iLastPage / this.getPageSize() + 1;
}

}
String strLastPage = "" + iLastPage;
request.setAttribute("info", strLastPage);
ms.closed();
return "<a href=" + current + "?page="
+ (String) request.getAttribute("page") + ">尾页</a> ";
}

private void setPageSize(int pageSize)
{
this.pageSize = pageSize;
}

private int getPageSize()
{
return this.pageSize;
}

/**
*返回sql语句参数说明 pageSize 这个参数是指你每页想显示几条数据比如说把10 传进去就会返回10条记录的结果集 tableName
* 这个没有说明好说的啦就是把你想要操作的表的名字传进来就行了 page 这个参数就是你当前的页码你可以在你的pagination.jsp 这么调用
* String pages=request.getParameter("page") 再把pages传到page里就一切 OK 了
*/
public String getSqlASC(int pageSize, String tableName, String page)
{
this.setTable(tableName);
this.setPageSize(pageSize);
int i = 0;
int temp = 0;
if (page != null && !page.equals(""))
{
i = Integer.parseInt(page);
temp = pageSize * (i - 1);
}
return "select top " + pageSize + " * from " + tableName
+ " where (id not in (select top " + temp + " id from "
+ tableName + " order by id)) order by id ";
}

/**
*返回sql语句按照你的主键id值倒序输出参数说明 pageSize 这个参数是指你每页想显示几条数据比如说把10
* 传进去就会返回10条记录的结果集 tableName 这个没有说明好说的啦就是把你想要操作的表的名字传进来就行了 page
* 这个参数就是你当前的页码你可以在你的pagination.jsp 这么调用 String pages=request.getParameter("page")
* 再把pages传到page 里就一切 OK 了
*/
public String getSqlDESC(int pageSize, String tableName, String page)
{
this.setPageSize(pageSize);
this.setTable(tableName);
int i = 0;
int temp = 0;
if (page != null && !page.equals(""))
{
i = Integer.parseInt(page);
temp = pageSize * (i - 1);
}
return "select top " + pageSize + " * from " + tableName
+ " where (id not in (select top " + temp + " id from "
+ tableName + " order by id desc)) order by id desc";
}

}