Servlet简单分页

我不是女神ヾ 2022-05-22 13:36 208阅读 0赞

本文是参照易百教程上的[例子][Link 1]，觉得很有收藏价值，于是特记录一份

数据库示例数据：

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;
    
    SELECT * FROM employees;
    -- ----------------------------
    -- 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');

paging.html：

<!DOCTYPE html>
    <html>
    <head>
    <meta charset="UTF-8">
    <title>Servlet分页示例</title>
    </head>
    <body>
        <div style="text-align: center;">
            <a href="PagingServlet">点击查看分页效果</a>
        </div>
    </body>
    </html>

Employees.java：

public class Employees {
    	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：

import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    import com.hthk.login.bean.Employees;
    
    public class EmpDao {
    	public static Connection getConnection() {
    		Connection con = null;
    		try {
    			Class.forName("com.mysql.jdbc.Driver");
    			con = DriverManager.getConnection("jdbc:mysql://localhost/mydb?useSSL=false&characterEncoding=utf8", "root",
    					"520520t");
    		} catch (ClassNotFoundException e) {
    			e.printStackTrace();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		return con;
    	}
    
    	public static int totalPages(int pageSize) {
    		int total = getTotal();
    		int totalPages = (total % pageSize == 0) ? (total / pageSize) : (total / pageSize) + 1;
    		return totalPages;
    	}
    
    	private static int getTotal() {
    		try {
    			Connection con = getConnection();
    			PreparedStatement ps = con.prepareStatement("SELECT COUNT(1) AS total FROM EMPLOYEES");
    			ResultSet rs = ps.executeQuery();
    			while (rs.next()) {
    				return rs.getInt("total");
    			}
    
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    		return 0;
    	}
    
    	public static List<Employees> getRecords(int currentPage, int pageSize) {
    		List<Employees> list = new ArrayList<Employees>();
    		int start = 0;
    		if (currentPage > 0) {
    			start = (currentPage - 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()) {
    				Employees e = new Employees();
    				e.setId(rs.getInt(1));
    				e.setName(rs.getString(2));
    				e.setSalary(rs.getFloat("salary"));
    				list.add(e);
    			}
    			con.close();
    
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    		return list;
    	}
    
    }

PagingServlet.java：

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.hthk.login.bean.Employees;
    import com.hthk.login.dao.EmpDao;
    
    @WebServlet("/PagingServlet")
    public class PagingServlet extends HttpServlet {
    
    	private static final long serialVersionUID = 1L;
    
    	@Override
    	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
    		resp.setCharacterEncoding("UTF-8");
    		resp.setContentType("text/html;charset=utf-8");
    		req.setCharacterEncoding("UTF-8");
    		PrintWriter out = resp.getWriter();
    		// 每页3条记录
    		int pageSize = 3;
    		String spage = req.getParameter("page");
    		if (spage == null || spage == "") {
    			spage = "1";
    		}
    		int currentPage = Integer.parseInt(spage);
    		int totalPage = EmpDao.totalPages(pageSize);
    
    		List<Employees> list = EmpDao.getRecords(currentPage, 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 (Employees 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='PagingServlet?page=1'>首页</a> ");
    		for (int i = 1; i <= totalPage; i++) {
    			out.print("<a href='PagingServlet?page=" + i + "'>" + i + "</a> ");
    		}
    		out.print("<a href='PagingServlet?page=" + totalPage + "'>最后一页</a> ");
    		out.close();
    	}
    
    }

运行页面展示：

![70][]

![70 1][]

第`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

注意：

这里不用配置web.xml，使用的是：

@WebServlet("/PagingServlet")

如果使用这个注解，应该在`tomcat7`以上版本中部署运行，`tomcat7`之前的版本是不支持注解的。 `@WebServlet`注释用于将servlet映射到指定的名称，这是Servlet3的特性，所以在创建项目时要指定Servlet的版本为`3.0`以上。

[Link 1]: https://www.yiibai.com/servlet/pagination-in-servlet.html
[70]: /images/20220522/4cd156315cb44c48b1c606d3e2d5eee6.png
[70 1]: /images/20220522/ab85abb4c3e4481089f9a053d5bdd432.png