分页:
页面上需要显示的查询结果太多了,应该分为多页显示
数据库分页:
根据用户想查看的页码去数据库中查询对应的数据
用户访问第一页时,查询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>
页面效果: