当前位置：首页 > news >正文

分页功能设计

news 2025/9/3 7:27:13

初始记录行的偏移量是 0(而不是 1)
limit offset,rowcount
limit 偏移到那个位置offset，往下数多少个rowcount
做分页首先要规定每页有多少条
每页有5条，取出第1页数据 limit 0,5
每页有5条，取出第2页数据 limit 5,5
每页有5条，取出第3页数据 limit 10,5

pageSize: 5 pageNo: 1 2 3
limit (pageNo-1)*pageSize,pageSize

在这里插入图片描述
limit 中offset代表偏移到哪个位置，rows代表往下数多少个
pageNo, pageSize 1,5 2,5 3,5
limit (pageNo-1)*pageSize ,pageSize
pageNo limit
1 0,5
2 5,5
3 10,5

分页常见的问题：
1.封装一个分页类需要哪些信息（也是面向对象封装体现的很好的例子）
2.分页需要发送哪两条sql语句

查询当前页的数据 select id,name,age,gender from student limit 0,5;
查询总的数量，目的是为了算总的页数 select count(*) from student;

package com.easy.web.util;import com.easy.web.pojo.Student;import java.util.List;public class PageInfo {private List<Student> list;private Integer totalPage;private Integer pageNo;private Integer pageSize;public PageInfo() {}public PageInfo(List<Student> list, Integer totalPage, Integer pageNo, Integer pageSize) {this.list = list;this.totalPage = totalPage;this.pageNo = pageNo;this.pageSize = pageSize;}public List<Student> getList() {return list;}public void setList(List<Student> list) {this.list = list;}public Integer getTotalPage() {return totalPage;}public void setTotalPage(Integer totalPage) {this.totalPage = totalPage;}public Integer getPageNo() {return pageNo;}public void setPageNo(Integer pageNo) {this.pageNo = pageNo;}public Integer getPageSize() {return pageSize;}public void setPageSize(Integer pageSize) {this.pageSize = pageSize;}@Overridepublic String toString() {return "PageInfo{" +"list=" + list +", totalPage=" + totalPage +", pageNo=" + pageNo +", pageSize=" + pageSize +'}';}
}

if (method == null || method.equals("")) {method = "selectByPage";}
……case "selectByPage":selectByPage(req,resp);break;
……
// /student?method=selectByPage&pageNo=1&pageSize=10private void selectByPage(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {System.out.println("StudentServlet.selectByPage");String pageNo = req.getParameter("pageNo");String pageSize = req.getParameter("pageSize");//pageNo和pageSize是我们在url中手动输入的，但每次都这样的话会很麻烦，所以我们设置默认值if (pageNo == null || pageNo.equals("")){pageNo = "1";}if (pageSize == null || pageSize.equals("")){pageSize = "5";}PageInfo pageInfo = studentService.selectByPage(Integer.parseInt(pageNo), Integer.parseInt(pageSize));System.out.println(pageInfo);req.setAttribute("pageInfo",pageInfo);req.getRequestDispatcher("student_list.jsp").forward(req,resp);}

