eclipse + JSP
Download: https://www.eclipse.org/downloads/packages/
Download: https://tomcat.apache.org/
Remark : 如果用 32-bit/64-bit Windows Service Installer 會撞 port
Download: https://dev.mysql.com/downloads/connector/j/
Remark : Download connect mysql-connector-java.jar
Example
employer.sql
CREATE TABLE `employee` ( `ID` int(10) UNSIGNED NOT NULL, `NAME` varchar(100) NOT NULL, `AGE` int(3) DEFAULT NULL, `JOINING_DATE` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- 傾印資料表的資料 `employee` -- INSERT INTO `employee` (`ID`, `NAME`, `AGE`, `JOINING_DATE`) VALUES (3, 'Kim', NULL, NULL), (100, 'David', 45, NULL), (101, 'wgegw', 45, NULL), (102, 'sdvsdv', 45, NULL), (103, 'ewfgwegw', 4444, NULL), (104, 'ewfgwegwewfewfwef', 4444, NULL), (105, 'wefew', 4444, NULL), (106, 'ewfwef', 4444, NULL), (107, 'ehreh', 4444, NULL), (108, 'eheheh', 4444, NULL), (109, 'Kim', NULL, NULL), (110, 'David', 45, NULL), (111, 'wgegw', 45, NULL), (112, 'sdvsdv', 45, NULL), (113, 'ewfgwegw', 4444, NULL), (114, 'ewfgwegwewfewfwef', 4444, NULL), (115, 'wefew', 4444, NULL), (116, 'ewfwef', 4444, NULL), (117, 'ehreh', 4444, NULL), (118, 'eheheh', 4444, NULL); -- -- 已傾印資料表的索引 -- -- -- 資料表索引 `employee` -- ALTER TABLE `employee` ADD PRIMARY KEY (`ID`); -- -- 在傾印的資料表使用自動遞增(AUTO_INCREMENT) -- -- -- 使用資料表自動遞增(AUTO_INCREMENT) `employee` -- ALTER TABLE `employee` MODIFY `ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=119; COMMIT;
Database.java
package classes; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class Database { private Connection con; PreparedStatement psPagination = null; ResultSet rs = null; public Database() { try { con = null; Class.forName("com.mysql.jdbc.Driver").newInstance(); con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=UTC&characterEncoding=utf-8", "root", ""); System.out.println("Successfully connected to " + "MySQL server using TCP/IP..."); } catch (Exception e) { System.out.println("Error in database connection:" + e.getMessage()); } } public Connection getConnection() { return con; } public ResultSet executeQuery(String sql) throws SQLException { psPagination = con.prepareStatement(sql); rs = psPagination.executeQuery(); return rs; } public void closeConnection() { try { if (con != null) { con.close(); } } catch (Exception e) { System.out.println("Error in close connection" + e.getMessage()); } } } [/sql] <strong>Pagination.java</strong> [java] package classes; public class Pagination { public int nullIntconv(String str) { int conv = 0; if (str == null) { str = "0"; } else if ((str.trim()).equals("null")) { str = "0"; } else if (str.equals("")) { str = "0"; } try { conv = Integer.parseInt(str); } catch (Exception e) { } return conv; } public String getPagination(int iTotalRows, int iPageNo, int iShowRows, int iTotalPages) { String str = ""; int iStartResultNo = 0; int iEndResultNo = 0; int iTotalSearchRecords = 10; // Number of pages index shown try { if (iTotalRows < (iPageNo + iShowRows)) { iEndResultNo = iTotalRows; } else { iEndResultNo = (iPageNo + iShowRows); } iStartResultNo = (iPageNo + 1); iTotalPages = ((int) (Math.ceil((double) iTotalRows / iShowRows))); } catch (Exception e) { e.printStackTrace(); } int i = 0; int cPage = 0; if (iTotalRows != 0) { cPage = ((int) (Math.ceil((double) iEndResultNo / (iTotalSearchRecords * iShowRows)))); int prePageNo = (cPage * iTotalSearchRecords) - ((iTotalSearchRecords - 1) + iTotalSearchRecords); if ((cPage * iTotalSearchRecords) - (iTotalSearchRecords) > 0) { str += "<a href=\"index.jsp?iPageNo=" + prePageNo + "\"><< Previous</a>"; } for (i = ((cPage * iTotalSearchRecords) - (iTotalSearchRecords - 1)); i <= (cPage * iTotalSearchRecords); i++) { if (i == ((iPageNo / iShowRows) + 1)) { str += "<a href=\"index.jsp?iPageNo=" + i + "\" style=\"cursor: pointer; color: red\"><b>" + i + "</b></a>"; } else if (i <= iTotalPages) { str += "<a href=\"index.jsp?iPageNo=" + i + " \">" + i + "</a>"; } } if (iTotalPages > iTotalSearchRecords && i < iTotalPages) { str += "<a href=\"index.jsp?iPageNo=" + i + "\"> >> Next</a>"; } } str += "<b>Total Result" + iTotalRows + "</b>"; return str; } }
index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="BIG5"%> <%@ page import="java.sql.*"%> <%@ page import="java.io.*"%> <%@ page import="java.sql.PreparedStatement"%> <%@ page import="java.sql.ResultSet"%> <%@ page import="java.sql.Connection"%> <%@ page import="java.sql.DriverManager"%> <%@ page import="classes.Database"%> <%@ page import="classes.Pagination"%> <% Database db = new Database(); Connection conn = db.getConnection(); Pagination pagination = new Pagination(); int iShowRows = 1; int iTotalRows = pagination.nullIntconv(request.getParameter("iTotalRows")); int iTotalPages = pagination.nullIntconv(request.getParameter("iTotalPages")); int iPageNo = pagination.nullIntconv(request.getParameter("iPageNo")); if (iPageNo == 0) { iPageNo = 0; } else { iPageNo = Math.abs((iPageNo - 1) * iShowRows); } ResultSet rs = db.executeQuery("SELECT * FROM employee limit " + iPageNo + "," + iShowRows + ""); ResultSet rsRowCnt = db.executeQuery("SELECT count(*) as cnt FROM employee"); if (rsRowCnt.next()) { iTotalRows = rsRowCnt.getInt("cnt"); } %> <html> <head> <title>Pagination of JSP page</title> </head> <body> <form name="frm"> <input type="hidden" name="iPageNo" value="<%=iPageNo%>"> <input type="hidden" name="iShowRows" value="<%=iShowRows%>"> <table width="100%" cellpadding="0" cellspacing="0" border="0"> <tr> <td>Name</td> <td>Batch</td> <td>Address</td> </tr> <% while (rs.next()) { %> <tr> <td><%=rs.getString("NAME")%></td> <td><%=rs.getString("AGE")%></td> <td><%=rs.getString("JOINING_DATE")%></td> </tr> <% } %> </table> <div> <% out.println(pagination.getPagination(iTotalRows, iPageNo, iShowRows, iTotalPages)); %> </div> </form> </body> </html>
index2.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="BIG5"%> <%@ page import="java.sql.*"%> <%@ page import="java.io.*"%> <%@ page import="classes.Database"%> <%@ page import="java.sql.PreparedStatement"%> <%@ page import="java.sql.ResultSet"%> <%@ page import="java.sql.Connection"%> <%@ page import="java.sql.DriverManager"%> <%!public int nullIntconv(String str) { int conv = 0; if (str == null) { str = "0"; } else if ((str.trim()).equals("null")) { str = "0"; } else if (str.equals("")) { str = "0"; } try { conv = Integer.parseInt(str); } catch (Exception e) { } return conv; }%> <% Connection conn = null; Class.forName("com.mysql.jdbc.Driver").newInstance(); conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=UTC&characterEncoding=utf-8", "root", ""); ResultSet rsPagination = null; ResultSet rsRowCnt = null; PreparedStatement psPagination = null; PreparedStatement psRowCnt = null; int iShowRows = 1; // Number of records show on per page int iTotalSearchRecords = 10; // Number of pages index shown int iTotalRows = nullIntconv(request.getParameter("iTotalRows")); int iTotalPages = nullIntconv(request.getParameter("iTotalPages")); int iPageNo = nullIntconv(request.getParameter("iPageNo")); // int cPageNo = nullIntconv(request.getParameter("cPageNo")); int iStartResultNo = 0; int iEndResultNo = 0; if (iPageNo == 0) { iPageNo = 0; } else { iPageNo = Math.abs((iPageNo - 1) * iShowRows); } String sqlPagination = "SELECT SQL_CALC_FOUND_ROWS * FROM employee limit " + iPageNo + "," + iShowRows + ""; psPagination = conn.prepareStatement(sqlPagination); rsPagination = psPagination.executeQuery(); //// this will count total number of rows String sqlRowCnt = "SELECT FOUND_ROWS() as cnt"; psRowCnt = conn.prepareStatement(sqlRowCnt); rsRowCnt = psRowCnt.executeQuery(); if (rsRowCnt.next()) { iTotalRows = rsRowCnt.getInt("cnt"); } %> <html> <head> <title>Pagination of JSP page</title> </head> <body> <form name="frm"> <input type="hidden" name="iPageNo" value="<%=iPageNo%>"> <input type="hidden" name="iShowRows" value="<%=iShowRows%>"> <table width="100%" cellpadding="0" cellspacing="0" border="0"> <tr> <td>Name</td> <td>Batch</td> <td>Address</td> </tr> <% while (rsPagination.next()) { %> <tr> <td><%=rsPagination.getString("NAME")%></td> <td><%=rsPagination.getString("AGE")%></td> <td><%=rsPagination.getString("JOINING_DATE")%></td> </tr> <% } %> <% //// calculate next record start record and end record try { if (iTotalRows < (iPageNo + iShowRows)) { iEndResultNo = iTotalRows; } else { iEndResultNo = (iPageNo + iShowRows); } iStartResultNo = (iPageNo + 1); iTotalPages = ((int) (Math.ceil((double) iTotalRows / iShowRows))); } catch (Exception e) { e.printStackTrace(); } %> <tr> <td colspan="3"> <div> <% int i = 0; int cPage = 0; if (iTotalRows != 0) { cPage = ((int) (Math.ceil((double) iEndResultNo / (iTotalSearchRecords * iShowRows)))); int prePageNo = (cPage * iTotalSearchRecords) - ((iTotalSearchRecords - 1) + iTotalSearchRecords); if ((cPage * iTotalSearchRecords) - (iTotalSearchRecords) > 0) { out.println("<a href=\"index.jsp?iPageNo=" + prePageNo + "\"><< Previous</a>"); } for (i = ((cPage * iTotalSearchRecords) - (iTotalSearchRecords - 1)); i <= (cPage * iTotalSearchRecords); i++) { if (i == ((iPageNo / iShowRows) + 1)) { out.println("<a href=\"index.jsp?iPageNo=" + i + "\" style=\"cursor: pointer; color: red\"><b>" + i + "</b></a>"); } else if (i <= iTotalPages) { out.println("<a href=\"index.jsp?iPageNo=" + i + " \">" + i + "</a>"); } } if (iTotalPages > iTotalSearchRecords && i < iTotalPages) { out.println("<a href=\"index.jsp?iPageNo=" + i + "\"> >> Next</a>"); } } out.println("<b>Total Result" + iTotalRows +"</b>"); %> </div> </td> </tr> </table> </form> </body> </html> <% try { if (psPagination != null) { psPagination.close(); } if (rsPagination != null) { rsPagination.close(); } if (psRowCnt != null) { psRowCnt.close(); } if (rsRowCnt != null) { rsRowCnt.close(); } if (conn != null) { conn.close(); } } catch (Exception e) { e.printStackTrace(); } %>
Download : source
Output :