分页（十九）

目录

一、分页SQL语句

二、pageNo,pageSize 

三、实例

note：

---

一、分页SQL语句

limit 中offset代表偏移到哪个位置，rows代表往下数多少个

二、pageNo,pageSize 

设置页面数据数量pageSize ---每页多少条数据

设置页面页数pageNo----第几页

offset =  (pageNo-1)*pageSize；  每一页的开头第一个数据

pageSize = 在offset这个数据开始向下数的数据数量

        

pageNo,pageSize   2,5

limit  (pageNo-1)*pageSize ,pageSize

pageNo    limit

1                0

2                5

3               10

三、实例

代码：

@WebServlet("/student")
public class StudentServlet extends HttpServlet {
    @Override
    //service 服务器
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // 解决post请求乱码问题
        req.setCharacterEncoding("UTF-8");


        // 把所有和学生相关的增删改查放到一个Servlet
        // http://localhost:8080/JavaWeb/student?method=selectAll
        // http://localhost:8080/JavaWeb/student?method=deleteById&id=2
        // http://localhost:8080/JavaWeb/student?method=insert

        String method = req.getParameter("method");
        if (method == null || method.equals("")){
            method = "selectPage";
        }
        switch (method){
//            case "selectALL":
//                //查询表的全部数据并显示出来
//                selectALL(req,resp);
//                break;
            case "selectPage":
                selectPage(req,resp);
                break;
            case "deleteById":
                //通过id删除数据库的数据
                deleteById(req,resp);
                break;
            case "insert":
                //增加数据
                insert(req,resp);
                break;
            case "selectById":
                //修改一行数据首先通过id查询出来并显示出来
                //然后通过修改上传后台保存到数据库中
                //最后重定向到查询表
                selectById(req,resp);
                break;
            case "update":
                //将修改的数据提交后台
                update(req,resp);
                break;
            case "getStudentInsertPage":
                getStudentInsertPage(req, resp);
                break;
        }

    }

private void selectPage(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("StudentServlet.selectPage");
        String pageNostr = req.getParameter("PageNo");//网址上的固定为string类型
        if (pageNostr == null || pageNostr.equals("")){
            pageNostr = "1";
        }
        String pageSizestr = req.getParameter("PageSize");
        if (pageSizestr == null || pageSizestr.equals("")){
            pageSizestr = "5";
        }
        int PageNo = Integer.parseInt(pageNostr);
        int PageSize = Integer.parseInt(pageSizestr);
        //select id,name,age from teacher limit offset,PageSIze
        //offset从第几行开始往下数，PageSIze是每一页的行数，
        // 每一页数PageSIze行就是输出一页
        int offset = (PageNo - 1 ) * PageSize;
        //获得所有数据组成的集合
        ArrayList<StudentBanji> list = getCurrentPageList(offset,PageSize);

        //得到所有数据的数量
        int totalCount =getTotalCount();
        //计算医用能分成多少页
        int totalPage = (int)Math.ceil((double) totalCount/PageSize);
        //构建分页对象,参数为数组集合,页号,总页数,每页大小
        Pageinfo pageinfo = new Pageinfo(list,PageNo,totalPage,PageSize);
        //将pageinfo加入到内存中
        req.setAttribute("pageinfo",pageinfo);
        // 跳转到student_list.jsp展示数据
        // Dispatcher:分发  forward：转发
        //转发到student_list.jsp
        req.getRequestDispatcher("/student_list.jsp").forward(req,resp);
    }

    private int getTotalCount() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int totalCount = 0;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "select count(*) from student0";
            statement = connection.prepareStatement(sql);
            System.out.println(statement);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                totalCount = resultSet.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return totalCount;
    }

    private ArrayList<StudentBanji> getCurrentPageList(int offset, int PageSize) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        ArrayList<StudentBanji> list = new ArrayList<>();/*new ArrayList后<>可加可不加*/
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT s.id AS studentId,s.name AS studentName,s.age AS studentAge,s.gender AS studentGender,b.name AS studentBanji\n" +
                    "FROM student0 AS s INNER JOIN banji AS b\n" +
                    "ON s.banji_id=b.id LIMIT ?,?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,offset);
            statement.setInt(2,PageSize);
            resultSet = statement.executeQuery();
            System.out.println(statement);
            while (resultSet.next()) {
                int studentId = resultSet.getInt("studentId");
                String studentName = resultSet.getString("studentName");
                int studentAge = resultSet.getInt("studentAge");
                String studentGender = resultSet.getString("studentGender");
                String studentBanji = resultSet.getString("studentBanji");
                StudentBanji studentbanji = new StudentBanji(studentId, studentName, studentAge, studentGender,studentBanji);
                list.add(studentbanji);
            }
            for (StudentBanji studentbanji : list) {
                System.out.println(studentbanji);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.close(connection, statement, resultSet);
        }
       return list;
    }

---

note：

跳转到一个JSP页面有两种方式：

1、浏览器直接请求这个JSP页面，一般这个页面不需要数据

2、先访问Servlet，然后转发到这个JSP页面，访问Servlet可以可以查询数据，放到req中，转发到JSP页面展示