数据库信息的web网页化显示——分页

一、Java代码

package com.study.web.servlet;

import com.study.web.entity.Book;
import com.study.web.entity.Student;
import com.study.web.util.JDBCUtil;
import com.study.web.util.PageInfo;
import com.study.web.vo.StudentBanji;
import com.sun.jmx.snmp.SnmpNull;
import com.sun.xml.internal.ws.addressing.WsaTubeHelper;

import javax.print.attribute.standard.Finishings;
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.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

@WebServlet("/book")
public class BookServlet extends HttpServlet {
    // 访问Servlet默认访问service方法
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("BookServlet.service");
        // 解决post请求乱码问题
        req.setCharacterEncoding("UTF-8");

        // 把所有和书籍相关的增删改查放到一个Servlet
        //http://localhost:8080/JavaWeb/book?menthod=select
        //http://localhost:8080/JavaWeb/book?menthod=deleteById&id=2
        //http://localhost:8080/JavaWeb/book?menthod=insert
        String method = req.getParameter("method");
        if (method == null || method == "") {
            method = "selectByPage";
        }

        switch (method) {
            case "selectByPage":
                selectByPage(req,resp);
                break;
        }
    }

    private void selectByPage(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("BookServlet.selectByPage");
        // 得到分页的页数
        String pageNoStr = req.getParameter("pageNo");
        if (pageNoStr == null || pageNoStr.equals("")) {
            pageNoStr = "1";
        }
        // 得到每一页存储的数据记录数
        String pageSizeStr = req.getParameter("pageSize");
        if (pageSizeStr == null || pageSizeStr.equals("")) {
            pageSizeStr = "3";
        }
        // 得到页数和每页数据数的整形
        int pageNo = Integer.parseInt(pageNoStr);
        int pageSize = Integer.parseInt(pageSizeStr);
        
        // 得到数据库中总的信息个数
        int totalCount = getTotalCount();
        // 计算得到应有多少页
        int totalPage = (int) Math.ceil((double) totalCount / pageSize);
        // 计算得到每页的偏移是多少
        int offset = (pageNo - 1) * pageSize;

        //ArrayList<Book> list = getCurrentPage(offset,pageSize);

        // 得到每页要显示的数据信息
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        ArrayList<Book> list = new ArrayList<>();
        try {
            connection = JDBCUtil.getConnection();
            String sql = "select id,name,publish,price from book limit ?,?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,offset);
            statement.setInt(2,pageSize);
            System.out.println(statement);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String publish = resultSet.getString("publish");
                double price = resultSet.getDouble("price");
                Book book = new Book(id,name,publish,price);
                list.add(book);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.close(connection,statement,resultSet);
        }

        PageInfo pageInfo = new PageInfo(list, pageNo,totalPage,pageSize);
        req.setAttribute("pageInfo",pageInfo);
        req.getRequestDispatcher("book_list.jsp").forward(req,resp);
    }

//    private ArrayList<Book> getCurrentPage(int offset, int pageSize) {
//        // 得到每页要显示的数据信息
//        Connection connection = null;
//        PreparedStatement statement = null;
//        ResultSet resultSet = null;
//        ArrayList<Book> list = new ArrayList<>();
//        try {
//            connection = JDBCUtil.getConnection();
//            String sql = "select id,name,publish,price from book limit ?,?";
//            statement = connection.prepareStatement(sql);
//            statement.setInt(1,offset);
//            statement.setInt(2,pageSize);
//            System.out.println(statement);
//            resultSet = statement.executeQuery();
//            while (resultSet.next()) {
//                int id = resultSet.getInt("id");
//                String name = resultSet.getString("name");
//                String publish = resultSet.getString("publish");
//                double price = resultSet.getDouble("price");
//                Book book = new Book(id,name,publish,price);
//                list.add(book);
//            }
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        } finally {
//            JDBCUtil.close(connection,statement,resultSet);
//        }
//
//        return list;
//    }

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

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.close(connection,statement,resultSet);
        }

        return count;
    }

二、封装页面展示的分页的所有信息

package com.study.web.util;

import java.util.ArrayList;

/**
 * 封装了页面展示分页需要的所有信息
 */
public class PageInfo<T> {
    // 尖括号中写T相当于Object，表示泛型，所有类型都可以使用
    // private ArrayList<Student> list;
    // 当前页数据集合  T：先理解成Object，任何类型都可以放
    private ArrayList<T> list;
    // 当前是第几页
    private Integer pageNo;
    // 一共有多少页
    private Integer totalPage;
    // 每页多少数据
    private Integer pageSize;

