MySQl + java-web 数据分页案例

桃扇骨 2022-06-07 10:43 129阅读 0赞

**MySQl + java-web 数据分页案例**

本案例使用MySQl数据库，Servlet，jsp实现数据的分页功能。案例使用三层架构（持久层：操作数据库；服务层：业务处理；表现层：数据的接收，页面跳转等操作）表现层调用服务层，服务层调用持久层。步骤如下：

一、准备数据

-- 创建数据库
    CREATE DATABASE db;
    -- 使用数据库
    USE db;
    -- 创建数据表
    CREATE TABLE dept(
    deptno INT PRIMARY KEY AUTO_INCREMENT,
    dname VARCHAR(20),
    loc VARCHAR(30)
    );
    
    -- 添加数据
    INSERT INTO dept(dname,loc) VALUES('测试','地址1');
    INSERT INTO dept(dname,loc) VALUES('美工部','地址2');
    INSERT INTO dept(dname,loc) VALUES('测试部','地址3');
    INSERT INTO dept(dname,loc) VALUES('市场部','地址4');
    INSERT INTO dept(dname,loc) VALUES('维护部','地址5');
    INSERT INTO dept(dname,loc) VALUES('项目部','地址6');
    INSERT INTO dept(dname,loc) VALUES('电竞部','地址7');
    INSERT INTO dept(dname,loc) VALUES('套路部','地址8');
    INSERT INTO dept(dname,loc) VALUES('分析部','地址9');
    INSERT INTO dept(dname,loc) VALUES('加工部','地址10');

数据截图：

![Center][]

二、创建javaWeb项目

项目中需要导入的包如下：

jstl.jar和/standard.jar为jstl表达式jar包，mysql-connector-java-5.1.39-bin.jar 为mysql数据库驱动包

![Center 1][]

注：导入包之后记得将jar包加载到项目中，可以如图操作：选中包右键--》Build Path--》Add to Build Path

![Center 2][]

项目结构如下：

![Center 3][]

**代码如下：**

1、index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    	pageEncoding="UTF-8"%>
    
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
    
    <%
    	String path = request.getContextPath();
    	String basepath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort()
    			+ path + "/";
    %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    
    <base href="<%=basepath%>">
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>MYSQl分页</title>
    </head>
    <body>
    	<table border="1" width="800" cellspacing="0">
    		<tr>
    			<th>部门编号</th>
    			<th>部门名称</th>
    			<th>部门地址</th>
    			<th>操作</th>
    		</tr>
    		
    		<c:if test="${list==null }">
    			<tr>
    				<td colspan="4" style="text-align: center;">无数据</td>
    			</tr>
    		</c:if>
    		
    		<c:forEach items="${list }" var="d">
    			<tr>
    				<td>${d.deptno }</td>
    				<td>${d.dname }</td>
    				<td>${d.loc }</td>
    				<td>修改</td>
    			</tr>
    		</c:forEach>
    
    	</table>
    
    	
    	
    	<jsp:include page="page.jsp">
    		<jsp:param value="dept.do" name="myurl" />
    	</jsp:include>
    
    </body>
    </html>

