在Web開(kāi)發(fā)之中，經(jīng)常需要使用MySQL數(shù)據(jù)庫(kù)進(jìn)行數(shù)據(jù)的存儲(chǔ)和查詢，而JSP作為一種Java語(yǔ)言的服務(wù)器端網(wǎng)頁(yè)技術(shù)，也是經(jīng)常被用作Web開(kāi)發(fā)之中。在JSP網(wǎng)頁(yè)之中，經(jīng)常會(huì)出現(xiàn)需要進(jìn)行分頁(yè)顯示數(shù)據(jù)的需求，而這時(shí)就需要通過(guò)MySQL數(shù)據(jù)庫(kù)進(jìn)行分頁(yè)查詢。以下是一個(gè)MySQL JSP 分頁(yè)查詢的實(shí)例。

<%
int currentPage = request.getParameter("currentPage") == null ? 1 : Integer.parseInt(request.getParameter("currentPage")); //獲取當(dāng)前頁(yè)碼，默認(rèn)為1
int pageSize = 10; //每頁(yè)顯示10條數(shù)據(jù)
//連接MySQL數(shù)據(jù)庫(kù)
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
//執(zhí)行SQL語(yǔ)句，獲取總記錄數(shù)
String sql1 = "SELECT COUNT(*) as total FROM mytable";
Statement stmt1 = conn.createStatement();
ResultSet rs1 = stmt1.executeQuery(sql1);
rs1.next();
int total = rs1.getInt("total");
//計(jì)算總頁(yè)數(shù)
int totalPages = total % pageSize == 0 ? total / pageSize : total / pageSize + 1;
//執(zhí)行SQL語(yǔ)句，獲取當(dāng)前頁(yè)數(shù)據(jù)
String sql2 = "SELECT * FROM mytable LIMIT " + (currentPage - 1) * pageSize + "," + pageSize;
Statement stmt2 = conn.createStatement();
ResultSet rs2 = stmt2.executeQuery(sql2);
%><% while (rs2.next()) { %><% } %>
ID
Name
Age
<%= rs2.getInt("id") %>
<%= rs2.getString("name") %>
<%= rs2.getInt("age") %>
<%
//輸出分頁(yè)導(dǎo)航條
out.print("
");
if (currentPage == 1) {
out.print("首頁(yè) 上一頁(yè) ");
} else {
out.print("首頁(yè)");
out.print("上一頁(yè)");
}
if (currentPage == totalPages) {
out.print("下一頁(yè) 尾頁(yè)");
} else {
out.print("下一頁(yè)");
out.print("尾頁(yè)");
}
out.print("當(dāng)前第" + currentPage + "頁(yè)，共" + totalPages + "頁(yè)");
out.print("
");
%><%
//關(guān)閉數(shù)據(jù)庫(kù)連接
rs1.close();
rs2.close();
stmt1.close();
stmt2.close();
conn.close();
%>

上面的代碼首先通過(guò)request.getParameter()方法獲取當(dāng)前頁(yè)碼，如果沒(méi)有指定則默認(rèn)為1。然后通過(guò)JDBC API連接MySQL數(shù)據(jù)庫(kù)，并執(zhí)行兩條SQL語(yǔ)句獲取總記錄數(shù)和當(dāng)前頁(yè)數(shù)據(jù)。接著計(jì)算總頁(yè)數(shù)，并使用while循環(huán)遍歷當(dāng)前頁(yè)數(shù)據(jù)，使用HTML代碼將數(shù)據(jù)顯示在網(wǎng)頁(yè)之中。最后輸出分頁(yè)導(dǎo)航條，包括首頁(yè)、上一頁(yè)、下一頁(yè)和尾頁(yè)等鏈接，同時(shí)也顯示當(dāng)前頁(yè)碼和總頁(yè)數(shù)。最后關(guān)閉數(shù)據(jù)庫(kù)連接。