JavaEE分页查询

   本文主要介绍JavaEE中采用泛型进行分页查询。该分页是通过当前页号及每页记录数在数据库中进行查询，假设数据库是Oracle。

完整代码可以到 https://github.com/psp0001060/LearnServlet下载 。

[TOC]

一、项目结构图

二、关键代码

PageModel

package com.hr.pojo;

import java.util.List;

public class PageModel<E> {
	private List<E> list;
	private int pageNo;//页号
	private int pageSize;//每页显示记录数
	private int totalNum; //总记录数
	private int totalPage;//总页数

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

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

	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;
	}

	public int getTotalNum() {
		return totalNum;
	}

	public void setTotalNum(int totalNum) {
		this.totalNum = totalNum;
		setTotalPage((getTotalNum() % pageSize) == 0 ? (getTotalNum() / pageSize) : (getTotalNum() / pageSize + 1));
	}

	public int getTotalPage() {
		return totalPage;
	}

	public void setTotalPage(int totalPage) {
		this.totalPage = totalPage;
	}

	// 获取第一页
	public int getFirstPage() {
		return 1;
	}

	// 获取最后页
	public int getLastPage() {
		return totalPage;
	}

	// 获取前页
	public int getPrePage() {
		if (pageNo > 1)
			return pageNo - 1;
		return 1;
	}

	// 获取后页
	public int getBackPage() {
		if (pageNo < totalPage)
			return pageNo + 1;
		return totalPage;
	}

	// 判断'首页'及‘前页'是否可用
	public String isPreable() {
		if (pageNo == 1)
			return "disabled";
		return "";
	}

	// 判断'尾页'及‘下页'是否可用
	public String isBackable() {
		if (pageNo == totalPage)
			return "disabled";
		return "";
	}
}

deptList.jsp

<table class="hovertable" width="100%">
				<tr>
					<td>部门编号</td>
					<td>部门名称</td>
					<td>部门地址</td>
					<td>操作1</td>
					<td>操作2</td>
				</tr>
				<c:forEach items="${requestScope.pagination.list }" var="dept">
					<tr>
						<td>${dept.deptId }</td>
						<td>${dept.deptName }</td>
						<td>${dept.deptLoc }</td>
						<td><a href="ToDeptUpdateServlet?deptId=${dept.deptId }">更新</a></td>
						<td><a href="">删除</a></td>
					</tr>
				</c:forEach>
				<tr>
		            <td colspan="5" align="right">
		            <c:if test="${pagination.pageNo > 1 }">
		            	<a href="DeptListServlet?pageNo=1">首页</a>
		            	<a href="DeptListServlet?pageNo=${pagination.pageNo-1} ">上一页</a>
		            </c:if>
		            <c:if test="${pagination.pageNo < pagination.totalPage }">
		            	<a href="DeptListServlet?pageNo=${pagination.pageNo+1}">下一页</a>
			            <a href="DeptListServlet?pageNo=${pagination.totalPage}">末页</a>
		            </c:if>
		            
				</tr>
			</table>

DeptListServlet

@WebServlet("/DeptListServlet")
public class DeptListServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	private int pageSize = 0; 
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public DeptListServlet() {
        super();
    }

    @Override 
    public void init() throws ServletException { 
    	pageSize = Integer.valueOf(this.getServletContext().getInitParameter("pageSize")); //从web.xml中获取pageSize数值
    }
    
	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		int pageNo = 1;//默认是第一页
		if (null != request.getParameter("pageNo")) {//从主界面过来的请求
			pageNo = Integer.parseInt(request.getParameter("pageNo")); 
		}
		 
		DeptService ds = new DeptService();
		if(null !=request.getAttribute("msg")){
			String toPage = (String) request.getAttribute("toPage");
			request.getRequestDispatcher(toPage).forward(request, response);
		}else{
//			List<Dept> list = ds.queryAll();
			PageModel<Dept> pagination = ds.queryForPage(pageNo,pageSize);
			request.setAttribute("pagination", pagination);
			request.getRequestDispatcher("dept/deptList.jsp").forward(request, response);
		}
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

DeptService####

	/**
	 * 分页查询记录
	 * @param pageNo 
	 * @param pageSize
	 * @return 
	 */
	public PageModel<Dept> queryForPage(int pageNo, int pageSize) {
		DeptDao deptDao = new DeptDao();

		PageModel<Dept> pagination = new PageModel<Dept>();
		pagination.setList(deptDao.queryForPage(pageNo, pageSize));
		pagination.setPageNo(pageNo);
		pagination.setPageSize(pageSize);
		pagination.setTotalNum(deptDao.getTotalNum());
		return pagination;
	}

DeptDao####

/**
	 * 分页查询记录
	 * @param pageNo 
	 * @param pageSize
	 * @return 
	 */
	public List<Dept> queryForPage(int pageNo, int pageSize) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet result = null;
		Dept dept = null;
		List<Dept> resultList = new ArrayList<Dept>();
		try {
			String startPageSql = "select * from(select rownum num,u.* from(";
			String innerSql = "select * from dept";
			String endPageSql = ") u where rownum<=?) where num>=?";
			String sql = startPageSql + innerSql + endPageSql;

			ps = conn.prepareStatement(sql);
			ps.setInt(1, pageNo * pageSize);
			ps.setInt(2, (pageNo - 1) * pageSize + 1);
			result = ps.executeQuery();

			while (result.next()) {
				dept = new Dept();
				dept.setDeptId(result.getInt(2));
				dept.setDeptName(result.getString(3));
				dept.setDeptLoc(result.getString(4));
				resultList.add(dept);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(result);
			DBUtil.closePreparedStatement(ps);
			DBUtil.closeConnection(conn);
		}

		return resultList;
	}


	/**
	 * 获取总记录数
	 * @return
	 */
	public int getTotalNum() {
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet result = null;
		int totalCount = 0;
		
		try {
			String sql = "select count(1) from dept";
			ps = conn.prepareStatement(sql); 
			result = ps.executeQuery();
			
			if(result.next()) {
				totalCount = result.getInt(1);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(result);
			DBUtil.closePreparedStatement(ps);
			DBUtil.closeConnection(conn);
		}

		return totalCount;
	}

三、效果图##