纯JSP MySQL分页，bootstrap美化显示

系统管理员 2023-09-28 20:02 31阅读 0赞

### 什么是分页？ ###

*  从数据库中获取数百万条记录几乎消耗了机器的所有 CPU 功率和内存。
 *  因此，我们将数百万条记录分成小块，显示每页的记录数量有限（比如 20 或 30 条）。最好的例子是谷歌搜索分页，它允许用户按页码导航到下一页并浏览每页的有限记录。

#### **如何实现分页？** ####

分页逻辑可以通过多种方式实现，有些是

**方法一：贪婪方法**

*  一次获取所有记录并在缓存结果后将其显示给用户。这被称为贪婪方法。
 *  这可以通过编写返回 List<Object> 的 DAO 来实现。每当用户需要结果时，可以从缓存列表中检索子列表，而不是在用户单击“下一步”链接时查询数据库以获取下一组结果。
 *  这种方法的缺点是，由于数据被缓存，它变得陈旧。如果您的应用程序更改了结果集中的数据，您在选择此解决方案时可能必须考虑结果的准确性。

**方法2：非贪婪方法**

*  通过限制 ResultSet 中的行数，每次用户想要查看时获取记录范围。
 *  如果您有超过数百万条记录会怎样？用户可能需要等待很长时间才能得到结果。在这里，我们将结果集限制为仅获取用户想要查看的记录数。

