分页查询

package com.cx.drp.util;

import java.util.List;

/**
 * 封装分页对象PageModel
 * @author CX
 *http://chenxu.wo.blog.163.com/
 */
public class PageModel {

	//分页列表
	private List list;
	
	//总页数
	private int totalRecords;	

	//每页大小
	private int pageSize;
	
	//当前页号
	private int pageNo;
	
	//获取总页数
	public int getTotalPages(){
		return (totalRecords+pageSize-1)/pageSize;
	}
	
	/**
	 * 取得首页
	 * @return
	 */
	public int getTopPageNo(){
		return 1;		
	}
	
	/**
	 * 前一页
	 * @return
	 */
	public int getPreviousPageNo(){
		if(this.pageNo<=1){
			return 1;
		}
		return pageNo-1;
	}
	
	/**
	 * 下一页
	 * @return
	 */
	public int getNextPageNo(){
		if(this.pageNo>=getTotalPages()){
			return getTotalPages();
		}
		return pageNo+1;		
	}
	
	/**
	 * 尾页
	 * @return
	 */
	public int getBottomPageNo(){
		return getTotalPages();					
	}
	

	public int getTotalRecords() {
		return totalRecords;
	}

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

	public List getList() {
		return list;
	}

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

	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.cx.drp.sysmgr.manager;

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;

import com.cx.drp.sysmgr.domain.User;
import com.cx.drp.util.DBUtil;
import com.cx.drp.util.PageModel;

/**
 * 使用单例管理用户
 * 
 * @author CX
 * 
 */
public class UserManager {

	private static UserManager instance = null;

	private UserManager() {
	};

	public static synchronized UserManager createInstance() {
		if (instance == null) {
			instance = new UserManager();
		}
		return instance;
	}
	
	/**
	 * 根据分页查询学生列表
	 * 
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public PageModel findUserList(int pageNo, int pageSize) {
		StringBuffer sb = new StringBuffer();
		sb
				.append("")
				.append("select * from(")
				.append(
						"select rownum rn,user_id,user_name,password,contact_tel,email,create_date from(")
				.append(
						"select * from t_User where user_id<>'root' order by user_id)")
				.append("where rownum <= ?").append(") where rn>?");
		PreparedStatement pstmt = null;

		ResultSet rs = null;
		Connection conn = null;
		List userList = new ArrayList();
		PageModel 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();
			while (rs.next()) {
				User user = new User();
				user.setUserId(rs.getString("user_id"));
				user.setUserName(rs.getString("user_name"));
				user.setContactTel(rs.getString("contact_tel"));
				user.setEmail(rs.getString("email"));
				user.setPassword(rs.getString("password"));
				user.setCreateDate(rs.getTimestamp("create_date"));

				userList.add(user);
			}

			pageModel = new PageModel();
			pageModel.setList(userList);
			pageModel.setPageNo(pageNo);
			pageModel.setTotalRecords(getTotalSize(conn));
			pageModel.setPageSize(pageSize);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pstmt);
			DBUtil.close(conn);
		}
		return pageModel;
	}

	/**
	 * 获取总记录数
	 * @param conn
	 * @return
	 */
	public int getTotalSize(Connection conn) {
		String sql = "select count(*) from t_user where user_id<>'root'";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int recordCount = 0;
		try {
			conn = DBUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			rs.next();
			recordCount = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return recordCount;
	}
	

 

<%@ page language="java" contentType="text/html; charset=GB18030"
	pageEncoding="GB18030"%>

<%@ page import="com.cx.drp.sysmgr.domain.*"%>
<%@ page import="com.cx.drp.sysmgr.manager.*"%>
<%@ page import="com.cx.drp.util.*"%>
<%@ page import="java.text.*"%>
<%@ page import="java.util.*"%>


<%
	int pageNo = 1;
	int pageSize = 1;
	String pageNoString = request.getParameter("pageNo");
	if(pageNoString!=null && !"".equals(pageNoString)){
		pageNo = Integer.parseInt(pageNoString);
	}
		
	PageModel pageModel = new PageModel();
	pageModel = UserManager.createInstance().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() {

}

//CheckBox全选	
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()%>";
}

function previousPage() {
	window.self.location="user_maint.jsp?pageNo=<%=pageModel.getPreviousPageNo()%>";
}

function nextPage() {
	window.self.location="user_maint.jsp?pageNo=<%=pageModel.getNextPageNo()%>";
}

function bottomPage() {
	window.self.location="user_maint.jsp?pageNo=<%=pageModel.getBottomPageNo()%>";
}
</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>
							&nbsp;

						</td>
					</tr>
					<tr>
						<td width="522" class="p1" height="17" nowrap>
							<img src="../images/mark_arrow_02.gif" width="14" height="14">
							&nbsp;
							<b>系统管理&gt;&gt;用户维护</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">
						&nbsp;
						<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 userList = pageModel.getList();
					for (Iterator iter = userList.iterator(); iter.hasNext();) {
						User 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.getPassword()%>
					</td>
					<td class="rd8">
						<%=user.getEmail()%>
					</td>
					<td class="rd8">
						<%=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">&nbsp;共&nbsp; <%=pageModel.getTotalPages()%>
								&nbsp;页</font>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
							<font color="#FFFFFF">当前第</font>&nbsp;
							<font color="#FF0000"><%=pageNo%></font>&nbsp;
							<font color="#FFFFFF">页</font>;
						</div>
					</td>
					<td nowrap class="rd19">
						<div align="right">
							<input name="btnTopPage" class="button1" type="button"
								id="btnTopPage" value="|&lt;&lt; " title="首页"
								onClick="topPage()">
							<input name="btnPreviousPage" class="button1" type="button"
								id="btnPreviousPage" value=" &lt;  " title="上页"
								onClick="previousPage()">
							<input name="btnNextPage" class="button1" type="button"
								id="btnNextPage" value="  &gt; " title="下页" onClick="nextPage()">
							<input name="btnBottomPage" class="button1" type="button"
								id="btnBottomPage" value=" &gt;&gt;|" 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>
				&nbsp;

			</p>
		</form>
	</body>
</html>