2、page.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    	pageEncoding="UTF-8"%>
    
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title></title>
    <style type="text/css">
    /* 当前页码样式 */
    .currentpage {
    	font-size: 20px;
    	color: red;
    	margin-left: 5px;
    	margin-right: 5px;
    	color: red;
    	font-weight: bold;
    	text-decoration: none;
    }
    /* 未选中的其它页码样式 */
    .otherpage {
    	font-size: 14px;
    	margin-left: 5px;
    	margin-right: 5px;
    	text-decoration: none;
    }
    /* 提示信息文字样式 */
    .pagenew {
    	color: gray;
    	font-size: 10px;
    }
    
    .pageinfo {
    	font-size: 14px;
    	color: green;
    	font-weight: bold;
    }
    </style>
    <script type="text/javascript">
    	function changePage(cp) {
    		/* 获取总页数 */
    		var pagecounts = parseInt(document.getElementById("pagecounts").value);
    		/* 判断要查看的页数是否在 1-pagecounts 之间，是则发送请求数据，不是则提示没有数据了 */
    		if (parseInt(cp) >= 0 && parseInt(cp) <= pagecounts) {
    			/* 	改变当前页码 */
    			pageform.cp.value = cp;
    
    			/* 提交表单 */
    			pageform.submit();
    		} else {
    			alert("没有数据了");
    		}
    	}
    </script>
    </head>
    <body>
    	<form action="${param.myurl }" id="pageform" name="pageform">
    		
    		<input type="hidden" value="${cp }" id="cp" name="cp" />
    		
    		<input type="hidden" value="${counts }" id="counts" />
    		
    		<input type="hidden" value="${pageCounts }" id="pagecounts" />
    		
    		<input type="button" value="首页" <c:if test="${cp==1 }">disabled</c:if>
    			οnclick="changePage('1');" />
    		
    		<input type="button" value="上一页"
    			<c:if test="${cp==1 }">disabled</c:if>
    			οnclick="changePage('${cp-1}');" />
    
    		
    		<c:if test="${cp>3 }">
    			<a href="JavaScript:changePage('${cp-3 }')" class="otherpage">...</a>
    		</c:if>
    
    		<c:if test="${cp>2 }">
    			<a href="JavaScript:changePage('${cp-2 }')" class="otherpage">${cp-2 }</a>
    		</c:if>
    
    		<c:if test="${cp>1 }">
    			<a href="JavaScript:changePage('${cp-1 }')" class="otherpage">${cp-1 }</a>
    		</c:if>
    		
    		<a href="JavaScript:void(0)" class="currentpage">${cp }</a>
    
    		<c:if test="${cp<pageCounts }">
    			<a href="JavaScript:changePage('${cp+1 }')" class="otherpage">${cp+1 }</a>
    		</c:if>
    
    		<c:if test="${cp+1<pageCounts }">
    			<a href="JavaScript:changePage('${cp+2 }')" class="otherpage">${cp+2 }</a>
    		</c:if>
    
    		<c:if test="${cp+2<pageCounts }">
    			<a href="JavaScript:changePage('${cp+3 }')" class="otherpage">...</a>
    		</c:if>
    		
    		
    		<input type="button" value="下一页" ${cp==pageCounts?"disabled":"" }
    			οnclick="changePage('${cp+1}');" />
    			
    		
    		<input type="button" value="尾页" ${cp==pageCounts?"disabled":"" }
    			οnclick="changePage('${pageCounts}');" />
    			
    		
    		<span class="pagenew"> 共查询到<span class="pageinfo">${counts }
    		</span>条数据，共<span class="pageinfo">${pageCounts }</span> 页，
    		现在是第<span class="pageinfo">${cp }</span> 页 ，每页显示 
    			
    		 <select name="ps" id="ps" οnchange="changePage('1')">
    				<option value="3" ${ps==3?"selected":"" }>3</option>
    				<option value="5" ${ps==5?"selected":"" }>5</option>
    				<option value="10" ${ps==10?"selected":"" }>10</option>
    				<option value="15" ${ps==15?"selected":"" }>15</option>
    				<option value="20" ${ps==20?"selected":"" }>20</option>
    		</select>
    		</span>
    	</form>
    </body>
    </html>

**表现层**  
3、DeptServlet.java (使用注解的方式配置请求路径@WebServlet("/dept.do"))

