JSP+Servlet+Oracle实现分页

菜鸟学javaweb，拼凑一个JSP+Servlet+oracle实现分页的实例，其中有明显不足之处，贴在此主要是方便自己记忆。
数据库建表语句：

-- Create table
create table BLOGINFO
(
  ID          NUMBER not null,
  BLOGTITLE   VARCHAR2(50) not null,
  BLOGCONTENT VARCHAR2(4000) not null,
  AUTHOR      VARCHAR2(30) not null,
  BLOGTIME    LONG not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column BLOGINFO.ID
  is '表id';
comment on column BLOGINFO.BLOGTITLE
  is '博客标题';
comment on column BLOGINFO.BLOGCONTENT
  is '博客内容';
comment on column BLOGINFO.AUTHOR
  is '作者，用户昵称';
-- Create/Recreate primary, unique and foreign key constraints 
alter table BLOGINFO
  add constraint BLOGINFO_ID primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table BLOGINFO
  add constraint AUTHOR_NAME foreign key (AUTHOR)
  references USERINFO (LOGINNAME);

加上一个javaVO，也如下：

package com.dylan.vo;

public class BlogInfo {

	int id;
	String blogtitle;
	String blogcontent;
	String author;
	long blogtime;
	String blogtimes;
	String blogt;

	public String getBlogtimes() {
		return blogtimes;
	}

	public void setBlogtimes(String blogtimes) {
		this.blogtimes = blogtimes;
	}

	public String getBlogt() {
		return blogt;
	}

	public void setBlogt(String blogt) {
		this.blogt = blogt;
	}

	public BlogInfo() {
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getBlogtitle() {
		return blogtitle;
	}

	public void setBlogtitle(String blogtitle) {
		this.blogtitle = blogtitle;
	}

	public String getBlogcontent() {
		return blogcontent;
	}

	public void setBlogcontent(String blogcontent) {
		this.blogcontent = blogcontent;
	}

	public String getAuthor() {
		return author;
	}

	public void setAuthor(String author) {
		this.author = author;
	}

	public long getBlogtime() {
		return blogtime;
	}

	public void setBlogtime(long blogtime) {
		this.blogtime = blogtime;
	}

}

新建一个PageModel类，List是为封装BlogInfo做准备的，还增加了为分页做准备的一些属性和方法：

package com.dylan.vo;

import java.util.List;

public class PageModel {

	// 结果集
	private List list;

	// 查询总记录数
	private int totalRecords;

	// 每页多少条数据
	private int pageSize;

	// 第几页
	private int pageNo;

	/**
	 * 总页数
	 * 
	 * @return
	 */
	public int getTotalPages() {
		return (totalRecords + pageSize - 1) / pageSize;
	}

	/**
	 * 取得首页
	 * 
	 * @return
	 */
	public int getTopPageNo() {
		return 1;
	}

	/**
	 * 上一页
	 * 
	 * @return
	 */
	public int getPreviousPageNo() {
		if (pageNo <= 1) {
			return 1;
		}
		return pageNo - 1;
	}

	/**
	 * 下一页
	 * 
	 * @return
	 */
	public int getNextPageNo() {
		if (pageNo >= getBottomPageNo()) {
			return getBottomPageNo();
		}
		return pageNo + 1;
	}

	/**
	 * 取得尾页
	 * 
	 * @return
	 */
	public int getBottomPageNo() {
		return getTotalPages();
	}

	public List getList() {
		return list;
	}

	public void setList(List list) {
		this.list = list;
	}

	public int getTotalRecords() {
		return totalRecords;
	}

	public void setTotalRecords(int totalRecords) {
		this.totalRecords = totalRecords;
	}

	public int getPageSize() {
		return pageSize;
	}

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

	public int getPageNo() {
		return pageNo;
	}

	public void setPageNo(int pageNo) {
		this.pageNo = pageNo;
	}
}

事务处理的类：

package com.dylan.service;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.dylan.util.DBHelper;
import com.dylan.util.DateUtil;
import com.dylan.vo.BlogInfo;
import com.dylan.vo.PageModel;

public class BlogInfoQueryService {

	private static BlogInfoQueryService instance = new BlogInfoQueryService();

	private BlogInfoQueryService() {
	};

	public static BlogInfoQueryService getInstance() {
		return instance;
	}

	/**
	 * 查询此用户所拥有的所有博客
	 * 
	 * @param loginname
	 * @return
	 * @throws SQLException
	 */
	public PageModel queryBlogInfoForAll(String loginname, int pageNo,
			int pageSize) throws SQLException {
		StringBuffer sql = new StringBuffer();
		PageModel pageModel = null;
		
		sql.append("select * ")
		.append("from")
		.append("(")
		.append("select rownum rn,t.* ")
		.append("from")
		.append("(")
		.append("select * ")
		.append("from bloginfo where author= ")
		.append("?")
		.append(" order by id")
		.append(") t ")
		.append("where rownum <= ?")
		.append(")")
		.append("where rn > ? ");
		
		Connection conn = DBHelper.getConnetion();
		PreparedStatement pstm =conn.prepareStatement(sql.toString());
		ResultSet rs = null;
		try {

			System.out.println(sql);
			pstm.setString(1, loginname);
			pstm.setInt(2, pageNo * pageSize);
			pstm.setInt(3, (pageNo - 1) * pageSize);
			
			rs = pstm.executeQuery();
			List<BlogInfo> blogInfoList = new ArrayList<BlogInfo>();
			while (rs.next()) {
				BlogInfo blogInfo = new BlogInfo();

				blogInfo.setId(rs.getInt(2));
				blogInfo.setBlogtitle(rs.getString(3));
				blogInfo.setBlogcontent(rs.getString(4));
				blogInfo.setAuthor(rs.getString(5));
				long l = rs.getLong(6);
				Date date = new Date(l);
				DateUtil du = new DateUtil();
				blogInfo.setBlogtimes(du.dateToString(date));

				blogInfoList.add(blogInfo);
			}
			pageModel = new PageModel();
			pageModel.setList(blogInfoList);
			pageModel.setTotalRecords(getTotalRecords(loginname, conn));
			pageModel.setPageSize(pageSize);
			pageModel.setPageNo(pageNo);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pstm.close();
			conn.close();
		}
		return pageModel;
	}

	public int getTotalRecords(String loginname, Connection conn)
			throws SQLException {
		String sql = "select count(*) from bloginfo where author = '"+loginname+"'";
		Statement pstm = conn.createStatement();
		ResultSet rs = null;
		int count = 0;
		try {
			rs = pstm.executeQuery(sql);
			rs.next();
			count = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			rs.close();
			pstm.close();
			conn.close();
		}
		return count;
	}
}

加上一个Servlet

package com.dylan.servlet;

import java.io.IOException;
import java.sql.SQLException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.dylan.service.BlogInfoQueryService;
import com.dylan.vo.PageModel;

public class QueryBlogInfoServlet extends HttpServlet {

	private static final long serialVersionUID = 1L;

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doPost(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		response.setContentType("text/html;charset=utf-8");
		request.setCharacterEncoding("utf-8");
		String loginname = request.getParameter("loginname");
		System.out.println("pageNo="+request.getParameter("pageNo"));
		System.out.println("pageSize="+request.getParameter("pageSize"));
		int pageNo =Integer.parseInt(request.getParameter("pageNo"));
		int pageSize = Integer.parseInt(request.getParameter("pageSize"));
		
		System.out.println(pageNo+"--"+pageSize);
		PageModel pageModel = null;
		System.out.println(loginname);
		BlogInfoQueryService biqs = BlogInfoQueryService.getInstance();
		try {
			pageModel = biqs.queryBlogInfoForAll(loginname, pageNo, pageSize);
			System.out.println("上一页："+pageModel.getPreviousPageNo());
			System.out.println("下一页："+pageModel.getNextPageNo());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		request.setAttribute("pageModel",pageModel);
		System.out.println("打印pageModel对象"+request.getAttribute("pageModel"));
		RequestDispatcher rd = request.getRequestDispatcher("/listblog.jsp");
		rd.forward(request, response);	
	}
}

最后就是JSP了。

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ page import="com.dylan.vo.BlogInfo"%>
<%@ page import="java.util.List"%>
<%@ page import="com.dylan.service.*"%>
<%@ page import="com.dylan.vo.*"%>
<%
	String path = request.getContextPath();
	String basePath = request.getScheme() + "://"
			+ request.getServerName() + ":" + request.getServerPort()
			+ path + "/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
	<head>
		<base href="<%=basePath%>">

		<title>My JSP 'userregeste.jsp' starting page</title>
		<meta http-equiv="pragma" content="no-cache">
		<meta http-equiv="cache-control" content="no-cache">
		<meta http-equiv="expires" content="0">
		<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
		<meta http-equiv="description" content="This is my page">
		<%
			int pageNo = 1;
			int pageSize = 3;
			PageModel pageModel = (PageModel) request.getAttribute("pageModel");
		%>
		<script type="text/javaScript">
function topPage() {
window.self.location = "<%=basePath%>queryBlogInfoServlet?loginname=<%=request.getSession().getAttribute("loginname")%>&pageNo=<%=pageModel.getTopPageNo()%>&pageSize=3";
}

function previousPage() {
	window.self.location = "<%=basePath%>queryBlogInfoServlet?loginname=<%=request.getSession().getAttribute("loginname")%>&pageNo=<%=pageModel.getPreviousPageNo()%>&pageSize=3";
}

function nextPage() {
	window.self.location = "<%=basePath%>queryBlogInfoServlet?loginname=<%=request.getSession().getAttribute("loginname")%>&pageNo=<%=pageModel.getNextPageNo()%>&pageSize=3";
}

function bottomPage() {
	window.self.location = "<%=basePath%>queryBlogInfoServlet?loginname=<%=request.getSession().getAttribute("loginname")%>&pageNo=<%=pageModel.getBottomPageNo()%>&pageSize=3";
}
</script>
	</head>
	<body bgcolor="#abe2d6">
		<table width="650">
			<tr bgcolor="#ade2d6">
				<td width="560">
					我的博文
				</td>
				<td></td>
				<td></td>
				<td>
					<a href="<%=basePath%>writeblog.jsp" shape="rect">写博客</a>
				</td>
			</tr>
		</table>

		<table width="650">
			<hr />
			<tr bgcolor="#ade2d6">
				<td bgcolor="yellow" width="400">
					博客标题
				</td>
				<td bgcolor="yellow">
					博客作者
				</td>
				<td bgcolor="yellow">
					发表时间
				</td>
				<td bgcolor="yellow">
					更多操作
				</td>
			</tr>
			<%
				List<BlogInfo> bloginfolist = pageModel.getList();
				//List<BlogInfo> bloginfolist = (List<BlogInfo>)request.getAttribute("bloginfolist");
			%>
			<%
				for (BlogInfo blogInfo : bloginfolist) {
			%>
			<tr>
				<td width="400">
					<a
						href="<%=basePath%>queryBlogInfoByIdServlet?id=<%=blogInfo.getId()%>"><%=blogInfo.getBlogtitle()%></a>
				</td>
				<td><%=blogInfo.getAuthor()%></td>
				<td><%=blogInfo.getBlogtimes()%></td>
				<td>
					更多操作
				</td>
			</tr>
			<%
				}
			%>
			<tr>
				<td colspan="5">
					共&nbsp;<%=pageModel.getTotalPages()%>&nbsp;页&nbsp;&nbsp;&nbsp;&nbsp;
					当前第&nbsp;<%=pageModel.getPageNo()%>页&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
					<input type="button" value="首页" onClick="topPage()">
					<input type="button" value="上一页" onClick="previousPage()">
					<input type="button" value="下一页" onClick="nextPage()">
					<input type="button" value="尾页" onClick="bottomPage()">
				</td>
			</tr>
		</table>
	</body>
</html>

以上代码有些不合理的地方，暂时没有处理，如pageNo和pageSize赋值应该是放在JSP中可以填写的，这个是Oracle分页的第一种方法。