eclipse + JSP

jsp_01.png
Download: https://www.eclipse.org/downloads/packages/

jsp_02.png
Download: https://tomcat.apache.org/
Remark : 如果用 32-bit/64-bit Windows Service Installer 會撞 port

jsp_03.png
Download: https://dev.mysql.com/downloads/connector/j/
Remark : Download connect mysql-connector-java.jar

Example
jsp_04.png

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 :
jsp_05.png

發表迴響