分页查询示例

PageModel

/**
 * 封装分页信息
 * 
 * @author admin
 * 
 */
public class PageModel<E> {
	// 结果集
	private List<E> list;

	// 查询记录数
	private int totalRecords;

	// 第几页
	private int pageNo;

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

	// 取得下一页
	public int getNextPage() {
		if (pageNo >= getBottomPage()) {
			return getBottomPage();
		}
		return pageNo + 1;
	}

	// 取得上一页
	public int getPreviousPage() {
		if (pageNo <= 1) {
			return 1;
		}
		return pageNo - 1;
	}

	// 取得尾页,尾页就是总页数
	public int getBottomPage() {
		return getTotalPages();
	}

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

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

	public List<E> getList() {
		return list;
	}

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

	public int getTotalRecords() {
		return totalRecords;
	}

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

	public int getPageNo() {
		return pageNo;
	}

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

	public int getPageSize() {
		return pageSize;
	}

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

}

UserManager

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

/**
 * 采用单例管理用户
 * 
 * @author admin
 * 
 */
public class UserManager {
	private static UserManager instance = new UserManager();

	private UserManager() {
	}

	public static UserManager getInstance() {
		return instance;
	}

	
	/**
	 * 分页查询
	 * @param pageNo	第几页
	 * @param pageSize  每页多少条数据
	 * @return	pageModel
	 */
	public PageModel<User> findUserList(int pageNo,int pageSize) {
		StringBuffer sb = new StringBuffer();
		sb.append("select user_id,user_name,password,contact_tel,email,create_date ")
		  .append("from (")
		  .append("select rownum rn,user_id,user_name,password,contact_tel,email,create_date ")
		  .append("from (")
		  .append("select * from t_user where user_id<>'root' order by user_id")
		  .append(")where rownum<=?")
		  .append(")")
		  .append("where rn>?");

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		PageModel<User> pageModel = null;
		try {
			conn = DbUtil.getConnection();
			pstmt = conn.prepareStatement(sb.toString());
			pstmt.setInt(1, pageNo * pageSize);
			pstmt.setInt(2, (pageNo - 1) * pageSize);
			rs = pstmt.executeQuery();
			List<User> userList = new ArrayList<User>();
			while(rs.next()){
				User user = new User();
				user.setUserId(rs.getString("user_id"));
				user.setUserName(rs.getString("user_name"));
				user.setPassword(rs.getString("password"));
				user.setContactTel(rs.getString("contact_tel"));
				user.setEmail(rs.getString("email"));
				user.setCreateDate(rs.getTimestamp("create_date"));
				userList.add(user);
			}
			pageModel = new PageModel<User>();
			pageModel.setList(userList);
			pageModel.setTotalRecords(getTotalRecords(conn));
			pageModel.setPageSize(pageSize);
			pageModel.setPageNo(pageNo);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DbUtil.close(rs);
			DbUtil.close(pstmt);
			DbUtil.close(conn);
		}
		return pageModel;
	}

	/**
	 * 取得总记录数
	 * @param conn
	 * @return
	 */
	private int getTotalRecords(Connection conn) {
		String sql = "select count(*) from t_user where user_id<>'root'";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int count = 0;
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			rs.next();
			count = rs.getInt(1);//查询出来只有一个count
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}
}

user_maint.jsp

<!-- drp\WebRoot\sysmgr\user_maint.jsp -->
<%@ page language="java" import="java.util.*" contentType="text/html; charset=GB18030" pageEncoding="GB18030"%>
<%@ page import="com.xdy.drp.sysmgr.domain.*" %>
<%@ page import="com.xdy.drp.sysmgr.manager.*" %>
<%@ page import="com.xdy.drp.util.*" %>
<%@ page import="java.text.*" %>
<%
	int pageNo=3;
	int pageSize=2;
	//取得客户端传过来的需要跳转到的页数，赋值给pageNo，传入方法，返回pageModel在页面进行显示。
	String pageNoString = request.getParameter("pageNo");
	if(pageNoString!=null){
		pageNo = Integer.parseInt(pageNoString);
	}
	PageModel<User> pageModel = UserManager.getInstance().findUserList(pageNo,pageSize);
 %>
