jsp+selvlet+jdbc分页优化

系统管理员 2022-05-19 06:27 47阅读 0赞

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

**分页种类;**

逻辑分页

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

物理分页

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

**分页的原理**

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

CurrentPage 当前页

PageSize 每页显示的数量

**Sql****执行后出来的参数：**

**Total 总记录数：**

SELECTCOUNT(\*) 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>

![70][]

[70]: /images/20220519/b5c36fbc3c8548d290828bb3de631bba.png