    public PageInfo() {
    }

    public PageInfo(ArrayList<T> list, Integer pageNo, Integer totalPage, Integer pageSize) {
        this.list = list;
        this.pageNo = pageNo;
        this.totalPage = totalPage;
        this.pageSize = pageSize;
    }

    public ArrayList<T> getList() {
        return list;
    }

    public void setList(ArrayList<T> list) {
        this.list = list;
    }

    public Integer getPageNo() {
        return pageNo;
    }

    public void setPageNo(Integer pageNo) {
        this.pageNo = pageNo;
    }

    public Integer getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(Integer totalPage) {
        this.totalPage = totalPage;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    @Override
    public String toString() {
        return "PageInfo{" +
                "list=" + list +
                ", pageNo=" + pageNo +
                ", totalPage=" + totalPage +
                ", pageSize=" + pageSize +
                '}';
    }
}

三、html代码

• 使用了bootstrap中的有关表格、分页等的界面优化。

<%@ page import="java.util.ArrayList" %>
<%@ page import="com.study.web.entity.Book" %>
<%@ page import="com.study.web.util.PageInfo" %><%--
  Created by IntelliJ IDEA.
  User: Administrator
  Date: 2021/8/10
  Time: 19:17
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
    <link rel="stylesheet" type="text/css" href="<%=request.getContextPath()%>/static/bootstrap-3.4.1-dist/css/bootstrap.css" />

</head>
<body>
<%--    ${list}--%>
    <a class="btn btn-primary" href="<%=request.getContextPath()%>/book_insert.jsp">添加</a>
    <table class="table table-bordered table-striped table-hover table-condensed">
        <tr>
            <td>ID</td>
            <td>书名</td>
            <td>出版社</td>
            <td>价格</td>
            <td>删除/修改</td>
        </tr>
        <%
            //ArrayList<Book> list = (ArrayList<Book>) request.getAttribute("list");
            PageInfo pageInfo = (PageInfo) request.getAttribute("pageInfo");
            ArrayList<Book> list = pageInfo.getList();
            System.out.println(list);
            for (Book book : list) {
        %>
        <tr>
            <td><%=book.getId()%></td>
            <td><%=book.getName()%></td>
            <td><%=book.getPublish()%></td>
            <td><%=book.getPrice()%></td>
            <td>
                <a class="btn btn-danger btn-sm" href="javascript:void(0)" onclick="deleteById(<%=book.getId()%>)">删除</a>
                <a class="btn btn-warning btn-sm" href="<%=request.getContextPath()%>/book?method=selectById&id=<%=book.getId()%>">编辑</a>
            </td>
        </tr>
        <%
            }
        %>
    </table>

    <nav aria-label="Page navigation">
        <ul class="pagination">
            <%--设置往左换页--%>
            <%
                if (pageInfo.getPageNo() > 1) {
            %>
                    <li>
                        <a href="<%=request.getContextPath()%>/book?method=selectByPage&pageNo=<%=pageInfo.getPageNo()-1%>" aria-label="Previous">
                            <span aria-hidden="true">&laquo;</span>
                        </a>
                    </li>
            <%
                } else {
            %>
                    <li class="disabled">
                        <a aria-label="Previous">
                            <span aria-hidden="true">&laquo;</span>
                        </a>
                    </li>
            <%
                }
            %>
            <%--设置每一页--%>
            <%
                for (int i = 1; i <= pageInfo.getTotalPage(); i++) {
            %>
                    <li>
                        <a href="<%=request.getContextPath()%>/book?method=selectByPage&pageNo=<%=i%>"><%=i%></a>
                    </li>
            <%
                }
            %>
            <%--设置往右换页--%>
            <%
                if (pageInfo.getPageNo() < pageInfo.getTotalPage()) {
            %>
                    <li>
                        <a href="<%=request.getContextPath()%>/book?method=selectByPage&pageNo=<%=pageInfo.getPageNo()+1%>" aria-label="Next">
                            <span aria-hidden="true">&raquo;</span>
                        </a>
                    </li>
            <%
                } else {
            %>
                    <li class="disabled">
                        <a aria-label="Next">
                            <span aria-hidden="true">&raquo;</span>
                        </a>
                    </li>
            <%
                }
            %>
        </ul>
    </nav>

    <script>
        function deleteById(id) {
            var isDelete = confirm("请确认您是否要删除？");
            if (isDelete) {
                location.href = "/JavaWeb/book?method=deleteById&id=" + id;
            }
        }
    </script>
</body>
</html>