文章目录
前言
忘记写分页功能了,最后的优化了
一、查询语句
首先我们要知道分页功能是怎么查询语句的,这里是数据库数据,我要查询出来前两条数据
sql语句:第0条开始,查询出两条(第二页我从3开始,查询2条,这样实现分页查询)
这是查询出来数据的总条数,需要它来算分页
select count(*) as pageTotal from books
二、实现分页代码
1.创建page实现类
数据太少了,所以一页分了5条,正常是10条
package com.zy.bean;
/**
* 类名:Page
* 描述:行动是成功的阶梯,行动越多,登得越高。
* 作者:劫恋李
* 日期:2021/7/21 15:26
*/
public class Page {
private int pageNum=1;//当前页码,默认第一页
private int pageSize=5;//每页显示条数,5条
private int pageSum;//总页数,需要计算
private int pageTotal;//总条数,查数据库
public Page() {
}
public Page(int pageNum, int pageSize, int pageSum, int pageTotal) {
this.pageNum = pageNum;
this.pageSize = pageSize;
this.pageSum = pageSum;
this.pageTotal = pageTotal;
}
public int getPageNum() {
return pageNum;
}
public void setPageNum(int pageNum) {
this.pageNum = pageNum;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getPageSum() {
//总条数取余,每页显示条数有余数商就多+1,没有余数就是商
pageSum=pageTotal%pageSize==0 ? pageTotal/pageSize:(pageTotal/pageSize+1);
return pageSum;
}
public void setPageSum(int pageSum) {
this.pageSum = pageSum;
}
public int getPageTotal() {
return pageTotal;
}
public void setPageTotal(int pageTotal) {
this.pageTotal = pageTotal;
}
}
2.书写分页查询语句
这里要写的查询语句和之前查询不一样,这里要查出总条数,还有页数(页数是算出来的,实现类已经算好了);
public List selectAllPage(Page page) throws SQLException, ClassNotFoundException {
//2
List<Books> list=new ArrayList<Books>();
connection =Conn.getcoon();
//偏移量
String sql="select * from books limit ?,?";
preparedStatement=connection.prepareStatement(sql);
//1-1*10=0 2-1*10=10从10后面取
preparedStatement.setInt(1,(page.getPageNum()-1)*page.getPageSize());
preparedStatement.setInt(2,page.getPageSize());
resultSet = preparedStatement.executeQuery();
while (resultSet!=null&&resultSet.next()){
Books books = new Books(resultSet.getInt("bid"),
resultSet.getString("bookname"),
resultSet.getString("author"),
resultSet.getString("images"),
resultSet.getString("press"),
resultSet.getInt("price"),
resultSet.getString("pubtime"));
list.add(books);
}
//3
return list;
}
public int selectTotal() throws SQLException, ClassNotFoundException {
//2
int pageTotal=0;
connection =Conn.getcoon();
//偏移量
String sql="select count(*) as pageTotal from books";
preparedStatement=connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet!=null&&resultSet.next()){
pageTotal=resultSet.getInt("pageTotal");
}
//3
return pageTotal;
}
3.链接
main.jsp 添加链接
4.在servlet 包下创建BooksAllPage
重写doget
package com.zy.servlet;
import com.zy.Dao.BooksDao;
import com.zy.bean.Books;
import com.zy.bean.Page;
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 java.io.IOException;
import java.sql.SQLException;
import java.util.List;
/**
* 类名:BooksAllPage
* 描述:行动是成功的阶梯,行动越多,登得越高。
* 作者:劫恋李
* 日期:2021/7/21 21:42
*/
@WebServlet("/BooksAllPage")
public class BooksAllPage extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String pageNumStr = req.getParameter("pageNum");
Page page=new Page();
if(pageNumStr!=null&&!pageNumStr.equals("")){
int pageNum=Integer.parseInt(pageNumStr);
page.setPageNum(pageNum);//页码数
}
BooksDao booksDao = new BooksDao();
try {
//查询所有商品,获取集合
List<Books> list = booksDao.selectAllPage(page);
//查询总条数
int pageTotal = booksDao.selectTotal();
page.setPageTotal(pageTotal);
//把page存入request,转发到页面可以取出来
req.setAttribute("p",page);
req.setAttribute("books",list);
//转到jsp,固定方法getRequestDispatcher
req.getRequestDispatcher("booksallPage.jsp").forward(req,resp);
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
5.创建booksallPage.jsp
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%--
Created by 劫恋李
User: dell
Date: 2021/7/21
Time: 21:45
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";
%>
<html>
<head>
<base href="<%=basePath %>"/>
<title>Title</title>
</head>
<body>
<center>
<table border="1" width="80%">
<tr>
<th width="10%">书名编号</th>
<th width="10%">图书名</th>
<th width="10%">作者</th>
<th width="20%" >图片</th>
<th width="10%">出版社</th>
<th width="10%">价格</th>
<th width="20%">发布时间</th>
<th width="10%">操作</th>
</tr>
<c:forEach items="${requestScope.books}" var="u">
<tr>
<td>${u.bid}</td>
<td>${u.bookname}</td>
<td>${u.author}</td>
<td style="text-align: center">
<img src="upload/${u.images}" width="80px" height="60px" >
</td>
<td>${u.press}</td>
<td>${u.price}</td>
<td>${u.pubtime}</td>
<td>
<a href="BooksGoupdate?bid=${u.bid}">修改</a>
<a href="BooksDel?bid=${u.bid}" onclick="return window.confirm('是否确定删除?')">删除</a>
</td>
</tr>
</c:forEach>
<tr>
<td colspan="8" style="text-align: center" >
<a href="BooksAllPage?pageNum=1">首页</a>
<a href="BooksAllPage?pageNum=${p.pageNum>1 ? (p.pageNum-1) : 1}">上一页</a>
<a href="BooksAllPage?pageNum=${p.pageNum<p.pageSum ? (p.pageNum+1) : p.pageSum}">下一页</a>
<a href="BooksAllPage?pageNum=${p.pageSum}">尾页</a>
当前${p.pageNum}页 共 ${p.pageSum}页 共${p.pageTotal}条
</td>
</tr>
</table>
</center>
</body>
</html>
6.测试效果
总结
将遗忘的分页功能完成,源码都已经在文章里面,不上传代码了,主要是公式比较难理解。这里基本难理解的地方都有注释。
网上我查了一下有好多种,可以去学习一下。
结束。