Servlet分頁
2019-10-16 23:02:48
在現實應用中，一個資料庫的表中都會儲存有大量的資料記錄，如果一次將所有資料讀取出來，除了浪費伺服器資源外，對於使用者閱也是不太友好的體驗。所以我們使用分頁將大量記錄分成多個部分，允許使用者僅顯示一部分資料記錄。 在一個頁面中載入所有記錄可能需要時間，因此建議建立或使用分頁。在servlet中，我們可以輕鬆地開發分頁範例。
在這個servlet分頁範例中，使用MySQL資料庫來獲取記錄。
在這裡，「testdb」資料庫中建立了「employees」表。「employees」表有以下幾個欄位：id，name，age，education，address和salary。建立表和手動插入記錄或匯入本範例提供的SQL檔案。
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL DEFAULT '',
`age` int(3) unsigned NOT NULL DEFAULT '0',
`education` varchar(32) DEFAULT '' COMMENT '學歷',
`address` varchar(254) DEFAULT NULL,
`salary` float(8,2) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES ('1', '李小春', '23', '其它', '海口市人民大道1800號', '8900.00');
INSERT INTO `employees` VALUES ('2', '張輝', '28', '本科', '廣州天河區珠村市場', '15800.98');
INSERT INTO `employees` VALUES ('3', '林賢弟', '29', '博士', '廣州白雲區龍塘村120號', '18990.99');
INSERT INTO `employees` VALUES ('4', '王小簡', '23', '本科', '海口人民大道1688號', '899.98');
INSERT INTO `employees` VALUES ('5', '蔡世傑', '27', '專科', '上海市寶山區聯楊路2211弄26號', '15800.00');
INSERT INTO `employees` VALUES ('6', '張承龍', '30', '本科', '上海市虹口區虯江路522號', '23000.00');
INSERT INTO `employees` VALUES ('7', '李林奕', '26', '本科', '上海市徐匯區漕寶路440號', '32600.00');
INSERT INTO `employees` VALUES ('8', '劉皓軒', '28', '研究生', '上海松江九亭立同商務廣場', '29000.00');
INSERT INTO `employees` VALUES ('9', '周佳豪', '36', '博士', '深圳市寶安區沙井街道辦107國道富達工業區B棟', '48000.00');
INSERT INTO `employees` VALUES ('10', '陳聰', '23', '本科', '福田區文蔚大廈19層', '9800.00');
開啟Eclipse，建立一個動態Web專案：ServletPagination，其完整的目錄結構如下所示 -
以下是這個專案中的幾個主要的程式碼檔案。
檔案：index.html -
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Servlet分頁範例</title>
</head>
<body>
<div style="text-align: center;">
<a href="ViewServlet">點選檢視分頁效果</a>
</div>
</body>
</html>
檔案：Emp.java -
package com.yiibai.bean;
public class Emp {
private int id;
private String name;
private int age;
private String address;
private String education;
private float salary;
public int getId() {
return id;
}
public String getEducation() {
return education;
}
public void setEducation(String education) {
this.education = education;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public float getSalary() {
return salary;
}
public void setSalary(float salary) {
this.salary = salary;
}
}
檔案：EmpDao.java -
package com.yiibai.dao;
import com.yiibai.bean.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class EmpDao {
public static Connection getConnection() {
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/testdb?useSSL=false&characterEncoding=utf8",
"root", "123456");
} catch (Exception e) {
System.out.println(e);
}
return con;
}
public static List<Emp> getRecords(int page, int pageSize) {
List<Emp> list = new ArrayList<Emp>();
int start = 0;
if (page > 0) {
start = (page - 1) * pageSize;
}
try {
Connection con = getConnection();
String sql = "SELECT * FROM employees ORDER BY id LIMIT " + start + "," + pageSize;
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Emp e = new Emp();
e.setId(rs.getInt(1));
e.setName(rs.getString(2));
e.setSalary(rs.getFloat("salary"));
list.add(e);
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
return list;
}
// 表中的記錄總數
public static int getTotal() {
try {
Connection con = getConnection();
PreparedStatement ps = con.prepareStatement("SELECT count(*) AS total FROM employees ");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
return rs.getInt("total");
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
return 0;
}
// 總頁數
public static int totalPages(int pageSize) {
int total = getTotal();
int totalPages = (total % pageSize == 0) ? (total / pageSize) : (total / pageSize) + 1;
return totalPages;
}
}
檔案：ViewServlet.java -
package com.yiibai;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.yiibai.bean.Emp;
import com.yiibai.dao.EmpDao;
@WebServlet("/ViewServlet")
public class ViewServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
request.setCharacterEncoding("UTF-8");
PrintWriter out = response.getWriter();
// 每頁3條記錄
int pageSize = 3;
String spage = request.getParameter("page");
if (spage == null || spage == "") {
spage = "1";
}
int page = Integer.parseInt(spage);
int totalPage = EmpDao.totalPages(pageSize);
List<Emp> list = EmpDao.getRecords(page, pageSize);
out.print("<h1>當前第: " + spage + "/" + totalPage + " 頁</h1>");
out.print("<table border='1' cellpadding='4' width='60%'>");
out.print("<tr><th>編號</th><th>姓名</th><th>薪水</th>");
for (Emp e : list) {
out.print("<tr><td>" + e.getId() + "</td><td>" + e.getName() + "</td><td>" + e.getSalary() + "</td></tr>");
}
out.print("</table>");
out.print("<a href='ViewServlet?page=1'>首頁</a> ");
for (int i = 1; i <= totalPage; i++) {
out.print("<a href='ViewServlet?page=" + i + "'>" + i + "</a> ");
}
out.print("<a href='ViewServlet?page=" + totalPage + "'>最後一頁</a> ");
out.close();
}
}
注意：此專案使用
@WebServlet("ServletURL")來對映URL，因此不需要組態web.xml。
在編寫上面程式碼後，部署此Web應用程式(在專案名稱上點選右鍵->」Run On Server…」)，開啟瀏覽器存取URL： http://localhost:8080/ServletPagination/ ，如果沒有錯誤，應該會看到以下結果 -
點選連結，進入分頁頁面 -
分別點選第1，2，3，4頁，結果如下所示 -
第1，2，3，4頁所執行的SQL語句分別如下：
SELECT * FROM employees ORDER BY id LIMIT 0,3
SELECT * FROM employees ORDER BY id LIMIT 3,3
SELECT * FROM employees ORDER BY id LIMIT 6,3
SELECT * FROM employees ORDER BY id LIMIT 9,3