需求说明：
• 演示最终分页效果
• 提供分页素材
• 分页的作用
• 数据量大，一页容不下
• 后台查询部分数据而不是全部数据
• 降低带宽使用，提高访问速度
• 分页的实现思路
• MVC四个层次都需要参与分页操作SXT SXT 练习2——理解PageBean
• 需求说明：
• 提供面向对象的PageBean，降低分页难度，实现功能重用
• 理解PageBean的属性和方法
• 分页的三个基本属性
• 1.每页几条记录size 可以有默认值5
• 2.当前页号 index 可以有默认值1
• 3.记录总数totalCount：没有默认值，需查询数据库获取真正记录总数
• 分页的其他属性
• 一共多少页 ：totalPageCount=totalCount/size+1
• 上一页 index-1 当前页1，上一页1
• 下一页 index+1 当前页是最后一页 下一页：还是最后一页
• 扩展
• 分页Bean还可以放要查询的数据 protected List<T> list;
• 分页Bean还可以放页码列表 [1] 2 3 4 5 private int[] numbers;SXT SXT 练习2——理解PageBean
• public class PageBean<T> {
private int size = 5;//每页显示记录
private int index = 1;// 当前页号
private int totalPageCount = 1;// 总页数
private int totalCount = 0;// 记录总数
private int[] numbers;//展示页数集合
protected List<T> list;//要显示到页面的数据集
//赋值记录总数
public void setTotalCount(int totalCount) {}
//根据记录总数计算总页数
private void setTotalPageCountByRs() {}
//根据总页数计算页面显示的页号范围
public void setNumbers(int totalPageCount) {}
• }SXT SXT
练习2——理解PageBean
• 需要根据当前页和总页数的关系确定显示的页码
• 情况1：总页数21，当前14
• 9 10 11 12 13 [14] 15 16 17 18
• 情况2：总页数21，当前1,2,3,4
• 1 2 [3] 4 5 6 7 8 9 10
• 情况3：总页数21，当前19,20,21
• 12 13 14 15 16 17 [18] 19 20 21
• 情况4：总页数不够10页
• 1 2 [3] 4 5SXT SXT
练习3——实现基本分页的后台操作
• 需求说明：
• 控制层
• 获取当前页号
• 将当前页号给PageBean
• 传递PageBean到业务层
• 业务层
• 获取记录总数
• 使用记录总数计算PageBean其他属性值
• 调用数据访问层获取当前页数据并给PageBean
• 数据访问层
• 分页查询语句 String sql = "select * from (select rownum r,e2.* from "
+ "( select e.* from student e order by score desc) e2 "
+ "where rownum<="+end+" ) where r>"+startSXT SXT
练习4——实现基本分页的页面显示
• 需求说明：
• 视图层显示分页
• 使用JSTL/EL完成分页数据显示
练习5——完善基本分页
• 需求说明：
• 数据访问层
• 理解分页查询语句：
• Oracle :复杂 三层子查询
SELECT u_id,m_id as id,m_date as pubTime,m_body as body,m_image as image,m_tranum as tranum,
m_comnum as comnum,m_colnum as colnum,m_like as likeN,to_char(m_date,'yyyy-mm-dd hh24:mi:ss') as str_pubTime FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM weibo_tab where u_id=#{param1} and m_state = 0 order by m_date desc) A
WHERE ROWNUM <= #{param3}
)
WHERE RN >= #{param2}
• MySQL：简单
select * from student limit 5,5//start,size
• 业务层
• 直接获取记录总数 select count (*) from student
• 视图层
• 改变每页记录数
• 直接跳到某一页
• 代码优化，提取JS方法
• 控制层
• 取当前页号和每页记录数
function change(index,size){
location.href="servlet/ShowAllServlet
?index="+index+"&size="+size;
}
练习6——带条件查询的分页
• 需求说明：
• 视图层：
• 查询表单
• 记忆查询条件
• 控制层：
• 获取表单数据，将表单数据（查询条件）传递到业务层
• 业务层：
• 获取符合查询条件的记录总数
• 获取符合查询条件的记录数据
• DAO层:
• 改变SQL查询语句，需要根据查询条件拼接SQL语句
练习7——完善带条件查询的分页
• 需求说明：
• 点击页码超链接的同时要提交表单
• 实现1：修改form的action属性
• document.forms[0].action="servlet/ShowAllServlet?index="+index+"&size="+size;
• 实现2：给表单添加hidden属性，表示index和size
• <input type="hidden" id="index" name="index" >
• <input type="hidden" id="size" name="size" >
• document.getElementById("index").value=index;
• document.getElementById("size").value=size;
• 实现直接输入页号并提交
• 与上个功能类似
• 更新删除后仍旧跳回当前页，而不是第一页
详细代码如下：
pageBean.java
package com.briup.common.util; import java.util.List; /**
* 分页的三个基本属性
* 1.每页几条记录size 可以有默认值5
* 2.当前页号 index 可以有默认值1
* 3.记录总数totalCount：不可能有默认值，需要查询数据库获取真正的记录总数
*
* 4.一共多少页 ：totalPageCount=totalCount/size+1
* 5 30 31 32 33 34 35
* 5.上一页 index-1 当前页1，上一页1
* 6.下一页 index+1 当前页是最后一页 下一页：还是最后一页
*
* 扩展
* 分页Bean还可以放要查询的数据 protected List<T> list;
* 分页Bean还可以放页码列表 [1] 2 3 4 5 private int[] numbers;
*
* @author Administrator
*
* @param <T>
*/
public class PageBean<T> {
private int size = ;//每页显示记录 //
private int index = ;// 当前页号
private int totalCount = ;// 记录总数 ok private int totalPageCount = ;// 总页数 ok private int[] numbers;//展示页数集合 //ok
protected List<T> list;//要显示到页面的数据集 /**
* 得到开始记录
* @return
*/
public int getStartRow() { return (index - ) * size;
} /**
* 得到结束记录
* @return
*/
public int getEndRow() { return index * size;
} /**
* @return Returns the size.
*/
public int getSize() {
return size;
} /**
* @param size
* The size to set.
*/
public void setSize(int size) {
if (size > ) {
this.size = size;
}
}
/**
* @return Returns the currentPageNo.
*/
public int getIndex() {
if (totalPageCount == ) { return ;
} return index;
} /**
* @param currentPageNo
* The currentPageNo to set.
*/
public void setIndex(int index) {
if (index > ) {
this.index = index;
}
} /**
* @return Returns the totalCount.
*/
public int getTotalCount() {
return totalCount;
} /**
* @param totalCount
* The totalCount to set.
*/
public void setTotalCount(int totalCount) {
if (totalCount >= ) {
this.totalCount = totalCount;
setTotalPageCountByRs();//根据总记录数计算总页�?
}
} public int getTotalPageCount() {
return this.totalPageCount;
} /**
* 根据总记录数计算总页�?
* 5
* 20 4
* 23 5
*/
private void setTotalPageCountByRs() {
if (this.size > && this.totalCount > && this.totalCount % this.size == ) {
this.totalPageCount = this.totalCount / this.size;
} else if (this.size > && this.totalCount > && this.totalCount % this.size > ) {
this.totalPageCount = (this.totalCount / this.size) + ;
} else {
this.totalPageCount = ;
}
setNumbers(totalPageCount);//获取展示页数集合
} public int[] getNumbers() {
return numbers;
} /**
* 设置显示页数集合
*
* 默认显示10个页码
* 41 42 43 44 [45 ] 46 47 48 49 50
*
*
* [1] 2 3 4 5 6 7 8 9 10
*
* 41 42 43 44 45 46 47 [48] 49 50
* @param totalPageCount
*/
public void setNumbers(int totalPageCount) {
if(totalPageCount>){
//!.当前数组的长度
int[] numbers = new int[totalPageCount>?:totalPageCount];//页面要显示的页数集合
int k =;
//
//1.数组长度<10 1 2 3 4 .... 7
//2.数组长度>=10
// 当前页<=6 1 2 3 4 10
// 当前页>=总页数-5 ......12 13 14 15
// 其他 5 6 7 8 9 当前页(10) 10 11 12 13
for(int i = ;i < totalPageCount;i++){
//保证当前页为集合的中�?
if((i>=index- (numbers.length/+) || i >= totalPageCount-numbers.length) && k<numbers.length){
numbers[k] = i+;
k++;
}else if(k>=numbers.length){
break;
}
} this.numbers = numbers;
} } public void setNumbers(int[] numbers) {
this.numbers = numbers;
} public List<T> getList() {
return list;
} public void setList(List<T> list) {
this.list = list;
} /*
public static int getTotalPageCount(int iTotalRecordCount, int iPageSize) {
if (iPageSize == 0) {
return 0;
} else {
return (iTotalRecordCount % iPageSize) == 0 ? (iTotalRecordCount / iPageSize) : (iTotalRecordCount / iPageSize) + 1;
}
}*/
}
pojo
Customer.java
package com.briup.bean;
import java.io.Serializable;
public class Customer implements Serializable{
private static final long serialVersionUID = -1415977267636644567L;
private Long id;
private String name;
private String password;
private String zip;
private String address;
private String telephone;
private String email;
public Customer() {
}
public Customer(Long id, String name, String password, String zip,
String address, String telephone, String email) {
super();
this.id = id;
this.name = name;
this.password = password;
this.zip = zip;
this.address = address;
this.telephone = telephone;
this.email = email;
}
public Customer(String name, String password, String zip,
String address, String telephone, String email) {
super();
this.name = name;
this.password = password;
this.zip = zip;
this.address = address;
this.telephone = telephone;
this.email = email;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getZip() {
return zip;
}
public void setZip(String zip) {
this.zip = zip;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getTelephone() {
return telephone;
}
public void setTelephone(String telephone) {
this.telephone = telephone;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}
dao
package com.briup.dao; import java.util.List; import com.briup.bean.Customer;
import com.briup.bean.Student; public interface StudentDao {
/**
* ��ѯ����ѧ��
* @return
*/
public List<Customer> findAll();
/**
* ��ѯָ����Χ��ѧ��
* @param start
* @param end
* @return
*/
public List<Customer> findStu(int start, int size);
/**
* ��ѯ��¼����
* @return
*/
public int findCount();
/**
* ��ѯ��ϲ�ѯ�����ļ�¼����
* @param name
* @param minScore
* @return
*/
public int findCount(String name);
/**
* ��ѯ��ϲ�ѯ������ָ��ҳ���ѧ��
* @param start
* @param end
* @param name
* @param minScore
* @return
*/
public List<Customer> findStu(int start, int size, String name); }
dao.impl
package com.briup.dao.impl; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List; import com.briup.bean.Customer;
import com.briup.bean.Student;
import com.briup.common.util.DBUtil;
import com.briup.dao.StudentDao; public class StudentDaoImpl implements StudentDao {
public List<Customer> findAll() {
Connection conn =DBUtil.getConnection();
Statement stmt =null;
ResultSet rs =null;
List<Customer> stuList = new ArrayList<Customer>();
try {
stmt =conn.createStatement();
rs = stmt.executeQuery("select * from student");
while(rs.next()){
Customer cus1 = new Customer();
cus1.setId(rs.getLong("customer_id"));
cus1.setAddress(rs.getString("address"));
cus1.setEmail(rs.getString("email"));
cus1.setName(rs.getString("name"));
cus1.setPassword(rs.getString("password"));
cus1.setTelephone(rs.getString("telephone"));
cus1.setZip(rs.getString("zip"));
stuList.add(cus1);
} } catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.closeAll(rs, stmt, conn);
}
return stuList;
} public List<Customer> findStu(int start, int size) {
Connection conn =DBUtil.getConnection();
PreparedStatement pstmt =null;
ResultSet rs =null;
List <Customer> stuList = new ArrayList<Customer>();
try {
String sql = "select * from tbl_customer limit ?,?";
pstmt =conn.prepareStatement(sql);
pstmt.setInt(, start);
pstmt.setInt(, size);
rs = pstmt.executeQuery(); while(rs.next()){
/*private Long id;
private String name;
private String password;
private String zip;
private String address;
private String telephone;
private String email;*/
Customer cus1 = new Customer();
cus1.setId(rs.getLong("customer_id"));
cus1.setAddress(rs.getString("address"));
cus1.setEmail(rs.getString("email"));
cus1.setName(rs.getString("name"));
cus1.setPassword(rs.getString("password"));
cus1.setTelephone(rs.getString("telephone"));
cus1.setZip(rs.getString("zip")); stuList.add(cus1);
} } catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.closeAll(rs, pstmt, conn);
}
return stuList;
} public int findCount() {
Connection conn =DBUtil.getConnection();
Statement stmt =null;
ResultSet rs =null;
List <Student> stuList = new ArrayList<Student>();
int count = ;
try {
stmt =conn.createStatement();
rs = stmt.executeQuery("select count(*) from tbl_customer");
rs.next();
count = rs.getInt();
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.closeAll(rs, stmt, conn);
} return count;
} public int findCount(String name) {
Connection conn =DBUtil.getConnection();
Statement stmt =null; ResultSet rs =null;
List <Student> stuList = new ArrayList<Student>();
int count = ;
try {
StringBuilder sql = new StringBuilder("select count(*) from tbl_customer where 1=1 ");
if(name != null && !"".equals(name)){
sql.append(" and name like '%"+name+"%'");
} stmt =conn.createStatement();
rs = stmt.executeQuery(sql.toString());
rs.next();
count = rs.getInt();
System.out.println("StudentDaoImpl.findCount()"+count);
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.closeAll(rs, stmt, conn);
} return count;
} public List<Customer> findStu(int start, int size, String name
) {
Connection conn =DBUtil.getConnection();
Statement stmt =null;
ResultSet rs =null;
PreparedStatement pstmt=null;
List<Customer> stuList = new ArrayList<Customer>();
try {
stmt =conn.createStatement();
StringBuilder sql = new StringBuilder("select * from tbl_customer cus where 1=1 ");
if(name != null && !"".equals(name)){
sql.append(" and name like '%"+name+"%'");
} sql.append(" order by score desc"); String sql2 = "select * from tbl_customer limit ?,?"; pstmt = conn.prepareStatement(sql2);
pstmt.setInt(, start);
pstmt.setInt(, size);
rs = pstmt.executeQuery();
while(rs.next()){
Customer cus1 = new Customer();
cus1.setId(rs.getLong("customer_id"));
cus1.setAddress(rs.getString("address"));
cus1.setEmail(rs.getString("email"));
cus1.setName(rs.getString("name"));
cus1.setPassword(rs.getString("password"));
cus1.setTelephone(rs.getString("telephone"));
cus1.setZip(rs.getString("zip"));
stuList.add(cus1);
} } catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.closeAll(rs, stmt, conn);
}
return stuList;
}
}
Service
package com.briup.service; import java.util.List; import com.briup.bean.Customer;
import com.briup.bean.Student;
import com.briup.common.util.PageBean; public interface StudentService { /**
* ��ѯ����ѧ��
* @return
*/
public List<Customer> findAll();
/**
* ��ɻ�ķ�ҳ�����������ѯ������
* @param pageBean
*/
public void findStu(PageBean<Customer> pageBean);
/**
* ��ɷ�ҳ���������ѯ������
* @param pageBean
*/
public void findStu(PageBean<Customer> pageBean, String name); }
Service.impl
package com.briup.service.impl; import java.util.List; import com.briup.bean.Customer;
import com.briup.bean.Student;
import com.briup.common.util.PageBean;
import com.briup.dao.StudentDao;
import com.briup.dao.impl.StudentDaoImpl;
import com.briup.service.StudentService; public class StudentServiceImpl implements StudentService { private StudentDao stuDao = new StudentDaoImpl(); public List<Customer> findAll() {
return this.stuDao.findAll();
} public void findStu(PageBean<Customer> pageBean) {
//��ѯ��ݿ���ȡ��¼����
//int totalCount = this.stuDao.findAll().size();//????
int totalCount = this.stuDao.findCount();
System.out.println("count="+totalCount);
//ʹ�ü�¼�������PageBean�е���������(totalCount,totalPageCount,numbers)���Ͳ�list����
pageBean.setTotalCount(totalCount); //����DAO���ȡָ��ҳ��ѧ����ݣ�������pageBean��list����
/*
*ÿҳsize = 5����¼
* �ڼ�ҳ ��ʼ��¼��>= �����¼��<= <
* 1 0 4 5
* 2 5 9 10
* 3 10 14 15
*
* index (index-1)*size index*size
*
*/
//int start = (pageBean.getIndex()-1)*pageBean.getSize();
//int end= pageBean.getIndex()*pageBean.getSize();
int start = pageBean.getStartRow();
int end = pageBean.getEndRow();
int size = pageBean.getSize();
List<Customer> list = this.stuDao.findStu(start,size);
pageBean.setList(list); } public void findStu(PageBean<Customer> pageBean, String name) {
//��ѯ��ݿ���ȡ��ϲ�ѯ�����ļ�¼����
int totalCount = this.stuDao.findCount(name);
System.out.println("count="+totalCount); //ʹ�ü�¼�������PageBean�е���������(totalCount,totalPageCount,numbers)���Ͳ�list����
pageBean.setTotalCount(totalCount); //����DAO���ȡָ��ҳ��ѧ����ݣ�������pageBean��list����
int start = pageBean.getStartRow();
int end = pageBean.getEndRow();
int size = pageBean.getSize();
List<Customer> list = this.stuDao.findStu(start,size,name);
pageBean.setList(list); } }
Servlet
package com.briup.web.servlet; import java.io.IOException; import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import com.briup.bean.Customer;
import com.briup.common.util.PageBean;
import com.briup.service.StudentService;
import com.briup.service.impl.StudentServiceImpl; public class ShowAllServlet extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8"); String sindex = request.getParameter("index"); // null ""
int index = ;
System.out.println(index);
try {
index = Integer.parseInt(sindex);//"5"
} catch (NumberFormatException e) {
e.printStackTrace();
} String ssize = request.getParameter("size"); // null ""
int size = ;
try {
size = Integer.parseInt(ssize);//"5"
} catch (NumberFormatException e) {
e.printStackTrace();
} String name = request.getParameter("name"); //
PageBean<Customer> pageBean = new PageBean<Customer>();
pageBean.setIndex(index);
pageBean.setSize(size);
StudentService stuService = new StudentServiceImpl();
//List<Student> stuList = stuBiz.findAll();
//stuService.findStu(pageBean);//����Ҫ����stuList����Ϊ����ҵ��㴦�?���е���ݶ���PageBean��
stuService.findStu(pageBean,name);
request.setAttribute("pageBean", pageBean);// !!!!!!!
request.setAttribute("name", name); // 3com.bjsxt request.getRequestDispatcher("/jsp/showAll.jsp").forward(request,
response);
} public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doGet(request, response);
} }
jsp
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>"> <title>查询并显示所有学生信息</title> <meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
<script type="text/javascript">
function changeIndex(index){
location.href="stu?index="+index;
} function changeSize(size){
//alert(size);
location.href= "stu?size="+size;
} function change2(index,size){
//location.href="servlet/ShowAllServlet?index="+index+"&size="+size;
document.forms[].action="stu?index="+index+"&size="+size;
document.forms[].submit();
} function change(index,size){
document.forms[].index.value = index;
//document.forms[0].size.value = size;
document.getElementById("size").value = size;
document.forms[].submit();
} </script>
</head> <body>
<hr>
<form action="stu" method="post" id="form1">
<table align="center">
<tr>
<td>姓名</td>
<td>
<input type="text" name="name" value="${name }">
<input type="hidden" name="index" value="">
<input type="hidden" id="size" name="size">
</td> <td><input type="submit" value="提交"></td>
</tr>
</table>
</form>
<hr> <!-- 显示所有学生 /stumanager/ -->
<table align="center" border="" width="60%">
<tr>
<th>学生 编号</th>
<th>学生姓名</th>
<th>学生年龄</th>
<th>学生成绩</th>
<th>vs.index</th>
<th>更新操作</th>
<th>删除操作</th>
</tr>
<c:forEach items="${pageBean.list}" var="stu" varStatus="vs">
<tr> <td>${stu.id }</td>
<td>${stu.name }</td>
<td>${stu.password}</td>
<td>${stu.zip }</td>
<td>${stu.address }</td>
<td>${stu.telephone }</td>
<td>${stu.email }</td>
<td>${vs.index }</td>
<td><a href="/stumanager/servlet/StudentServlet?operate=preupdate&sid=${stu.id}">更新</a></td>
<td><a href="/stumanager/servlet/StudentServlet?operate=delete&sid=${stu.id}">删除</a></td>
</tr>
</c:forEach>
<tr>
<td colspan="" align="center">
<a href="javascript:change(1,${pageBean.size })">首页</a>
<c:if test="${pageBean.index !=1 }">
<a href="javascript:change(${pageBean.index-1 },${pageBean.size })">上一页 </a>
</c:if>
<c:if test="${pageBean.index ==1 }">
上一页
</c:if>
<c:forEach items="${pageBean.numbers }" var="num">
<c:if test="${num ==pageBean.index }">
[<a href="javascript:change(${num },${pageBean.size })">${num }</a>]
</c:if>
<c:if test="${num != pageBean.index }">
<a href="javascript:change(${num },${pageBean.size })">${num }</a>
</c:if>
</c:forEach>
<c:if test="${pageBean.index != pageBean.totalPageCount }">
<a href="javascript:change(${pageBean.index+1 },${pageBean.size })">下一页</a>
</c:if>
<c:if test="${pageBean.index == pageBean.totalPageCount }">
下一页
</c:if>
<a href="javascript:change(${pageBean.totalPageCount },${pageBean.size })">末页</a>
每页
<select onchange="change(${pageBean.index},this.value)">
<c:forEach begin="" end="" step="" var="i">
<c:if test="${i==pageBean.size }">
<option value="${i }" selected="selected">${i }</option>
</c:if>
<c:if test="${i!=pageBean.size }">
<option value="${i }">${i }</option>
</c:if>
</c:forEach>
</select>
条记录
直接跳到第
<select onchange="change(this.value,${pageBean.size})">
<c:forEach begin="" end="${ pageBean.totalPageCount }" var="num">
<c:if test="${num == pageBean.index }">
<option value="${num }" selected="selected">${num }</option>
</c:if>
<c:if test="${num != pageBean.index }">
<option value="${num }">${num }</option>
</c:if>
</c:forEach>
</select>
页 共${pageBean.totalCount }条记录
</td>
</tr>
</table> </body>
</html>
分页细节分析见github。