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

     

     

     

     

  • 相关阅读:
    java毕业设计高校智慧校园学生系统mybatis+源码+调试部署+系统+数据库+lw
    【Java面试】离谱！面试官竟想用分布式和微服务干掉大批面试者，结果遇到面试八股文大师后......
    向中央超算平台进化 哪吒汽车发布技术品牌“浩智”
    世界上只有两种黑客：俄罗斯黑客和普通黑客
    Mysql行级锁
    城市排水管网监测方案（新型排水管网智能监测技术）
    Python常见报错及解决方案，建议收藏
    MongoDB数组查询
    网络通信（套接字通信）（C/C++）
    【2023年11月第四版教材】专题3 - 10大管理49过程输入、输出、工具和技术总结
  • 原文地址：https://blog.csdn.net/allway2/article/details/126316275