页面分页实现

分页： 页面上需要显示的查询结果太多了，应该分为多页显示

数据库分页: 根据用户想查看的页码去数据库中查询对应的数据 用户访问第一页时，查询0~9条记录 用户访问第二页时，查询10~19条记录 数据库分页语句 select * from tableName limit 0,9;

数据库分页的实现步骤:

进行需求分析根据需求建立模型 设计javabean class Page { private List pageData; // 页面显示的用户(10个) private int pageNum; // 当前页码 private int totalRecord; // 总记录数 private int totalPageNum; // 总页数 }实现dao getPageData() getTotalRecords()实现service 查询分页数据 Page getPage(int pageNum); pageData 查询数据库 调用dao pageNum 参数 totalRecord 查询数据库 调用dao totalPageNum 计算servlet+jsp 获得用户指定的页码 调用service查询分页数据 将分页数据转发给jsp显示

准备jar包： c3p0-0.9.2-pre5.jar： commons-dbutils-1.4.jar commons-logging-1.1.1.jar mchange-commons-java-0.2.3.jar mysql-connector-java-5.1.22-bin.jar jstl.jar standard.jar（引入jstl）

准备数据库：

create database pagedb; use pagedb; create table users( id int primary key auto_increment, username varchar(50), password varchar(32), gender varchar(1), age int ); insert into users(username, password,gender, age) values('Tim1','1234','男',10); insert into users(username, password,gender, age) values('Tim2','1234','女',11); insert into users(username, password,gender, age) values('Tim3','1234','男',12); insert into users(username, password,gender, age) values('Tim4','1234','女',13); insert into users(username, password,gender, age) values('Tim5','1234','男',14); insert into users(username, password,gender, age) values('Tim6','1234','女',15); insert into users(username, password,gender, age) values('Tim7','1234','男',16); insert into users(username, password,gender, age) values('Tim8','1234','女',17); insert into users(username, password,gender, age) values('Tim9','1234','男',18); insert into users(username, password,gender, age) values('Tim19','1234','男',19); insert into users(username, password,gender, age) values('Tim11','1234','男',20); insert into users(username, password,gender, age) values('Tim12','1234','女',21); insert into users(username, password,gender, age) values('Tim13','1234','男',22); insert into users(username, password,gender, age) values('Tim14','1234','女',23); insert into users(username, password,gender, age) values('Tim15','1234','男',24); insert into users(username, password,gender, age) values('Tim16','1234','女',25); insert into users(username, password,gender, age) values('Tim17','1234','男',26); insert into users(username, password,gender, age) values('Tim18','1234','女',27); insert into users(username, password,gender, age) values('Tim19','1234','男'28);

项目结构：

实现代码： User bean:

package com.page.bean; public class User { private int id; private String username; private String password; private String gender; private int age; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } }

Page Bean:

