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

     

     

     

     

  • 相关阅读:
    仿游戏热血江湖游戏类22（物品方法）
    关于 Eclipse 的一场 “三角关系”
    懒人制作企业期刊的秘籍
    随机森林计算指标重要性—从决策树到随机森林Python实现
    数据库备份与恢复
    C/C++ 运用WMI接口查询系统信息
    分享篇：最近在研究的AIGC内容
    Zookeeper学习笔记（1）—— 基础知识
    Python字符串格式化
    2.1 JAVA基础语法
  • 原文地址：https://blog.csdn.net/allway2/article/details/126316275