MySQl + java-web 数据分页案例

来源：互联网发布：中小型网络拓扑及搭建编辑：程序博客网时间：2024/05/16 14:28

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');

数据截图：

二、创建javaWeb项目

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

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

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

项目结构如下：

代码如下：

1、index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%><!-- 引入jstl标签库 --><%@ 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><!--分页  --><!-- 传递参数为处理分页的url --><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"%><!-- 引入jstl标签库 --><%@ 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" /><!-- 首页按钮（如果当前就是首页则将按钮设置为disabled（禁用状态）） --><input type="button" value="首页" <c:if test="${cp==1 }">disabled</c:if>onclick="changePage('1');" /><!-- 上一页按钮（如果当前就是第一页则将按钮设置为disabled（禁用状态）） --><input type="button" value="上一页"<c:if test="${cp==1 }">disabled</c:if>onclick="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><!-- 下一页按钮（如果当前就是最后一页则将按钮设置为disabled（禁用状态）） --><input type="button" value="下一页" ${cp==pageCounts?"disabled":"" }onclick="changePage('${cp+1}');" /><!-- 尾页按钮（如果当前就是最后一页则将按钮设置为disabled（禁用状态）） --><input type="button" value="尾页" ${cp==pageCounts?"disabled":"" }onclick="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" onchange="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 {@Overridepublic 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;}@Overridepublic 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();@Overridepublic int getCounts() {return iDeptDAO.getCounts();}@Overridepublic 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;}@Overridepublic String toString() {return "Dept [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + "]";}}

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

阅读全文

0 0