jsp中利用MySQL实现分页技术

jsp中利用MySQL实现分页技术

分页是很常用的一种技术，而MySQL中对于分页的操作也很简单，今天就说说如何在jsp页面中利用标签来最简化的实现分页：

链接：MySQL分页技术详解http://blog.csdn.net/u011637069/article/details/49928513

step1:编写DAO中代码：

	public List<Employee> findAll2(int page, int perPageRows) throws Exception {
		List<Employee> employees = new ArrayList<Employee>();
		Connection conn = null;
		try{
			conn = DBUtil.getConnection();
			PreparedStatement prep = 
				conn.prepareStatement("select id,name,salary,age from t_emp limit ?,?");
			prep.setInt(1, (page-1)*perPageRows);
			prep.setInt(2,perPageRows);
			ResultSet rs = prep.executeQuery();
			while(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				double salary = rs.getDouble("salary");
				int age = rs.getInt("age");
				Employee e = new Employee(id, name, salary, age);
				employees.add(e);
			}
		}catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally{
			DBUtil.close(conn);
		}
		return employees;
	}

	public int findPages(int perPageRows) throws Exception {
		int totalPage = 0;
		Connection conn = null;
		try{
			conn = DBUtil.getConnection();
			Statement state = conn.createStatement();
			ResultSet rs = state.executeQuery("select count(*) from t_emp");
			int rows = 0;
			if(rs.next()){
				rows = rs.getInt(1);
			}
			if(rows % perPageRows == 0){
				totalPage = rows / perPageRows;
			}else{
				totalPage = rows / perPageRows + 1;
			}
		}catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally{
			DBUtil.close(conn);
		}
		return totalPage;
	}

step2:编写servlet代码：

			EmployeeDAO dao = (EmployeeDAO) Factory.getInstance("EmployeeDAO");
			List<Employee> employees = null;
			String pageStr = request.getParameter("page");
			
			int perPageRows = 5;
			int totalPage = 0;
			int page = 1;
			try {
				totalPage = dao.findPages(perPageRows);
				if(pageStr!=null){
					page = Integer.parseInt(pageStr);
				}
				if(page<1 || page>totalPage){
					page = 1;
				}
				employees = dao.findAll2(page,perPageRows);
				//转发
				//step1，绑定数据
				request.setAttribute("employees",employees);
				request.setAttribute("page",page);
				request.setAttribute("totalPage",totalPage);
				//step2，获得转发器
				RequestDispatcher rd = request.getRequestDispatcher("/emplist.jsp");
				//step3，转发
				rd.forward(request, response);
			} catch (Exception e1) {
				e1.printStackTrace();
				//1-转发处理异常
				//request.setAttribute("syserror", "系统正忙，请稍后重试！");
				//request.getRequestDispatcher("error.jsp").forward(request, response);
				//2-交给容器处理
				throw new ServletException(e1);
			}

step3:jsp代码：

          	<c:choose>
          		<c:when test="${page>1}">
          			<a href="list.do?page=${page-1}">上一页</a>
          		</c:when>
          		<c:otherwise>
          			上一页
          		</c:otherwise>
          	</c:choose>
          	第${page}页
          	<c:choose>
          		<c:when test="${page<totalPage}">
          			<a href="list.do?page=${page+1}">下一页</a>
          		</c:when>
          		<c:otherwise>
          			下一页
          		</c:otherwise>
          	</c:choose>
          	共${totalPage}页

自此，全部结束，还是比较简单的！！！