分页显示数据
程序员文章站
2024-03-24 19:03:16
...
从数据库中取出的数据,分页的显示到页面上
- 创建数表
- 创建web动态工程
- 创建Bean对象,一个数据StudentBean,一个分页数据相关的PageBean
- 显示页面编写
- Servlet及对应后台数据处理类的编写,返回一个PageBean对象,表示要显示的那一页数据
- 运行显示
创建数表student
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`sname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学生姓名',
`sgender` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学生性别',
`sage` int(11) NULL DEFAULT NULL COMMENT '学生年龄',
PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `student` VALUES (1, '张三', '男', 22);
INSERT INTO `student` VALUES (2, '李四', '男', 48);
INSERT INTO `student` VALUES (3, '王五', '男', 30);
INSERT INTO `student` VALUES (4, '赵六', '男', 39);
INSERT INTO `student` VALUES (5, '桥七', '男', 38);
INSERT INTO `student` VALUES (6, '小明', '男', 29);
INSERT INTO `student` VALUES (7, '小白', '女', 20);
INSERT INTO `student` VALUES (8, '小黑', '男', 19);
INSERT INTO `student` VALUES (9, '小李', '女', 20);
INSERT INTO `student` VALUES (10, '小红', '女', 39);
INSERT INTO `student` VALUES (11, '你好', '男', 49);
SET FOREIGN_KEY_CHECKS = 1;
创建web动态工程
创建Bean对象,一个数据StudentBean,一个分页数据相关的PageBean
package com.gang.domain;
public class StudentBean {
private int sid;
private String sname;
private String sgender;
private int sage;
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getSgender() {
return sgender;
}
public void setSgender(String sgender) {
this.sgender = sgender;
}
public int getSage() {
return sage;
}
public void setSage(int sage) {
this.sage = sage;
}
@Override
public String toString() {
return "StudentBean [sid=" + sid + ", sname=" + sname + ", sgender=" + sgender + ", sage=" + sage + "]";
}
}
package com.gang.domain;
import java.util.List;
/**
* 这是一个用于封装了分页的数据。
* 里面包含:
*
* 该页的的学生集合数据
* 总的记录数
* 总的页数
* 当前页
* 每页显示的记录数
*/
public class PageBean<T> {
private int currentPage; //当前页
private int totalPage;//总页数
private int pageSize;//每页的记录数
private int totalSize; //总的记录数
private List<T> list; //当前页的学生集合
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotalSize() {
return totalSize;
}
public void setTotalSize(int totalSize) {
this.totalSize = totalSize;
}
public List<T> getList() {
return list;
}
public void setList(List<T> list) {
this.list = list;
}
}
显示页面编写
- index.jsp中直接写一个连接,跳转至请求分页数据servlet,
- servlet中直接封装一个PageBean对象,转发给list_page.jsp用el表达式显示
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!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>首页</title>
</head>
<body>
<h3><a href="StudentListPageServlet?currentPage=1">分页显示所有学生</a></h3>
</body>
</html>
<%@ 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>学生列表页面</title>
</head>
<body>
<table border="1" width="700">
<tr align="center">
<td>编号</td>
<td>姓名</td>
<td>性别</td>
<td>年龄</td>
</tr>
<!-- 遍历pageBean对象中的学生集合 -->
<c:forEach items="${pageBean.list }" var="stu">
<tr align="center">
<td>${stu.sid }</td>
<td>${stu.sname }</td>
<td>${stu.sgender }</td>
<td>${stu.sage }</td>
</tr>
</c:forEach>
<tr>
<td colspan="8">
第 ${pageBean.currentPage } / ${pageBean.totalPage }
每页显示${pageBean.pageSize }条
总的记录数${pageBean.totalSize }
<!-- 首页即上一页显示 ,当当前页不等于第一页时才显示 -->
<c:if test="${pageBean.currentPage !=1 }">
<a href="StudentListPageServlet?currentPage=1">首页</a>
| <a href="StudentListPageServlet?currentPage=${pageBean.currentPage-1 }">上一页</a>
</c:if>
<!-- 输出中间页码 -->
<c:forEach begin="1" end="${pageBean.totalPage }" var="i">
<c:if test="${pageBean.currentPage == i }">
${i }
</c:if>
<c:if test="${pageBean.currentPage != i }">
<a href="StudentListPageServlet?currentPage=${i }">${i }</a>
</c:if>
</c:forEach>
<!-- 尾页即下一页显示 ,当当前页不等于最后一页时才显示 -->
<c:if test="${pageBean.currentPage !=pageBean.totalPage }">
<a href="StudentListPageServlet?currentPage=${pageBean.currentPage+1 }">下一页</a> |
<a href="StudentListPageServlet?currentPage=${pageBean.totalPage }">尾页</a>
</c:if>
</td>
</tr>
</table>
</body>
</html>
Servlet及对应后台数据处理类的编写,返回一个PageBean对象,表示要显示的那一页数据
- StudentDao 接口中定义两个方法,一个接受参数为要显示的页码(currentPage),返回值为要显示的那一页所有的学生信息,一个返回数据库一共有多少条记录。
- StudentDaoImpl中具体实现StudentDao中的方法,通过select * from student limit ? offset ?,查询对应页的数据,第一个问号,代表一页返回多少条记录 , 第二个问号, 跳过前面的多少条记录,如果是select * from student limit ?,?,两个参数意思相反。
- Servlet接收当前页码,封装一个PageBean对象,转发到list_page.jsp中显示
package com.gang.dao;
import java.sql.SQLException;
import java.util.List;
import com.gang.domain.StudentBean;
public interface StudentDao {
int PAGE_SIZE = 5; //一页显示多少条记录
//根据页码,查询当前页要显示的所有学生信息
List<StudentBean> findListPage(int currentPage) throws SQLException;
//查询一共有多少条数据
int findCount() throws SQLException;
}
package com.gang.dao.impl;
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.gang.dao.StudentDao;
import com.gang.domain.StudentBean;
import com.gang.util.JDBCUtil;
public class StudentDaoImpl implements StudentDao {
@Override
public List<StudentBean> findListPage(int currentPage) throws SQLException {
QueryRunner runner=new QueryRunner(JDBCUtil.getDataSource());
return runner.query("select * from student limit ? offset ?",
new BeanListHandler<StudentBean>(StudentBean.class) , PAGE_SIZE , (currentPage-1)*PAGE_SIZE);
}
@Override
public int findCount() throws SQLException{
QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
//用于处理 平均值 、 总的个数。
Long result = (Long) runner.query("select count(*) from student" , new ScalarHandler() );
return result.intValue();
}
}
package com.gang.servlet;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
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.gang.dao.StudentDao;
import com.gang.dao.impl.StudentDaoImpl;
import com.gang.domain.PageBean;
import com.gang.domain.StudentBean;
@WebServlet("/ListPageServlet")
public class ListPageServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//得到当前页数
int currentPage=Integer.parseInt(request.getParameter("currentPage"));
//封装PageBean
PageBean<StudentBean> pageBean=new PageBean<StudentBean>();
try {
StudentDao dao=new StudentDaoImpl();
int totalSize=dao.findCount();
int pageSize=StudentDao.PAGE_SIZE;
//总记录200 , 每页10 ==共20 总记录201 , 每页10 = 共21 所以201 % 10 == 0 ?201 / 10 :201 % 10 + 1
int totalPage=((totalSize/pageSize)==0?totalSize/pageSize:(totalSize/pageSize)+1);
List<StudentBean> list=dao.findListPage(currentPage);
pageBean.setCurrentPage(currentPage);
pageBean.setTotalSize(totalSize);
pageBean.setTotalPage(totalPage);
pageBean.setPageSize(pageSize);
pageBean.setList(list);
} catch (SQLException e) {
e.printStackTrace();
}
//存储转发显示
request.setAttribute("pageBean", pageBean);
request.getRequestDispatcher("list_page.jsp").forward(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
运行结果
页码显示优化:
以上方法只能是把所有页码都显示出来,如果页码太多显示效果不好。
可以,实现每页显示时,只显示指定的页数
解决:
循环打印页码时,先计算出开始页码begin,和结束页码end,然后再输出
在PageBean中添加begin和end并添加set方法,在StudentDao中添加SHOW_SIZE表示每页显示的页码数
思路:
(假设每页显示10个页码SHOW_SIZE=10,要求前5,后4)
- 如果总页数小于要显示的SHOW_SIZE则之则begin=1,end=totalPage
否则begin=currentPage-5,end=currentPage+4; - 处理begin为负则前面不够5,则begin=1,后面补齐end=SHOW_SIZE。如果end>totalPage则说明后面不够4个,则end=totalPage,begin=totalPage-SHOW_SIZE+1。
//计算开始显示的页码,和结束的页码,一次显示
int begin;
int end;
if(totalPage<=StudentDao.SHOW_SIZE){
begin=1;
end=totalPage;
}else{
begin=currentPage-5;
end=currentPage+4;
if(begin<1){
begin=1;
end=StudentDao.SHOW_SIZE;
}
if(end>totalPage){
end=totalPage;
begin=totalPage-StudentDao.SHOW_SIZE+1;
}
}