• 纯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');

     

     

     

     

  • 相关阅读:
    电商大促就靠RPA，摆脱重复劳动，急速提效
    python自动化测试面试题（三）（持续更新）
    JAVA常见基础面试问题汇集
    开发工程师必备————【Day09】数据库基础知识与基本SQL语句
    STM32单片机IC卡指纹密码锁门禁系统GSM短信报警
    实现放大镜的效果
    GPFS 文件系统部署步骤
    ❤mac使用Idea工具
    详解C++三大特性之继承
    vue 项目的屏幕自适应方案
  • 原文地址：https://blog.csdn.net/allway2/article/details/126316275