<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=GB18030">
		<title>用户维护</title>
		<link rel="stylesheet" href="../style/drp.css">
		<script type="text/javascript">
	
	function addUser() {
		window.self.location = "user_add.jsp";	
	}
	
	function modifyUser() {
		window.self.location = "user_modify.html";
	}
	
	/*
	删除用户记录
	*/
	function deleteUser() {
		
	}
		
	/*
	全选/全不选
	*/
	function checkAll(field) {
		var selectFlags = document.getElementsByName("selectFlag");
		for(var i=0;i<selectFlags.length;i++){
			selectFlags[i].checked=field.checked;//上面的选中，下面的选中。上面的不选中，下面也跟着不选中。
		}
	}

	//首页
	function topPage() {
		window.self.location="user_maint.jsp?pageNo=<%=pageModel.getTopPageNo()%>";//客户端把pageNo发送到服务端，服务端也是此页面。上面的JAVA代码就是服务端，需要接收参数pageNo。
	}
	
	//上一页
	function previousPage() {
		window.self.location="user_maint.jsp?pageNo=<%=pageModel.getPreviousPage()%>";
	}	
	
	//下一页
	function nextPage() {
		window.self.location="user_maint.jsp?pageNo=<%=pageModel.getNextPage()%>";
	}
	
	//尾页
	function bottomPage() {
		window.self.location="user_maint.jsp?pageNo=<%=pageModel.getBottomPage()%>";
	}

</script>
	</head>

	<body class="body1">
		<form name="userform" id="userform">
			<div align="center">
				<table width="95%" border="0" cellspacing="0" cellpadding="0"
					height="35">
					<tr>
						<td class="p1" height="18" nowrap> 
							
						</td>
					</tr>
					<tr>
						<td width="522" class="p1" height="17" nowrap>
							<img src="../images/mark_arrow_02.gif" width="14" height="14">
							 
							<b>系统管理>>用户维护</b>
						</td>
					</tr>
				</table>
				<hr width="100%" align="center" size=0>
			</div>
			<table width="95%" height="20" border="0" align="center"
				cellspacing="0" class="rd1" id="toolbar">
				<tr>
					<td width="49%" class="rd19">
						<font color="#FFFFFF">查询列表</font>
					</td>
					<td width="27%" nowrap class="rd16">
						<div align="right"></div>
					</td>
				</tr>
			</table>
			<table width="95%" border="1" cellspacing="0" cellpadding="0"
				align="center" class="table1">
				<tr>
					<td width="55" class="rd6">
						<input type="checkbox" name="ifAll" onClick="checkAll(this)">
					</td>
					<td width="119" class="rd6">
						用户代码
					</td>
					<td width="152" class="rd6">
						用户名称
					</td>
					<td width="166" class="rd6">
						联系电话
					</td>
					<td width="150" class="rd6">
						email
					</td>
					<td width="153" class="rd6">
						创建日期
					</td>
				</tr>
				<%
					List<User> userList = pageModel.getList();
					for(Iterator<User> iter = userList.iterator();iter.hasNext();){
						User user = iter.next();
				 %>
				<tr>
					<td class="rd8">
						<input type="checkbox" name="selectFlag" class="checkbox1"
							value="<%=user.getUserId() %>">
					</td>
					<td class="rd8">
						<%=user.getUserId() %>
					</td>
					<td class="rd8">
						<%=user.getUserName() %>
					</td>
					<td class="rd8">
						<%=user.getContactTel() %>
					</td>
					<td class="rd8">
						<%=user.getEmail() %>
					</td>
					<td class="rd8">
						<%=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(user.getCreateDate()) %>
					</td>
				</tr>
				<%
					}
				 %>
			</table>
			<table width="95%" height="30" border="0" align="center"
				cellpadding="0" cellspacing="0" class="rd1">
				<tr>
					<td nowrap class="rd19" height="2">
						<div align="left">
							<font color="#FFFFFF"> 共 <%=pageModel.getTotalPages() %> 页</font>     
							<font color="#FFFFFF">当前第</font> 
							<font color="#FF0000"><%=pageModel.getPageNo() %></font> 
							<font color="#FFFFFF">页</font>
						</div>
					</td>
					<td nowrap class="rd19">
						<div align="right">
							<input name="btnTopPage" class="button1" type="button"
								id="btnTopPage" value="|<< " title="首页"
								onClick="topPage()">
							<input name="btnPreviousPage" class="button1" type="button"
								id="btnPreviousPage" value=" <  " title="上页"
								onClick="previousPage()">
							<input name="btnNextPage" class="button1" type="button"
								id="btnNextPage" value="  > " title="下页" onClick="nextPage()">
							<input name="btnBottomPage" class="button1" type="button"
								id="btnBottomPage" value=" >>|" title="尾页"
								onClick="bottomPage()">
							<input name="btnAdd" type="button" class="button1" id="btnAdd"
								value="添加" onClick="addUser()">
							<input name="btnDelete" class="button1" type="button"
								id="btnDelete" value="删除" onClick="deleteUser()">
							<input name="btnModify" class="button1" type="button"
								id="btnModify" value="修改" onClick="modifyUser()">
						</div>
					</td>
				</tr>
			</table>
			<p> 
				
			</p>
		</form>
	</body>
</html>