【知了堂学习笔记】分页查询—第一章

第一章

很多时候，我们查询有很多条记录，一个网页往往不装不下的，我们要怎样把页面进行分配呢？这个时候就需要分页了！

技术要求：JSP+Servlet+JSTL+JDBC

        首先，我们看一下目录结构

第一步，我们需要创建一个Users对象，把用户的基本信息封装起来

package com.jie.bean;public class Users {private int id;private String name;private String sex;private int cell;private String message;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public int getCell() {return cell;}public void setCell(int cell) {this.cell = cell;}public String getMessage() {return message;}public void setMessage(String message) {this.message = message;}@Overridepublic String toString() {return "Users [id=" + id + ", name=" + name + ", sex=" + sex + ", cell=" + cell + ", message=" + message + "]";}}

第二步，我们需要准备一个PageBean，为了重用性，我们这里选择用了泛型类。这个JavaBean主要封装了页码的一些属性，包括当前页码，总记录数(通过数据库的count函数获取)，每页记录数（即每页有几条记录，这里自定义设置），总页数（总记录数/每页记录数），当前的记录，这里用了一个集合把多条记录装进来。

package com.jie.bean; import java.util.List;public class PageBean <T>{private int pc;//当前页码private int tp;//总页数private int tr;//总记录数private int ps;//每页记录数private List<T> beanList;//当前页的记录public int getPc() {return pc;}public void setPc(int pc) {this.pc = pc;}public int getTp() {tp = tr/ps;return tr%ps==0?tp:tp+1;}public int getTr() {return tr;}public void setTr(int tr) {this.tr = tr;}public int getPs() {return ps;}public void setPs(int ps) {this.ps = ps;}public List<T> getBeanList() {return beanList;}public void setBeanList(List<T> beanList) {this.beanList = beanList;}}

第三步，我们写Dao层。怎么分页呢？其实这层很关键，MySQL中有一个分页查询语句 String sql = "select * from users limit ?,?" 这个能查出当前记录到后面参数长度的记录，比如 String sql = "select * from users limit 5,10"，表示查出从5开始的10条记录。这里的pc，ps需要Servlet页面传过来。后面的while循环，我们需要把从数据库的结果封装到Users对象中，然后把Users存进List集合，最后把list，pb，pc，tr（用count函数得到）封装到PageBean对象中，返回给Servlet。

package com.jie.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.jie.bean.PageBean;import com.jie.bean.Users;import com.jie.utils.JDBC;public class ListDao {public PageBean<Users> findAll(int pc,int ps) throws SQLException {Connection conn = JDBC.getConnection();String sql = "select * from users limit ?,?";PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.setInt(1, ps*(pc-1));pstmt.setInt(2, ps);ResultSet rs = pstmt.executeQuery();PageBean<Users> pb = new PageBean<>();List<Users> list =new ArrayList<Users>();while(rs.next()){Users u = new Users();u.setId(rs.getInt(1));u.setName(rs.getString(2));u.setSex(rs.getString(3));u.setCell(rs.getInt(4));u.setMessage(rs.getString(5));list.add(u);}pb.setBeanList(list);pb.setPc(pc);pb.setPs(ps);pb.setTr(getTr());conn.close();return pb;}//获取总记录数public int getTr() throws SQLException {int tr = 0;Connection conn = JDBC.getConnection();String sql = "select count(*) from users";PreparedStatement pstmt = conn.prepareStatement(sql);ResultSet rs = pstmt.executeQuery();while(rs.next()) { tr = rs.getInt(1);}conn.close();return tr;}}

第四步，我们写Servlet，调用dao的findAll，需要两个参数，第一个是pc，即当前的页码，这个我们从JSP页面获取，ps这里我们设置的是为10，即每页显示10条记录。我们把得到的PageBean对象保存到request域中，转发给list.jsp页面

package com.jie.servlet;import java.io.IOException;import java.sql.SQLException;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.jie.bean.PageBean;import com.jie.bean.Users;import com.jie.dao.ListDao;/** * Servlet implementation class ListServlet */@WebServlet("/ListServlet")public class ListServlet extends HttpServlet {private static final long serialVersionUID = 1L; protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {int pc = Integer.parseInt(request.getParameter("pc"));int ps =10;ListDao dao = new ListDao();try {PageBean<Users> pb = dao.findAll(pc, ps);request.setAttribute("pageBean", pb);request.getRequestDispatcher("list.jsp").forward(request, response);} catch (SQLException e) {e.printStackTrace();}}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {doGet(request, response);}}

最后一步，准备写JSP页面，这个用EL表达式取出request域的值并进行循环遍历，显示在页面上。这里比较重要的是页码的处理，详情请看代码。

<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%><%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>Insert title here</title><style type="text/css">body {background: url("bg3.jpg");}table {margin: 0 auto;color: white;}table td {width: 100px;height: 30px;text-align: center;}.menu {width: 350px;margin-left: 500px;margin-top: 30px;overflow: hidden;}a {text-decoration: none;color: white;opacity: 0.8;}</style></head><body><table border="1" cellspacing="0"><c:forEach items="${pageBean.beanList}" var="entry"><tr><td><c:out value="${entry.id}" /></td><td><c:out value="${entry.name}" /></td><td><c:out value="${entry.sex}" /></td><td><c:out value="${entry.cell}" /></td><td><c:out value="${entry.message}" /></td></tr></c:forEach></table><div class="menu"><a href="ListServlet?pc=1">首页</a><c:if test="${pageBean.pc>1}"><a href="ListServlet?pc=${pageBean.pc-1}">上一页</a></c:if><c:choose><%--如果总页数不足5页 --%><c:when test="${pageBean.tp<=5}"><c:set var="begin" value="1" /><c:set var="end" value="${pageBean.tp}" /></c:when><%--如果总页数大于5页 --%><c:otherwise><c:set var="begin" value="${pageBean.pc-2}" /><c:set var="end" value="${pageBean.pc+2}" /><%--头溢出 --%><c:if test="${begin<1}"><c:set var="begin" value="1" /><c:set var="end" value="5" /></c:if><%--尾溢出 --%><c:if test="${end>pageBean.tp}"><c:set var="begin" value="${pageBean.tp-4}" /><c:set var="end" value="${pageBean.tp}" /></c:if></c:otherwise></c:choose><%--遍历循环页码列表 --%><c:forEach var="i" begin="${begin }" end="${end }"><c:choose><c:when test="${i eq pageBean.pc}">${i}</c:when><c:otherwise><a href="ListServlet?pc=${i}">${i}</a></c:otherwise></c:choose></c:forEach><c:if test="${pageBean.pc<pageBean.tp}"><a href="ListServlet?pc=${pageBean.pc+1}">下一页</a></c:if><a href="ListServlet?pc=${pageBean.tp}">尾页</a>${pageBean.pc}/${pageBean.tp}</div></body></html>

效果图