<%@ page contentType="text/html; charset=UTF-8" %>
    <%@ page language="java"%>
    <%@ page import="java.sql.*"%>
    <%!public int nullIntconvert(String str) {
            int num = 0;
            if (str == null) {
                str = "0";
            } else if ((str.trim()).equals("null")) {
                str = "0";
            } else if (str.equals("")) {
                str = "0";
            }
            try {
                num = Integer.parseInt(str);
            } catch (Exception e) {
            }
            return num;
        }%>
    <%
        Connection conn = null;
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/jpetstore", "root", "root");
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
    
        int showRows = 2;
        int totalRecords = 5;
        int totalRows = nullIntconvert(request.getParameter("totalRows"));
        int totalPages = nullIntconvert(request.getParameter("totalPages"));
        int iPageNo = nullIntconvert(request.getParameter("iPageNo"));
        int cPageNo = nullIntconvert(request.getParameter("cPageNo"));
    
        int startResult = 0;
        int endResult = 0;
        if (iPageNo == 0) {
            iPageNo = 0;
        } else {
            iPageNo = Math.abs((iPageNo - 1) * showRows);
        }
        String query1 = "SELECT SQL_CALC_FOUND_ROWS * FROM jpetstore.item  LIMIT "
                + iPageNo + "," + showRows + "";
        ps1 = conn.prepareStatement(query1);
        rs1 = ps1.executeQuery();
    
        String query2 = "SELECT FOUND_ROWS() as cnt";
        ps2 = conn.prepareStatement(query2);
        rs2 = ps2.executeQuery();
        if (rs2.next()) {
            totalRows = rs2.getInt("cnt");
        }
    %>
    <html>
        <head>
            <title>JSP分页</title>
            <link href="bootstrap-5.1.3-dist/css/bootstrap.min.css" rel="stylesheet" >
            <script src="bootstrap-5.1.3-dist/js/bootstrap.bundle.min.js"></script>
            <link href="bootstrap-icons-1.8.3/bootstrap-icons.css" rel="stylesheet">
            <style>
                .panel-table .panel-body{
                    padding:0;
                }
    
                .panel-table .panel-body .table-bordered{
                    border-style: none;
                    margin:0;
                }
    
                .panel-table .panel-body .table-bordered > thead > tr > th:first-of-type {
                    text-align:center;
                    width: 100px;
                }
    
                .panel-table .panel-body .table-bordered > thead > tr > th:last-of-type,
                .panel-table .panel-body .table-bordered > tbody > tr > td:last-of-type {
                    border-right: 0px;
                }
    
                .panel-table .panel-body .table-bordered > thead > tr > th:first-of-type,
                .panel-table .panel-body .table-bordered > tbody > tr > td:first-of-type {
                    border-left: 0px;
                }
    
                .panel-table .panel-body .table-bordered > tbody > tr:first-of-type > td{
                    border-bottom: 0px;
                }
    
                .panel-table .panel-body .table-bordered > thead > tr:first-of-type > th{
                    border-top: 0px;
                }
    
                .panel-table .panel-footer .pagination{
                    margin:0;
                }
    
                /*
                used to vertically center elements, may need modification if you're not using default sizes.
                */
                .panel-table .panel-footer .col{
                    line-height: 34px;
                    height: 34px;
                }
    
                .panel-table .panel-heading .col h3{
                    line-height: 30px;
                    height: 30px;
                }
    
                .panel-table .panel-body .table-bordered > tbody > tr > td{
                    line-height: 34px;
                }
    
    
            </style>
    
        </head>
    
        <body>
            <h3>Pagination of JSP page</h3>
            <form>
                <input type="hidden" name="iPageNo" value="<%=iPageNo%>"> <input
                    type="hidden" name="cPageNo" value="<%=cPageNo%>"> <input
                    type="hidden" name="showRows" value="<%=showRows%>">
                <div class="panel-body">
                    <table class="table table-striped table-bordered table-list">
                        <thead>
                            <tr>
                                <td>ItemID</td>
                                <td>ProductID</td>
                                <td>listPrice</td>
                                <td>unitCost</td>
                            </tr>
                        </thead>
    
                        <tbody>
                            <%
                                while (rs1.next()) {
                            %>
                            <tr>
                                <td><%=rs1.getString("itemid")%></td>
                                <td><%=rs1.getString("productId")%></td>
                                <td><%=rs1.getDouble("listprice")%></td>
                                <td><%=rs1.getDouble("unitcost")%></td>
                            </tr>
                            <%
                                }
                            %>
    
                        </tbody>
                    </table>
                </div>
                <div class="panel-footer">
                    <div class="row">
    
    
                        <div class="col col-xs-4">
                            <%
                                try {
                                    if (totalRows < (iPageNo + showRows)) {
                                        endResult = totalRows;
                                    } else {
                                        endResult = (iPageNo + showRows);
                                    }
                                    startResult = (iPageNo + 1);
                                    totalPages = ((int) (Math.ceil((double) totalRows / showRows)));
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                            %>
                            第 <%=startResult%> - <%=endResult%> 行，共 <%=totalRows%> 行
    
                        </div>
                        <div class="col col-xs-8">
                            <nav aria-label="Page navigation example">
                                <ul class="pagination pagination-sm justify-content-end">
                                    <%
                                        int i = 0;
                                        int cPage = 0;
                                        if (totalRows != 0) {
                                            cPage = ((int) (Math.ceil((double) endResult
                                                    / (totalRecords * showRows))));
    
                                            int prePageNo = (cPage * totalRecords)
                                                    - ((totalRecords - 1) + totalRecords);
                                            if ((cPage * totalRecords) - (totalRecords) > 0) {
                                    %>
    
    
                                    <li class="page-item">
                                        <a class="page-link"
                                           href="pagination.jsp?iPageNo=<%=prePageNo%>&cPageNo=<%=prePageNo%>">
                                            < 前一页</a>
                                    </li>
                                    <%
                                        }
                                        for (i = ((cPage * totalRecords) - (totalRecords - 1)); i <= (cPage * totalRecords); i++) {
                                            if (i == ((iPageNo / showRows) + 1)) {
                                    %>
                                    <li class="page-item">
                                        <a class="page-link" href="pagination.jsp?iPageNo=<%=i%>"
                                           style="cursor: pointer; color: red"><b><%=i%></b> </a>
                                    </li>
                                    <%
                                    } else if (i <= totalPages) {
                                    %>
                                    <li class="page-item">
                                        <a class="page-link" href="pagination.jsp?iPageNo=<%=i%>"><%=i%></a>
                                    </li>
                                    <%
                                            }
                                        }
                                        if (totalPages > totalRecords && i < totalPages) {
                                    %>
                                    <li class="page-item">
                                        <a class="page-link" href="pagination.jsp?iPageNo=<%=i%>&cPageNo=<%=i%>">
                                            后一页 ></a>
                                    </li>
                                    <%
                                            }
                                        }
                                    %>
                                </ul>
                            </nav>
                        </div>
    
                    </div>
                </div>
            </div>
        </form>
    </body>
    </html>
    <%
        try {
            if (ps1 != null) {
                ps1.close();
            }
            if (rs1 != null) {
                rs1.close();
            }
    
            if (ps2 != null) {
                ps2.close();
            }
            if (rs2 != null) {
                rs2.close();
            }
    
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    %>

create table if not exists item (
        itemid varchar(10) not null,
        productid varchar(10) not null,
        listprice decimal(10,2) null,
        unitcost decimal(10,2) null,
        supplier int null,
        status varchar(2) null,
        attr1 varchar(80) null,
        attr2 varchar(80) null,
        attr3 varchar(80) null,
        attr4 varchar(80) null,
        attr5 varchar(80) null,
    primary key (itemid) )

INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-1','FI-SW-01',16.50,10.00,1,'P','Large');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-2','FI-SW-01',16.50,10.00,1,'P','Small');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-3','FI-SW-02',18.50,12.00,1,'P','Toothless');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-4','FI-FW-01',18.50,12.00,1,'P','Spotted');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-5','FI-FW-01',18.50,12.00,1,'P','Spotless');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-6','K9-BD-01',18.50,12.00,1,'P','Male Adult');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-7','K9-BD-01',18.50,12.00,1,'P','Female Puppy');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-8','K9-PO-02',18.50,12.00,1,'P','Male Puppy');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-9','K9-DL-01',18.50,12.00,1,'P','Spotless Male Puppy');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-10','K9-DL-01',18.50,12.00,1,'P','Spotted Adult Female');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-11','RP-SN-01',18.50,12.00,1,'P','Venomless');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-12','RP-SN-01',18.50,12.00,1,'P','Rattleless');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-13','RP-LI-02',18.50,12.00,1,'P','Green Adult');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-14','FL-DSH-01',58.50,12.00,1,'P','Tailless');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-15','FL-DSH-01',23.50,12.00,1,'P','With tail');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-16','FL-DLH-02',93.50,12.00,1,'P','Adult Female');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-17','FL-DLH-02',93.50,12.00,1,'P','Adult Male');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-18','AV-CB-01',193.50,92.00,1,'P','Adult Male');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-19','AV-SB-02',15.50, 2.00,1,'P','Adult Male');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-20','FI-FW-02',5.50, 2.00,1,'P','Adult Male');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-21','FI-FW-02',5.29, 1.00,1,'P','Adult Female');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-22','K9-RT-02',135.50, 100.00,1,'P','Adult Male');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-23','K9-RT-02',145.49, 100.00,1,'P','Adult Female');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-24','K9-RT-02',255.50, 92.00,1,'P','Adult Male');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-25','K9-RT-02',325.29, 90.00,1,'P','Adult Female');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-26','K9-CW-01',125.50, 92.00,1,'P','Adult Male');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-27','K9-CW-01',155.29, 90.00,1,'P','Adult Female');
    INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-28','K9-RT-01',155.29, 90.00,1,'P','Adult Female');

![09d6efb16f0649d38bf9782affef31c1.png][]

![34ccecc74e4242e3bcaad74c88acdf3b.png][]

![6af7723c74834f4f80a7a8e1687e91c3.png][]

![744bcf696af5428d9eb340e9f3126ff3.png][]

[09d6efb16f0649d38bf9782affef31c1.png]: https://img-blog.csdnimg.cn/09d6efb16f0649d38bf9782affef31c1.png
[34ccecc74e4242e3bcaad74c88acdf3b.png]: https://img-blog.csdnimg.cn/34ccecc74e4242e3bcaad74c88acdf3b.png
[6af7723c74834f4f80a7a8e1687e91c3.png]: https://img-blog.csdnimg.cn/6af7723c74834f4f80a7a8e1687e91c3.png
[744bcf696af5428d9eb340e9f3126ff3.png]: https://img-blog.csdnimg.cn/744bcf696af5428d9eb340e9f3126ff3.png