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

第一章

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

技术要求：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;
	}
	@Override
	public 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>

效果图