Mysql数据库中的sql语句select * from tableName limit start,length,意思是从mysql中的tableName表取出start+1开始的length条数据，利用这个可以进行分页处理。
在javaBean中取出数据封装到java.util.Vector中，返回这个java.util.Vector，
在servlet中进行进行分页的具体处理，jsp文件则用于页面显示
这个javaBean用来封装当前页号和每页应该显示的记录数目
package thisBeans;
/**
* @author David.Qiu
*
*/
public class IntBean {
int currentPage;
int pageNum;
public IntBean(){
}
public void setCurrentPage(int currentPage){
this.currentPage=currentPage;
}
public int getCurrentPage(){
return this.currentPage;
}
public void setPageNum(int pageNum){
this.pageNum=pageNum;
}
public int getPageNum(){
return this.pageNum;
}
}
这个javaBean中的getArticles方法用于获取指定页的记录并封装程java.util.Vector中
package thisBeans;
import java.sql.*;
import java.util.*;
/**
* @author David.Qiu
* 留言信息bean
*/
public class ArticleBean {
/**
* 构造函数
*/
public ArticleBean() {
// TODO Auto-generated constructor stub
}
/**
* 添加留言
*/
public static boolean articlePublish(Article art){
boolean ok=false;
if(art==null||art.getTitle()==null||art.getContent()==null||art.getNickname()==null)
;
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
String sql=null;
try{
conn=DatabaseBean.getConnection();
stmt=conn.createStatement();
int articleId=DatabaseBean.getMaxId("article");
sql="insert into article(articleid,title,content,pubdate,userid,hostip,farticleid) values("
+articleId+",'"+art.getTitle()+"','"+art.getContent()+"','"+art.getPubdate()+"',"+art.getUserid()
+",'"+art.getHostip()+"',"+articleId+")";
int nResult=stmt.executeUpdate(sql);
ok=(nResult==1);
}catch(Exception ex){
ex.toString();
ok=false;
}finally{
DatabaseBean.close(conn, stmt, rs);
}
return ok;
}
/**
* 获取留言数目
*/
public static int getArticleCount(){
int articleCount=0;
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
String sql=null;
try{
conn=DatabaseBean.getConnection();
stmt=conn.createStatement();
sql="select COUNT(articleid) from article where articleid=farticleid";
rs=stmt.executeQuery(sql);
while(rs.next()){
articleCount=rs.getInt(1);
}
}catch(Exception ex){
ex.toString();
}finally{
DatabaseBean.close(conn, stmt, rs);
}
return articleCount;
}
/**
* 获取留言主题
*/
public static Vector<Article> getArticles(int start,int length){
Vector<Article> vt=new Vector<Article>();
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
String sql=null;
try{
conn=DatabaseBean.getConnection();
stmt=conn.createStatement();
sql="select a.articleid,a.title,a.content,a.pubdate,a.userid,a.hostip,a.farticleid,u.nickname from (select articleid from article a where articleid=farticleid order by pubdate desc limit "+start+","+length+")b,article a,userinfo u where a.articleid=b.articleid and a.userid=u.userid";
//执行sql语句
rs=stmt.executeQuery(sql);
while(rs.next()){
//构造Article对象，并加入vt中
Article art=new Article();
art.setArticleId(rs.getInt(1));
art.setTitle(rs.getString(2));
art.setContent(rs.getString(3));
art.setPubdate(rs.getTimestamp(4));
art.setUserid(rs.getInt(5));
art.setHostip(rs.getString(6));
art.setFarticleid(rs.getInt(7));
art.setNickname(rs.getString(8));
vt.add(art);
}
}catch(Exception ex){
ex.toString();
}finally{
DatabaseBean.close(conn, stmt, rs);
}
return vt;
}
}
下面是个servlet
package thisServlet;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import thisBeans.*;
import java.util.*;
/**
* @author David.Qiu
* 获取留言主题
*/
public class GetArticles extends HttpServlet{
/**
*
*/
private static final long serialVersionUID = 1L;
public void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException{
//获取参数
//count是留言主题的总数
//currentPage是当前的页号
//pageNum是每页的主题数
int currentPage;
if(request.getParameter("currentPage")==null)
currentPage=0;
else
currentPage=Integer.parseInt(request.getParameter("currentPage"));
int pageNum=2;
IntBean intBean=new IntBean();
intBean.setCurrentPage(currentPage);
intBean.setPageNum(pageNum);
request.setAttribute("intBean",intBean);
//获取留言主题
//注意第一页和其他页的区别
if(currentPage>0){
vt=ArticleBean.getArticles(currentPage*pageNum-1, pageNum);
}else {
vt=ArticleBean.getArticles(currentPage*pageNum, pageNum);
}
request.setAttribute("articles",vt);
response.setContentType("text/html; charset=GBK");
response.setCharacterEncoding("gbk");
RequestDispatcher dispatcher=request.getRequestDispatcher("articleList.jsp");
dispatcher.forward(request,response);
}
public void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException{
doPost(request,response);
}
}
用于显示的jsp界面
<%@ page language="java" contentType="text/html; charset=gbk"%>
<%@ page pageEncoding="gbk"%><%@ page import="java.util.*" %>
<%@ page import="thisBeans.*" %>
<%@ page import="commonBeans.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gbk">
<title>话题列表</title>
</head>
<body>
<%
request.setCharacterEncoding("gbk");
//获取留言主题
Vector vt=(Vector)request.getAttribute("articles");
%>
<%UserInfo user=(UserInfo)session.getAttribute("user_info"); %>
<p><%=user.getNickname() %>欢迎您！
<center>
<table border="0" width="600">
<tr>
<td colspan="3" align="center">留言列表（<a href="logout">退出</a>/<a href="articlePublish.jsp">发表文章</a>）</td>
</tr>
</table>
<table border="1" width="600">
<%
for(int i=0;i<vt.size();i++){
Article art=(Article)vt.get(i);
%>
<tr>
<td rowspan="2"><%=i+1 %></td>
<td width="250"><%=art.getTitle() %>(<%=art.getPubdate() %>)</td>
<td>
<%=art.getNickname() %>(<%=art.getHostip() %>)
</td>
<td >
<%
out.println("<a href=articleReply?articleId="+art.getArticleId()+">回复</a> ");
if(art.getUserid()==user.getUserid())
out.println("<a href=articleUpdate?articleId="+art.getArticleId()+">修改</a> ");
if(user.isAdmin())
out.println("<a href=articleDelete?articleId="+art.getArticleId()+">删除</a> ");
%>
</td>
</tr>
<tr>
<td colspan="3"><%=art.getContent() %></td>
</tr>
<%
}
%>
</table>
<%
//获取总的留言数
int nCount=ArticleBean.getArticleCount();
IntBean intBean=(IntBean)request.getAttribute("intBean");
int currentPage=intBean.getCurrentPage();
int pageNum=intBean.getPageNum();
%>
<table border="0" width="383">
<tr>
<td width="377">
<p>当前共有<%=ArticleBean.getArticleCount() %>条记录,<%=(ArticleBean.getArticleCount()-1)/pageNum +1%>页,每页有<%=pageNum %>条记录,当前为第<%=currentPage+1 %>页。
</td>
</tr>
<tr>
<td width="377" align="center">
<a href="getArticles?currentPage=0">首页</a>
<%
if(currentPage-1>=0){%>
<a href="getArticles?currentPage=<%=currentPage-1 %>">上一页</a>
<%} else{%>
<a>上一页</a>
<%} %>
<%if(currentPage+1<=(ArticleBean.getArticleCount()-1)/pageNum){ %>
<a href="getArticles?currentPage=<%=currentPage+1 %>">下一页</a>
<%}else{ %>
<a> 下一页</a>
<%} %>
<a href="getArticles?currentPage=<%=(ArticleBean.getArticleCount()-1)/pageNum %>">末页</a>
</td>
</tr>
</table>
</center>
</body>
</html>
创建数据库的语句为
# MySQL-Front 3.1 (Build 9.0)
# Host: localhost Database: msgboard
# ------------------------------------------------------
# Server version 5.0.22-community-nt
#
# Table structure for table article
#
CREATE TABLE `article` (
`articleid` int(6) unsigned NOT NULL default '0' COMMENT '留言号码，主键',
`title` varchar(32) NOT NULL COMMENT '留言标题',
`content` varchar(255) NOT NULL COMMENT '留言内容',
`pubdate` datetime NOT NULL COMMENT '发布时间',
`userid` int(6) NOT NULL default '0' COMMENT '发布人',
`hostip` varchar(16) NOT NULL COMMENT '发布人ip地址',
`farticleid` int(6) NOT NULL default '0' COMMENT '回复留言ID，为非回复文章时，与articleid相同',
PRIMARY KEY (`articleid`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
#
# Dumping data for table article
#
INSERT INTO `article` VALUES (1,'???ó??','????','2007-03-16 19:47:24',1,'127.0.0.1',1);
INSERT INTO `article` VALUES (2,'asdsadas','????·?????·?','2007-03-16 19:48:14',2,'127.0.0.1',2);
INSERT INTO `article` VALUES (3,'·????ù·???·?','asdsa','2007-03-16 19:48:28',2,'127.0.0.1',3);
INSERT INTO `article` VALUES (4,'·????ù·???·?','???????? ','2007-03-16 20:07:29',2,'127.0.0.1',4);
INSERT INTO `article` VALUES (5,'·????ù·???·?','???????? ','2007-03-16 20:33:01',2,'127.0.0.1',5);
INSERT INTO `article` VALUES (6,'前卫队','???????? ','2007-03-16 20:33:06',2,'127.0.0.1',6);
INSERT INTO `article` VALUES (7,'阿三多','阿三多岁','2007-03-16 20:43:05',2,'127.0.0.1',7);
INSERT INTO `article` VALUES (8,'邱大为','你好','2007-03-16 20:44:08',2,'127.0.0.1',8);
INSERT INTO `article` VALUES (9,'阿三多岁','阿三多岁大赛的撒大赛的','2007-03-16 20:57:11',2,'127.0.0.1',9);
#
# Table structure for table tableseq
#
CREATE TABLE `tableseq` (
`tablename` varchar(32) NOT NULL default '0' COMMENT '表名，主键',
`maxid` int(6) NOT NULL default '0' COMMENT '最大主键值',
PRIMARY KEY (`tablename`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
#
# Dumping data for table tableseq
#
INSERT INTO `tableseq` VALUES ('article',9);
INSERT INTO `tableseq` VALUES ('userinfo',2);
#
# Table structure for table userinfo
#
CREATE TABLE `userinfo` (
`userid` int(6) unsigned NOT NULL default '0' COMMENT '用户号码主键',
`username` varchar(32) NOT NULL COMMENT '用户名',
`nickname` varchar(32) NOT NULL COMMENT '昵称',
`password` char(16) NOT NULL COMMENT '密码',
`email` varchar(64) NOT NULL COMMENT '用户电子邮件',
`type` int(4) NOT NULL default '0' COMMENT '用户类型，0为普通用户，1为管理员',
`status` int(4) NOT NULL default '0' COMMENT '帐号状态，0为可用，1为禁止登录',
PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
#
# Dumping data for table userinfo
#
INSERT INTO `userinfo` VALUES (1,'abc','???ó??','abc','jaklsd',0,0);
INSERT INTO `userinfo` VALUES (2,'qiu','qiu','qiu','4798',0,0);
应该注意的问题
◎在获取当前页号是利用request.getParameter()获取的是String类型，因该用Integer.parseInt();将其转化为int类型，之后再进行运算和查询
◎ 进行分页时注意第一页和其他页的操作
◎分也处理时Mysql数据库中的sql语句select * from tableName limit start,length,意思是从mysql中的tableName表取出start+1开始的length条数据