package com.easy.web.service;import com.easy.web.pojo.Student;
import com.easy.web.util.PageInfo;import java.util.List;public interface IStudentService {//有关Student类的方法（增删改查）//与Dao层相同List<Student> selectAll();void deleteById(Integer id);void add(Student student);Student selectById(Integer id);void update(Student student);PageInfo selectByPage(int pageNo, int pageSize);
}

    @Overridepublic PageInfo selectByPage(int pageNo, int pageSize) {//第一个sql:查询当前页的数据int offset = (pageNo - 1) * pageSize;List<Student> list = studentDao.selectByPage(offset,pageSize);//第二个sql:查询总的数量int totalCount = studentDao.selectTotalCount();int totalPage = (int)Math.ceil((double) totalCount / pageSize);PageInfo pageInfo = new PageInfo(list,totalPage,pageNo,pageSize);return pageInfo;}

package com.easy.web.dao;import com.easy.web.pojo.Student;import java.util.List;//Dao数据访问层
//分离数据访问逻辑与业务逻辑，降低两者之间的耦合度。
//接口里面列出来的是能提供的所有功能的清单
public interface IStudentDao {//有关Student类的方法（增删改查）//查询类方法有返回值，修改类方法没有List<Student> selectAll();void deleteById(Integer id);void add(Student student);Student selectById(Integer id);void update(Student student);List<Student> selectByPage(int offset, int pageSize);int selectTotalCount();
}

@Overridepublic List<Student> selectByPage(int offset, int pageSize) {Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;//扩大作用域，下面要将list数据放到req中List<Student> list = new ArrayList<>();try {//利用工具类方法获取连接connection = JDBCUtil.getConnection();String sql = "SELECT id,name,age,gender FROM student LIMIT ?,?";preparedStatement = connection.prepareStatement(sql);preparedStatement.setInt(1,offset);preparedStatement.setInt(2,pageSize);System.out.println(preparedStatement);resultSet = preparedStatement.executeQuery();//因为在这里声明的list只能在try的大括号里面使用，下面拿不到，所以需要在try上面声明list，扩大作用域//List<Student> list = new ArrayList<>();while (resultSet.next()) {int id = resultSet.getInt("id");String name = resultSet.getString("name");int age = resultSet.getInt("age");String gender = resultSet.getString("gender");Student student = new Student(id, name, age, gender);list.add(student);}for (Student student : list) {System.out.println(student);}}//ClassNotFoundException在JDBCUtil静态代码块中已被捕获，所以不需要在这里再捕获了catch (SQLException e) {throw new RuntimeException(e);} finally {JDBCUtil.close(connection, preparedStatement, resultSet);}return list;}@Overridepublic int selectTotalCount() {Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;//在这里不再是封装成list集合，封装为一个student对象即可int totalCount = 0;try {connection = JDBCUtil.getConnection();String sql = "SELECT count(*) FROM student";preparedStatement = connection.prepareStatement(sql);System.out.println(preparedStatement);resultSet = preparedStatement.executeQuery();while (resultSet.next()) {//count(*)生成一行一列的数据//获取第一列的数据totalCount = resultSet.getInt(1);}}         //ClassNotFoundException在JDBCUtil静态代码块中已被捕获，所以不需要在这里再捕获了catch (SQLException e) {throw new RuntimeException(e);} finally {JDBCUtil.close(connection, preparedStatement, resultSet);}return totalCount;}

分页代码在v3.bootcss.com网站中的“组件”下找到的

    <%//JSP脚本，这里可以任意写java代码//request为内置对象，jsp页面已经new好了这个对象，名字也起好了// （request）（前面用的是缩写req，这里必须是完整的单词），所以名字不能乱改//前面已经将list数据放入request（req）中，现在需要将数据从request（req）中拿出来PageInfo pageInfo = (PageInfo) request.getAttribute("pageInfo");%>
……
<%--分页--%><nav aria-label="Page navigation"><ul class="pagination"><%//当当前页为第一页时，“上一页”应该不能点击if (pageInfo.getPageNo() == 1){%><%--class = "disabled"表示不可点击状态--%><li class = "disabled"><%--href="#"代表不进行页面跳转--%><a href="#" aria-label="Previous"><span aria-hidden="true">&laquo;</span></a></li><%}else {%><li><%--上一页的pageNo为当前页-1--%><a href="/student?method=selectByPage&pageNo=<%=pageInfo.getPageNo() - 1%>&pageSize=5" aria-label="Previous"><span aria-hidden="true">&laquo;</span></a></li><%}%><%for (int i = 1; i <= pageInfo.getTotalPage(); i++) {//点击页面为当前页if (pageInfo.getPageNo() == i){%><%--再次点击当前页不进行页面跳转--%><li class="active"><a href="#"><%=i%></a></li><%}else {%><li><a href="/student?method=selectByPage&pageNo=<%=i%>&pageSize=5"><%=i%></a></li><%}}%><%//与“上一页”同理if (pageInfo.getPageNo() == pageInfo.getTotalPage()){%><li class="disabled"><a href="#" aria-label="Next"><span aria-hidden="true">&raquo;</span></a></li><%}else {%><li><a href="/student?method=selectByPage&pageNo=<%=pageInfo.getPageNo() + 1%>&pageSize=5" aria-label="Next"><span aria-hidden="true">&raquo;</span></a></li><%}%></ul></nav>