分页功能设计

初始记录行的偏移量是 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;
    }

    @Override
    public 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=10
    private 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);
}

    @Override
    public 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();
}

@Override
    public 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;
    }

    @Override
    public 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>