package cn.sz.servlet;
    
    import java.io.IOException;
    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 cn.sz.pojo.Dept;
    import cn.sz.service.DeptServiceImpl;
    import cn.sz.service.IDeptService;
    
    @WebServlet("/dept.do")
    public class DeptServlet extends HttpServlet {
    	private static final long serialVersionUID = 1L;
    
    	protected void doGet(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		this.doPost(request, response);
    	}
    
    	protected void doPost(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		// 设置请求编码格式（防止中文乱码）
    		request.setCharacterEncoding("UTF-8");
    		// 设置响应编码格式（防止中文乱码）
    		response.setHeader("Content-Type", "text/html;charset=utf-8");/* 处理中文响应乱码 */
    		// 接收需要查看的页码
    		String cps = request.getParameter("cp");
    		// 接收每一页显示的数目
    		String pss = request.getParameter("ps");
    
    		int cp = 1;// 默认显示第一页
    		int ps = 5;// 默认每页显示5条
    
    		if (cps != null && !cps.equals("")) {
    			cp = Integer.parseInt(cps);
    		}
    		if (pss != null && !pss.equals("")) {
    			ps = Integer.parseInt(pss);
    		}
    		// 实例化Service层（调用Service层方法）
    		IDeptService iDeptService = new DeptServiceImpl();
    		// 查询总 的记录数
    		int counts = iDeptService.getCounts();
    		// 计算总是页数
    		int pageCounts = (counts - 1) / ps + 1;
    		// 查看要查看的页码的数据
    		List<Dept> list = iDeptService.splitPage(cp, ps);
    
    		// 保存需要返回的数据
    		request.setAttribute("cp", cp);
    		request.setAttribute("ps", ps);
    		request.setAttribute("list", list);
    		request.setAttribute("counts", counts);
    		request.setAttribute("pageCounts", pageCounts);
    		// 转发到index.jsp页面
    		request.getRequestDispatcher("index.jsp").forward(request, response);
    
    	}
    
    }

**持久层**  
4、IDeptDAO.java

package cn.sz.dao;
    
    import java.util.List;
    
    import cn.sz.pojo.Dept;
    
    public interface IDeptDAO {
    	/**
    	 * 得到总的记录数
    	 * 
    	 * @return
    	 */
    	int getCounts();
    
    	/**
    	 * 查询每一个的记录
    	 * 
    	 * @param pageNum
    	 *            要查询的页码
    	 * @param pageSize
    	 *            每页显示的数目
    	 * @return
    	 */
    
    	List<Dept> splitPage(int pageNum, int pageSize);
    
    }

5、DeptDAOImpl.java

