在开发过程中,经常做的一件事,也是最基本的事,就是从数据库中查询数据,然后在客户端显示出来。当数据少时,可以在一个页面内显示完成。然而,如果查询记录是几百条、上千条呢?直接一个页面显示完全的话,表格得多长啊。。。。。。这时,我们可以用分页技术。
分页查询原理
在服务端分页。跳到第n页才查询、显示第n页内容。要点就是根据客户端表格的“页面”计算出数据库要查询的当前页面的第一条记录的位置。优点:实时性:跳页才查询。数据量小:只加载当前页的记录进行显示。
重点在于两条语句:select count(*) from ...:查询得到记录总条数
select * from .. limit currentPage,pageSize:查询从第currentPage条开始的pageSize条数据。
项目目录
视图界面代码(index.jsp)
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>分页查询</title>
<style type="text/css">
.box{
width: 36%;
margin:20px auto;
}
.box ul li{
list-style: none;
float: left;
}
a{
text-decoration: none;
}
</style>
</head>
<body>
<table border="1" cellspacing="0px" width="70%" style="margin: 80px auto 20px;">
<caption>用户信息</caption>
<tr >
<th width="7%" align="center">id </th>
<th width="16%" align="center">name </th>
<th width="7%" align="center">age</th>
<th width="20%" align="center">birthday</th>
<th width="20%" align="center">password</th>
<th width="20%" align="center">email</th>
</tr>
<c:forEach items="${pageUser.users }" var="user">
<tr>
<td width="7%" align="center">${user.id }</td>
<td width="16%" align="center">${user.name }</td>
<td width="7%" align="center">${user.age}</td>
<td width="20%" align="center">${ user.birthday}</td>
<td width="20%" align="center">${user.password}</td>
<td width="20%" align="center">${ user.email}</td>
</tr>
</c:forEach>
</table>
<div class="box">
<ul>
<%--再点击上一页的时候,要判断当前页是否是第一页 ,如果是,就保持第一页的状态,否则当前页减一 --%>
<li><a href="${pageContext.request.contextPath}/PageUserServlet?currentPage=${pageUser.currentPage==1?1:pageUser.currentPage-1}"><<上一页</a></li>
<li> 第${pageUser.currentPage }页/共${pageUser.totalPage }页 </li>
<%--再点击下一页的时候,要判断当前页是否是最后一页 ,如果是,就保持最后一页的状态,否则当前页加一 --%>
<li><a href="${pageContext.request.contextPath }/PageUserServlet?currentPage=${pageUser.currentPage==pageUser.totalPage?pageUser.totalPage:pageUser.currentPage+1}">下一页>></a></li>
</ul>
</div>
</body>
</html>
Servlet层(PageUserServlet.java)
package com.qianfeng.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.qianfeng.javaBean.PageUser;
import com.qianfeng.service.UserService;
import com.qianfeng.serviceImpl.UserServiceImpl;
public class PageUserServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=utf-8");
request.setCharacterEncoding("utf-8");
//获取当前页数
String currentPage = request.getParameter("currentPage");
//转化成int型
int cpage = Integer.parseInt(currentPage);
//每页的数量
int pageSize = 3;
//创建处理逻辑对象
UserService uService = new UserServiceImpl();
PageUser pUser = uService.findUserByPage(cpage,pageSize);
request.setAttribute("pageUser", pUser);
request.getRequestDispatcher("/index.jsp").forward(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
Service(UserServiceImpl.java)
package com.qianfeng.serviceImpl;
import java.sql.SQLException;
import java.util.List;
import com.qianfeng.dao.UserDao;
import com.qianfeng.daoImpl.UserDaoImpl;
import com.qianfeng.javaBean.PageUser;
import com.qianfeng.javaBean.User;
import com.qianfeng.service.UserService;
public class UserServiceImpl implements UserService {
@Override
public PageUser findUserByPage(int cpage, int pageSize) {
UserDao dao = new UserDaoImpl();
//获取数据总数
int totalUser;
PageUser pUser = null;
try {
totalUser = dao.findAllUser();
//计算出总页数
int totalPage = (int) Math.ceil(totalUser*1.0/pageSize);
//获取当前页的数据
List<User> list = dao.findUserByPage(cpage,pageSize);
pUser = new PageUser(cpage, totalPage, pageSize, totalUser, list);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return pUser;
}
}
dao层(UserDaoImpl.java)
package com.qianfeng.daoImpl;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.qianfeng.dao.UserDao;
import com.qianfeng.javaBean.User;
import com.qianfeng.util.C3P0Util;
public class UserDaoImpl implements UserDao {
/*
* 查询数据库中的数据量
*/
@Override
public int findAllUser() throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
long num = (Long) qr.query("select count(*) from user", new ScalarHandler(1));
return (int)num;
}
/*
* 过去当前页中的数据
*/
@Override
public List<User> findUserByPage(int cpage, int pageSize) throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
return qr.query("select * from user limit ?,?", new BeanListHandler<User>(User.class),(cpage-1)*pageSize,pageSize);
}
}
过滤器(PageFilter.java)
package com.qianfeng.filter;
import java.io.IOException;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
/*
* 过滤器:当第一次进入界面的时候,传递currentPage参数
*/
public class PageFilter implements Filter {
@Override
public void destroy() {
}
@Override
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
throws IOException, ServletException {
HttpServletRequest hsr = (HttpServletRequest) request;
hsr.getRequestDispatcher("/PageUserServlet?currentPage=1").forward(request, response);
chain.doFilter(request, response);
}
@Override
public void init(FilterConfig filterConfig) throws ServletException {
}
}