• 纯JSP MySQL分页

    1. <%@ page contentType="text/html; charset=UTF-8" %>
    2. <%@ page language="java"%>
    3. <%@ page import="java.sql.*"%>
    4. <%!public int nullIntconvert(String str) {
    5.         int num = 0;
    6.         if (str == null) {
    7.             str = "0";
    8.         } else if ((str.trim()).equals("null")) {
    9.             str = "0";
    10.         } else if (str.equals("")) {
    11.             str = "0";
    12.         }
    13.         try {
    14.             num = Integer.parseInt(str);
    15.         } catch (Exception e) {
    16.         }
    17.         return num;
    18.     }%>
    19. <%
    20.     Connection conn = null;
    21.     Class.forName("com.mysql.jdbc.Driver").newInstance();
    22.     conn = DriverManager.getConnection(
    23.             "jdbc:mysql://localhost:3306/jpetstore", "root", "root");
    24.     ResultSet rs1 = null;
    25.     ResultSet rs2 = null;
    26.     PreparedStatement ps1 = null;
    27.     PreparedStatement ps2 = null;
    28.  
    29.     int showRows = 2;
    30.     int totalRecords = 5;
    31.     int totalRows = nullIntconvert(request.getParameter("totalRows"));
    32.     int totalPages = nullIntconvert(request.getParameter("totalPages"));
    33.     int iPageNo = nullIntconvert(request.getParameter("iPageNo"));
    34.     int cPageNo = nullIntconvert(request.getParameter("cPageNo"));
    35.  
    36.     int startResult = 0;
    37.     int endResult = 0;
    38.     if (iPageNo == 0) {
    39.         iPageNo = 0;
    40.     } else {
    41.         iPageNo = Math.abs((iPageNo - 1) * showRows);
    42.     }
    43.     String query1 = "SELECT SQL_CALC_FOUND_ROWS * FROM jpetstore.item  LIMIT "
    44.             + iPageNo + "," + showRows + "";
    45.     ps1 = conn.prepareStatement(query1);
    46.     rs1 = ps1.executeQuery();
    47.  
    48.     String query2 = "SELECT FOUND_ROWS() as cnt";
    49.     ps2 = conn.prepareStatement(query2);
    50.     rs2 = ps2.executeQuery();
    51.     if (rs2.next()) {
    52.         totalRows = rs2.getInt("cnt");
    53.     }
    54. %>
    55. <html>
    56.     <h3>Pagination of JSP pageh3>
    57.     <body>
    58.         <form>
    59.             <input type="hidden" name="iPageNo" value="<%=iPageNo%>"> <input
    60.                 type="hidden" name="cPageNo" value="<%=cPageNo%>"> <input
    61.                 type="hidden" name="showRows" value="<%=showRows%>">
    62.             <table width="100%" cellpadding="0" cellspacing="0"  border="1">
    63.                 <tr>
    64.                     <td>ItemIDtd>
    65.                     <td>ProductIDtd>
    66.                     <td>listPricetd>
    67.                     <td>unitCosttd>
    68.                 tr>
    69.                 <%
    70.                     while (rs1.next()) {
    71.                 %>
    72.                 <tr>
    73.                     <td><%=rs1.getString("itemid")%>td>
    74.                     <td><%=rs1.getString("productId")%>td>
    75.                     <td><%=rs1.getDouble("listprice")%>td>
    76.                     <td><%=rs1.getDouble("unitcost")%>td>
    77.                 tr>
    78.                 <%
    79.                     }
    80.                 %>
    81.  
    82.  
    83.             table>
    84.             <%
    85.                 try {
    86.                     if (totalRows < (iPageNo + showRows)) {
    87.                         endResult = totalRows;
    88.                     } else {
    89.                         endResult = (iPageNo + showRows);
    90.                     }
    91.                     startResult = (iPageNo + 1);
    92.                     totalPages = ((int) (Math.ceil((double) totalRows / showRows)));
    93.                 } catch (Exception e) {
    94.                     e.printStackTrace();
    95.                 }
    96.             %>
    97.  
    98.             <div>
    99.                 <%
    100.                     int i = 0;
    101.                     int cPage = 0;
    102.                     if (totalRows != 0) {
    103.                         cPage = ((int) (Math.ceil((double) endResult
    104.                                 / (totalRecords * showRows))));
    105.  
    106.                         int prePageNo = (cPage * totalRecords)
    107.                                 - ((totalRecords - 1) + totalRecords);
    108.                         if ((cPage * totalRecords) - (totalRecords) > 0) {
    109.                 %>
    110.                 <a
    111.                     href="pagination.jsp?iPageNo=<%=prePageNo%>&cPageNo=<%=prePageNo%>">
    112.                     < Previousa>
    113.                     <%
    114.                         }
    115.                         for (i = ((cPage * totalRecords) - (totalRecords - 1)); i <= (cPage * totalRecords); i++) {
    116.                             if (i == ((iPageNo / showRows) + 1)) {
    117.                     %>
    118.                 <a href="pagination.jsp?iPageNo=<%=i%>"
    119.                    style="cursor: pointer; color: red"><b><%=i%>b> a>
    120.                         <%
    121.                         } else if (i <= totalPages) {
    122.                         %>
    123.                 <a href="pagination.jsp?iPageNo=<%=i%>"><%=i%>a>
    124.                 <%
    125.                         }
    126.                     }
    127.                     if (totalPages > totalRecords && i < totalPages) {
    128.                 %>
    129.                 <a href="pagination.jsp?iPageNo=<%=i%>&cPageNo=<%=i%>">
    130.                     Next >a>
    131.                     <%
    132.                             }
    133.                         }
    134.                     %>
    135.                 <b>Rows <%=startResult%> - <%=endResult%> Total Rows <%=totalRows%>
    136.                 b>
    137.             div>
    138.  
    139.         form>
    140.     body>
    141. html>
    142. <%
    143.     try {
    144.         if (ps1 != null) {
    145.             ps1.close();
    146.         }
    147.         if (rs1 != null) {
    148.             rs1.close();
    149.         }
    150.  
    151.         if (ps2 != null) {
    152.             ps2.close();
    153.         }
    154.         if (rs2 != null) {
    155.             rs2.close();
    156.         }
    157.  
    158.         if (conn != null) {
    159.             conn.close();
    160.         }
    161.     } catch (Exception e) {
    162.         e.printStackTrace();
    163.     }
    164. %>
    165.  
    1. create table if not exists item (
    2.     itemid varchar(10) not null,
    3.     productid varchar(10) not null,
    4.     listprice decimal(10,2) null,
    5.     unitcost decimal(10,2) null,
    6.     supplier int null,
    7.     status varchar(2) null,
    8.     attr1 varchar(80) null,
    9.     attr2 varchar(80) null,
    10.     attr3 varchar(80) null,
    11.     attr4 varchar(80) null,
    12.     attr5 varchar(80) null,
    13. primary key (itemid) )
    1. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-1','FI-SW-01',16.50,10.00,1,'P','Large');
    2. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-2','FI-SW-01',16.50,10.00,1,'P','Small');
    3. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-3','FI-SW-02',18.50,12.00,1,'P','Toothless');
    4. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-4','FI-FW-01',18.50,12.00,1,'P','Spotted');
    5. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-5','FI-FW-01',18.50,12.00,1,'P','Spotless');
    6. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-6','K9-BD-01',18.50,12.00,1,'P','Male Adult');
    7. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-7','K9-BD-01',18.50,12.00,1,'P','Female Puppy');
    8. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-8','K9-PO-02',18.50,12.00,1,'P','Male Puppy');
    9. 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');
    10. 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');
    11. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-11','RP-SN-01',18.50,12.00,1,'P','Venomless');
    12. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-12','RP-SN-01',18.50,12.00,1,'P','Rattleless');
    13. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-13','RP-LI-02',18.50,12.00,1,'P','Green Adult');
    14. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-14','FL-DSH-01',58.50,12.00,1,'P','Tailless');
    15. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-15','FL-DSH-01',23.50,12.00,1,'P','With tail');
    16. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-16','FL-DLH-02',93.50,12.00,1,'P','Adult Female');
    17. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-17','FL-DLH-02',93.50,12.00,1,'P','Adult Male');
    18. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-18','AV-CB-01',193.50,92.00,1,'P','Adult Male');
    19. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-19','AV-SB-02',15.50, 2.00,1,'P','Adult Male');
    20. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-20','FI-FW-02',5.50, 2.00,1,'P','Adult Male');
    21. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-21','FI-FW-02',5.29, 1.00,1,'P','Adult Female');
    22. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-22','K9-RT-02',135.50, 100.00,1,'P','Adult Male');
    23. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-23','K9-RT-02',145.49, 100.00,1,'P','Adult Female');
    24. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-24','K9-RT-02',255.50, 92.00,1,'P','Adult Male');
    25. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-25','K9-RT-02',325.29, 90.00,1,'P','Adult Female');
    26. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-26','K9-CW-01',125.50, 92.00,1,'P','Adult Male');
    27. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-27','K9-CW-01',155.29, 90.00,1,'P','Adult Female');
    28. INSERT INTO item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-28','K9-RT-01',155.29, 90.00,1,'P','Adult Female');

     

     

     

     

  • 相关阅读:
    【论文精读0】MVSNet系列论文详解-GC-Net
    【论文阅读】Bag of Tricks for Efficient Text Classification高效率文本分类技巧包
    B. Boboniu Plays Chess
    不同路径数（冬季每日一题 4）
    Linux 之 journalctl 查看系统与 kernel 日志
    安全操作（安卓推流）程序
    用PHP组合数组，生成笛卡尔积。写几个例子
    jmeter录制https脚本
    MySQL数据库——SQL语言
    mmdetection 用yolox训练自己的coco数据集
  • 原文地址：https://blog.csdn.net/allway2/article/details/126316275