package cn.sz.dao;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    import cn.sz.pojo.Dept;
    import cn.sz.utils.DButils;
    
    public class DeptDAOImpl implements IDeptDAO {
    
    	@Override
    	public int getCounts() {
    		Connection conn = null;
    		PreparedStatement psmt = null;
    		ResultSet rs = null;
    		try {
    			conn = DButils.getConn();
    			String sql = "SELECT COUNT(deptno) FROM dept";
    			psmt = conn.prepareStatement(sql);
    			rs = psmt.executeQuery();
    			if (rs.next()) {
    				return rs.getInt(1);
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		} finally {
    			DButils.close(conn, psmt, rs);
    		}
    		return 0;
    	}
    
    	@Override
    	public List<Dept> splitPage(int pageNum, int pageSize) {
    		List<Dept> list = new ArrayList<>();
    		Connection conn = null;
    		PreparedStatement psmt = null;
    		ResultSet rs = null;
    		try {
    			conn = DButils.getConn();
    			String sql = "SELECT deptno,dname,loc FROM dept LIMIT ?,?";
    			psmt = conn.prepareStatement(sql);
    			psmt.setInt(1, (pageNum - 1) * pageSize);
    			psmt.setInt(2, pageSize);
    			rs = psmt.executeQuery();
    			while (rs.next()) {
    				Dept d = new Dept();
    				int deptno = rs.getInt(1);
    				String dname = rs.getString(2);
    				String loc = rs.getString(3);
    				d.setDeptno(deptno);
    				d.setDname(dname);
    				d.setLoc(loc);
    				list.add(d);
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		} finally {
    			DButils.close(conn, psmt, rs);
    		}
    		return list;
    	}
    
    }

**服务层**  
6、IDeptService.java

package cn.sz.service;
    
    import java.util.List;
    
    import cn.sz.pojo.Dept;
    
    public interface IDeptService {
    	/**
    	 * 得到总的记录数
    	 * 
    	 * @return
    	 */
    	int getCounts();
    
    	/**
    	 * 查询每一个的记录
    	 * 
    	 * @param pageNum
    	 *            要查询的页码
    	 * @param pageSize
    	 *            每页显示的数目
    	 * @return
    	 */
    
    	List<Dept> splitPage(int pageNum, int pageSize);
    }

7、DeptServiceImpl.java

package cn.sz.service;
    
    import java.util.List;
    
    import cn.sz.dao.DeptDAOImpl;
    import cn.sz.dao.IDeptDAO;
    import cn.sz.pojo.Dept;
    
    public class DeptServiceImpl implements IDeptService {
    	private IDeptDAO iDeptDAO = new DeptDAOImpl();
    
    	@Override
    	public int getCounts() {
    		return iDeptDAO.getCounts();
    	}
    
    	@Override
    	public List<Dept> splitPage(int pageNum, int pageSize) {
    		if (pageNum < 0 || pageSize < 0)
    			return null;
    		return iDeptDAO.splitPage(pageNum, pageSize);
    	}
    
    }

**数据库管理类**  
8、DButils.java

package cn.sz.utils;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    import com.mysql.jdbc.Driver;
    
    public class DButils {
    	/**
    	 * 数据库的属性信息
    	 */
    	private static class DbProperties {
    		/** 数据库主机 IP地址 */
    		static final String IP = "localhost";
    		/** 数据库端口号 */
    		static final String PORT = "3306";
    		/** 数据库名 */
    		static final String DB_NAME = "db";
    		/** 数据库 URL */
    		static final String URL = "jdbc:mysql://" + IP + ":" + PORT + "/" + DB_NAME + "?charsetEncoding=utf-8";
    		// static final String URL =
    		// "jdbc:mysql://localhost:3306/db?charsetEncoding=utf-8";
    		/** 数据库连接用户名 */
    		static final String USER = "root";
    		/** 用户密码 */
    		static final String PSW = "";
    	}
    
    	// 获取数据库连接方法
    	public static Connection getConn() {
    		try {
    			DriverManager.registerDriver(new Driver());
    		} catch (SQLException e1) {
    			e1.printStackTrace();
    		}
    		Connection conn = null;
    		try {
    			conn = DriverManager.getConnection(DbProperties.URL, DbProperties.USER, DbProperties.PSW);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		return conn;
    	}
    
    	// 关闭数据库连接方法
    	public static void close(Connection conn, PreparedStatement psmt, ResultSet rs) {
    		// 关闭资源（先开的后关）
    		try {
    			if (rs != null)
    				rs.close();
    		} catch (SQLException e) {
    
    			e.printStackTrace();
    		}
    		try {
    			if (psmt != null)
    				psmt.close();
    		} catch (SQLException e) {
    
    			e.printStackTrace();
    		}
    		try {
    			if (conn != null)
    				conn.close();
    		} catch (SQLException e) {
    
    			e.printStackTrace();
    		}
    	}
    
    }

**实体类**  
9、Dept.java

package cn.sz.pojo;
    
    import java.io.Serializable;
    
    public class Dept implements Serializable {
    	private Integer deptno;
    	private String dname;
    	private String loc;
    
    	public Dept() {
    	}
    
    	public Dept(Integer deptno, String dname, String loc) {
    		this.deptno = deptno;
    		this.dname = dname;
    		this.loc = loc;
    	}
    
    	public Integer getDeptno() {
    		return deptno;
    	}
    
    	public void setDeptno(Integer deptno) {
    		this.deptno = deptno;
    	}
    
    	public String getDname() {
    		return dname;
    	}
    
    	public void setDname(String dname) {
    		this.dname = dname;
    	}
    
    	public String getLoc() {
    		return loc;
    	}
    
    	public void setLoc(String loc) {
    		this.loc = loc;
    	}
    
    	@Override
    	public String toString() {
    		return "Dept [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + "]";
    	}
    
    }

完成什么操作后，将项目部署到服务器。在游览器输入：http://localhost:8080/MysqlPage/dept.do 就可以查看效果

![Center 4][]

[Center]: /images/20220607/337275de4a2d438badb6b845b1c07ea9.png
[Center 1]: /images/20220607/eae153cb9f9d44dc817a9f732363225f.png
[Center 2]: /images/20220607/305245cce03c435a98a0928f6db5c97e.png
[Center 3]: /images/20220607/048c4eb963434c4bbf6799f2f0f1a37d.png
[Center 4]: /images/20220607/8478d15f41434088b84355e893f17a7b.png