jsp+selvlet+jdbc分页优化

今天写了段jsp页面分页的代码，这里我就只讲下分页的思想 。代码附带部分

分页种类;

逻辑分页

一次查询数据库所有记录，放入内存集合中。第一次查询比较慢，数据够多可能会溢出。只对数据库进行一次操作。

物理分页

每次都去查询数据库.，查一次放一次。不会溢出，我们基本上用的都是物理分页。

 

分页的原理

用户输入的参数，前台页面到后端的数据

CurrentPage 当前页

PageSize 每页显示的数量

Sql执行后出来的参数：

Total 总记录数：

SELECT COUNT(*) AS total FROM users

countPage总页数：

countPage = (total % pagesize == 0 ? total / pagesize : total/ pagesize + 1);

message查询结果集：

SELECT * FROM users   order by id asc LIMIT ?,?

第一个参数为开始的索引 BeginIndex=（currentPage-1）*pagesize

第二个参数为每页的记录数：pagesize；

 

手动计算：

BeginPage：首页 默认1

countPage：末页/总页数

PrevPage:上一页 Prevpage>1? currentPage-1:1;(已经是第一页就减不了)

NextPage：下一页Nextpage< Totalpage?currentPage+1:countPage(注意countPage要定义在nextpage的前面)

以上分页所有用到的值都取到了，只需要数据交互就行了 下面是获取数值封装的方法。只需要在servlet中调用

public class Users {
	private final int pagesize=2;//每页的记录
	int total=0;
	int countPage = 0;
	public int getTotal() {
		Connection conn=JdbcUtils.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		int total = 0;
		String sql = "SELECT COUNT(*) AS num FROM users";   //查询记录条数，然后把查询结果另外起一个别名，叫做num
 
		try {
			ps = (PreparedStatement) conn.prepareStatement(sql);
			rs = ps.executeQuery();
 
			if (rs.next()) {
				total = rs.getInt("num");   //total为总条数
			}
			}catch (SQLException e) {
				throw new RuntimeException(e.getMessage(), e);
			}
		return total;
			
	}
public int getCountPage() throws SQLException {
			total=this.getTotal();
			 //   总页数=总条数/每页显示最大留言数，能除尽时直接取结果，不能除尽时，结果加1，多加一页来显示
			countPage = (total % pagesize == 0 ? total / pagesize : total
					/ pagesize + 1);  
			
			if (countPage != 0)
				return countPage;
			return countPage + 1;   //没有第0页，所以加1
		}


public List<User> getMessage(int currentPage) throws SQLException {   
	//currentPage为当前页数
	
	List<User> list = new ArrayList<User>(); 
	
	String sql = "SELECT * FROM users   order by id asc LIMIT ?,?";
	/*
	 * order by id desc --按id列大小降序排列，不加desc就是升序排列
	 * LIMIT 100,15--从查询的结果中第100条开始取出15条数据
	 */
	try {
		Connection conn=JdbcUtils.getConn();
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setInt(1, (currentPage - 1) * pagesize);
		ps.setInt(2, pagesize);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			 User user=new User();
			  user.setId(rs.getInt("id"));
			  user.setAge(rs.getInt("age"));
			  user.setName(rs.getString("name"));
			  user.setTel(rs.getString("tel"));
			  list.add(user);  //将找出的数据存入messageList
		}
		rs.close();
		ps.close();
		conn.close();
	} catch (SQLException e) {
		
	} finally {
		
	}

	return list;
}


}

@WebServlet("/FindServlet")
public class FindServlet extends HttpServlet {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		 try { 
			 Users messageService = new Users();
			 int countPage = messageService.getCountPage();
			 System.out.println("总页数"+countPage);
				//从jsp页面获取当前页数
			 	int currentPage = 1;
			 	