package com.page.bean; import java.util.List; public class Page { private List<?> pageData; // 页面实现的用户 private int pageNum; // 当前页码 private int totalRecord; // 总记录数 private int totalPageNum; // 总页数 private int firstPageNum; // 起始页码 private int lastPageNum; // 结束页码 public List<?> getPageData() { return pageData; } public void setPageData(List<?> pageData) { this.pageData = pageData; } public int getPageNum() { return pageNum; } public void setPageNum(int pageNum) { this.pageNum = pageNum; } public int getTotalRecord() { return totalRecord; } public void setTotalRecord(int totalRecord) { this.totalRecord = totalRecord; } public int getTotalPageNum() { return totalPageNum; } public void setTotalPageNum(int totalPageNum) { this.totalPageNum = totalPageNum; } public int getFirstPageNum() { return firstPageNum; } public void setFirstPageNum(int firstPageNum) { this.firstPageNum = firstPageNum; } public int getLastPageNum() { return lastPageNum; } public void setLastPageNum(int lastPageNum) { this.lastPageNum = lastPageNum; } }

c3p0-config.xml:

<c3p0-config> <!-- 默认配置，如果没有指定则使用这个配置 --> <default-config> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/pagedb</property> <property name="user">root</property> <property name="password">1234</property> <!-- 如果池中数据连接不够时一次增长多少个 --> <property name="acquireIncrement">5</property> <property name="initialPoolSize">20</property> <property name="minPoolSize">10</property> <property name="maxPoolSize">40</property> <property name="maxStatements">0</property> <property name="maxStatementsPerConnection">5</property> </default-config> </c3p0-config>

JdbcUtils:

package com.page.utils; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; public class JdbcUtils { private static ComboPooledDataSource dataSource; static { dataSource = new ComboPooledDataSource(); } public static DataSource getDataSource(){ return dataSource; } }

PageDao:

package com.page.dao; 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.page.bean.User; import com.page.utils.JdbcUtils; public class PageDao { //查询总记录数 public int getTotalRecords() throws SQLException{ String sql = "select count(1) from users"; QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource()); long totalRecords = (long) runner.query(sql, new ScalarHandler("count(1)")); return (int) totalRecords; } //查询分页数据 public List<User> getPageData(int start, int length) throws SQLException{ String sql = "select id,username,gender, age from users limit ?,?"; Object[] params = new Object[]{start, length}; QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource()); List<User> dataList = runner.query(sql, new BeanListHandler(User.class),params); return dataList; } }

PageService:

package com.page.service; import java.sql.SQLException; import java.util.List; import com.page.bean.Page; import com.page.bean.User; import com.page.dao.PageDao; public class PageService { private PageDao pageDao = new PageDao(); public Page getPage(int pageNum, int count) throws SQLException { // 查询总记录数 int totalRecord = pageDao.getTotalRecords(); // 计算总页数 11/10 =1 11+9/10=2 19+9/10=2 20+9/10=2 int totalPageNum = (totalRecord + count - 1) / count; // 查询分页显示的用户 // 1 0 2 10 3 20 int start = (pageNum - 1) * count; List<User> pageData = pageDao.getPageData(start, count); //页数很多的时候的，只显示前5页和最后5页的处理 // 计算 起始页码和结束页码 int firstPageNum = pageNum - 4; int lastPageNum = pageNum + 5; // 特殊情况判断 if (firstPageNum < 1) { firstPageNum = 1; lastPageNum = 10; } if (lastPageNum > totalPageNum) { lastPageNum = totalPageNum; firstPageNum = lastPageNum - 9; } if (totalPageNum < 10) { firstPageNum = 1; lastPageNum = totalPageNum; } Page page = new Page(); page.setPageData(pageData); page.setPageNum(pageNum); page.setTotalPageNum(totalPageNum); page.setTotalRecord(totalRecord); page.setFirstPageNum(firstPageNum); page.setLastPageNum(lastPageNum); return page; } }

UserListServlet:

package com.page.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.page.bean.Page; import com.page.service.PageService; @WebServlet("/userListServlet") public class UserListServlet extends HttpServlet { private static final long serialVersionUID = 1L; protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request, response); } protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); response.setContentType("text/html;charset=UTF-8"); // 1 获得请求参数 int pageNum = 1; String pageNumValue = request.getParameter("pageNum"); if (pageNumValue != null) { pageNum = Integer.parseInt(pageNumValue); } // 2 调用service PageService pageService = new PageService(); try { Page page = pageService.getPage(pageNum, 5);//5 每页显示的记录数 // 3 请求转发 request.setAttribute("page", page); request.getRequestDispatcher("/WEB-INF/pages/listuser.jsp").forward(request, response); } catch (SQLException e) { throw new RuntimeException(e); } } }

listUser jsp:

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <%@ 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> <script type="text/javascript"> function jump(pageNum){ window.location = "${pageContext.request.contextPath}/userListServlet?pageNum=" + pageNum; } function go() { var num = document.getElementById("num").value; var totalPageNum = ${page.totalPageNum}; if(num==null || num.match(/^[1-9][0-9]*$/)==null || num>totalPageNum) { num = 1; } jump(num); } </script> </head> <br><b>分页显示用户</b><hr> <body style="padding-left: 20%;padding-right: 20%"> <table border="1" width="600px" > <tr> <td>编号</td> <td>用户名</td> <td>性别</td> <td>年龄</td> </tr> <c:forEach items="${page.pageData}" var="user" varStatus="vs"> <tr> <td>${user.id}</td> <td><c:out value="${user.username}" ></c:out> </td> <td><c:out value="${user.gender}"></c:out> </td> <td><c:out value="${user.age}"></c:out> </td> </tr> </c:forEach> </table> <br><br> <a href="javascript:jump(1)">首页</a> <c:if test="${page.pageNum>1 }"> <a href="javascript:jump(${page.pageNum-1 })">上一页</a> </c:if> <c:forEach var="num" begin="${page.firstPageNum }" end="${page.lastPageNum }"> <c:choose> <c:when test="${num==page.pageNum }"> [ ${num } ] </c:when> <c:otherwise> [ <a href="javascript:jump(${num })">${num }</a> ] </c:otherwise> </c:choose> </c:forEach> <c:if test="${page.pageNum<page.totalPageNum }"> <a href="javascript:jump(${page.pageNum+1 })">下一页</a> </c:if> <a href="javascript:jump(${page.totalPageNum })">尾页</a> <br> 当前第 ${requestScope.page.pageNum } 页 总共 ${page.totalPageNum } 页 总共 ${page.totalRecord } 条记录 跳转至 <input id="num" type="text" value="${page.pageNum }" style="width:50px;" /> <input type="button" value="GO" onclick="go()" /> </body> <br><hr> </html>

页面效果：