				try {
					String scurrentPage =request.getParameter("currentPage");
					System.out.println("jsp传递的页码"+scurrentPage);
					//判断输入页码是否合法
					if(scurrentPage==null||scurrentPage=="") {
						currentPage=1;
								
					}else {
						
						currentPage = Integer.parseInt(scurrentPage);
						
						if(currentPage<=0) {
							currentPage=1;
							
						}	
						if(currentPage>countPage) {
							currentPage=countPage;
						}
					}
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				//查询数据库获得数据计算出总页数
				
				int total=messageService.getTotal();
				//将当前页数，总页数，以及找出的数据返回给jsp页面
				request.setAttribute("total", total);
				request.setAttribute("countPage", countPage);
				request.setAttribute("currentPage", currentPage);
				request.setAttribute("list", messageService.getMessage(currentPage));
				

			 /*Connection conn=JdbcUtils.getConn();
		      String sql="select * from users";
			  Statement st=(Statement) conn.createStatement();
			  ResultSet resultSet = st.executeQuery(sql);
			  List<User> list = new ArrayList<User>(); 
			  while(resultSet.next()) {
				  User user=new User();
				  user.setId(resultSet.getInt("id"));
				  user.setAge(resultSet.getInt("age"));
				  user.setName(resultSet.getString("name"));
				  user.setTel(resultSet.getString("tel"));
				  list.add(user);
			  }
			  request.setAttribute("list", list);
			  resultSet.close();
			  st.close();*/
			  
		      }catch (Exception e) {
			// TODO: handle exception
		    	  e.printStackTrace();
		}
		request.getRequestDispatcher("list.jsp").forward(request, response);
		
	}
	
	protected void doPost(String page1,HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		// TODO Auto-generated method stub
		//doGet(req, resp);
		
	}
}

前台代码

<div>
<form action="FindServlet">
		
		第${requestScope.currentPage}页/共${requestScope.countPage}页,共${requestScope.total }条记录
		 <a
			href="${pageContext.request.contextPath}/FindServlet?currentPage=1">首页</a><span> </span>
		<c:choose>
			<c:when test="${requestScope.currentPage==1}">
				上一页
			</c:when>
			<c:otherwise>
				<a
					href="${pageContext.request.contextPath}/FindServlet?currentPage=${requestScope.currentPage-1}">上一页</a>
			</c:otherwise>
		</c:choose>
		<%--计算begin和end --%>
		<c:choose>
			<%--如果总页数不足10，那么就把所有的页都显示出来 --%>
			<c:when test="${requestScope.countPage<=10}">
				<c:set var="begin" value="1" />
				<c:set var="end" value="${requestScope.countPage}" />
			</c:when>
			<c:otherwise>
				<%--如果总页数大于10，通过公式计算出begin和end --%>
				<c:set var="begin" value="${requestScope.currentPage-5}" />
				<c:set var="end" value="${requestScope.currentPage+4}" />
				<%--头溢出 --%>
				<c:if test="${begin<1}">
					<c:set var="begin" value="1"></c:set>
					<c:set var="end" value="10"></c:set>
				</c:if>
				<%--尾溢出 --%>
				<c:if test="${end>requestScope.countPage}">
					<c:set var="begin" value="${requestScope.countPage - 9}"></c:set>
					<c:set var="end" value="${requestScope.countPage}"></c:set>
				</c:if>
			</c:otherwise>
		</c:choose>
		<%--循环显示页码列表 --%>
		<c:forEach var="i" begin="${begin}" end="${end}">
			<c:choose>
				<c:when test="${i == requestScope.currentPage}">
				[${i}]
				</c:when>
				<c:otherwise>
					<a href="<c:url value ='/FindServlet
					?currentPage=${i}'/>">[${i}]</a>
				</c:otherwise>
			</c:choose>
		</c:forEach>
		<c:choose>
			<c:when test="${requestScope.currentPage==requestScope.countPage}">
				  下一页
			</c:when>
			<c:otherwise>
				<a
					href="${pageContext.request.contextPath}/FindServlet?currentPage=${requestScope.currentPage+1}"> 下一页</a>
			</c:otherwise>
		</c:choose>
		<span> </span><a
			href="${pageContext.request.contextPath}/FindServlet?currentPage=${requestScope.countPage}">尾页</a>
			
			<input type="number" name="currentPage" style="width:50px" class="c" min="1" max="${requestScope.countPage }" value="${requestScope.currentPage }" > 
			<input type="submit" value="Go" onclick="Page1()" >
			
			</form>
		</div>