下面以一个示例讲解如何使用MySQL+Service+Servlet+Jsp实现Table表格分页展示数据：
eg：请假管理系统
要求如下：
一、打开首页页面， 访问查询请假记录的 servlet , 将查询结果在列表页面进行分页展并按照请假时同升序排序。整个页面的标题需要加粗加大显示，请假记录列表要求使用式实现对表头文字加粗显示， 超链接 (包括 “ 删除” 和 “ 添加请假记录” 两个) 无下划线且 ’添加请假记录“字体为红色， 并实现信息列表隔行变色显示， 程序运行界面下：
点击 “添加请假记录“ 进入添加请假记录页面；如下图所示：
点击”提交“按钮提交表单时.要求使用‘jQuery对页面输入项进行验证.验证内包括姓名，请假shij、 请假原因的非空验证 ； 其中请假时间还必须进行格式验证 ，验证效果如下所示：
表单数据通过验证后. 则提交请求至添加请假记录的 Servlet:, 如添加成功，则给出成功提示，如下图：
点击 ”确定’ 后跳转至请假记录信息的列表页面， 并非显示最新的请假记录信息， 效果如下所示：
如果添加请假记录信息失败，同样给出添加失败提示，并跳转回添加请假记录页面，重新添加请假记录信息；
当用户点击某一条请假记录信息对应的 “删除” 超链接时， 首先弹出信息提示框确认是否删除 ， 效果如下图：
当用户点击 “确定" 后, 实现请假讵录的删除操作， 要求使用Ajax 异步请求后台Sevlet 方法进行删除，最后给出删除成功或者失败的信息提示；
二、MySQL数据库设计如下：
三、项目分层设计如下：
项目代码如下：
LeaveRecordsDao.java
packagecn.jbit.leaveReccords.dao;importjava.util.List;importjava.util.Map;importcn.jbit.leaveReccords.entity.LeaveRecords;public interfaceLeaveRecordsDao {/*** 删除请假信息
*
*@paramid
*@return
*/
public intdeleteLeaveRecords(Integer id);/*** 添加请假信息
*
*@paramleaveRecords 请假信息的对象
*@return
*/
public intaddLeaveRecords(LeaveRecords leaveRecords);/*** 分页查询所有消息
*@parampageIndex 页码
*@parampageSize 数据行数
*@return查询到的集合*/
public List selecteLeaveRecords(int pageIndex, intpageSize);/*** 查询总记录数
*@return查询到记录总数*/
public intcount();
}
LeaveRecordsDaoImpl.java
packagecn.jbit.leaveReccords.dao.impl;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.List;importjava.util.Map;importcn.jbit.leaveReccords.dao.BaseDao;importcn.jbit.leaveReccords.dao.LeaveRecordsDao;importcn.jbit.leaveReccords.entity.LeaveRecords;importcn.jbit.leaveReccords.util.DatabaseUtil;public class LeaveRecordsDaoImpl extends BaseDao implementsLeaveRecordsDao {/*** 删除*/@Overridepublic intdeleteLeaveRecords(Integer id) {int result = 0;
String sql= "DELETE FROM leaverecords WHERE id=?";try{
result= this.executeUpdate(sql, id);
}catch(SQLException e) {
e.printStackTrace();
}returnresult;
}
@Overridepublic intaddLeaveRecords(LeaveRecords leaveRecords) {int result = 0;
String sql= "INSERT INTO `leaverecords`(`name`,leaveTime,reason) VALUES (?,?,?)";try{
result= this.executeUpdate(sql, leaveRecords.getName(), leaveRecords.getLeaveTime(),
leaveRecords.getReason());
}catch(SQLException e) {
e.printStackTrace();
}returnresult;
}/*** 分页显示数据*/@Overridepublic List selecteLeaveRecords(int pageIndex, intpageSize) {
String sql= "SELECT id,`name`,leaveTime,reason FROM leaverecords ORDER BY leaveTime ASC limit ?,?";
Connection conn= null;
PreparedStatement pstmt= null;
ResultSet rs= null;
LeaveRecords records= null;
List leaveRecordsList = new ArrayList();try{
conn=DatabaseUtil.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, (pageIndex - 1) *pageSize);
pstmt.setInt(2, pageSize);
rs=pstmt.executeQuery();while(rs.next()) {
records= newLeaveRecords();
records.setId(rs.getInt("id"));
records.setName(rs.getString("name"));
records.setLeaveTime(rs.getDate("leaveTime"));
records.setReason(rs.getString("reason"));
leaveRecordsList.add(records);
}
}catch(SQLException e) {
e.printStackTrace();
}finally{
DatabaseUtil.closeAll(conn, pstmt, rs);
}returnleaveRecordsList;
}/*** 查询总数*/@Overridepublic intcount() {int result = 0;
String sql= "SELECT count(1) FROM leaverecords";
ResultSet rs= null;try{
rs= this.executeQuery(sql);while(rs.next()) {
result= rs.getInt(1);
}
}catch(SQLException e) {
e.printStackTrace();
}finally{
DatabaseUtil.closeAll(null, null, rs);
}returnresult;
}}
BaseDao.java
packagecn.jbit.leaveReccords.dao;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importcn.jbit.leaveReccords.util.DatabaseUtil;/*** 执行数据库操作的工具类。*/
public classBaseDao {privateConnection conn;/*** 增、删、改操作的方法
*
*@paramsql sql语句
*@paramprams 参数数组
*@return执行结果
*@throwsSQLException*/
protected int executeUpdate(String sql, Object... params) throwsSQLException {int result = 0;
conn=DatabaseUtil.getConnection();
PreparedStatement pstmt= null;try{
pstmt=conn.prepareStatement(sql);for (int i = 0; i < params.length; i++) {
pstmt.setObject(i+ 1, params[i]);
}
result=pstmt.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();throwe;
}finally{
DatabaseUtil.closeAll(null, pstmt, null);
}returnresult;
}/*** 查询操作的方法
*
*@paramsql sql语句
*@paramparams 参数数组
*@return查询结果集
*@throwsSQLException*/
protected ResultSet executeQuery(String sql, Object... params) throwsSQLException {
PreparedStatement pstmt= null;
conn=DatabaseUtil.getConnection();
ResultSet rs= null;try{
pstmt=conn.prepareStatement(sql);for (int i = 0; i < params.length; i++) {
pstmt.setObject(i+ 1, params[i]);
}
rs=pstmt.executeQuery();
}catch(SQLException e) {
e.printStackTrace();throwe;
}returnrs;
}
}
LeaveRecords.java
packagecn.jbit.leaveReccords.entity;importjava.util.Date;public classLeaveRecords {private Integer id;//编号
private String name;//请假人姓名
private Date leaveTime;//请假时间
private String reason;//请假原因
publicLeaveRecords() {super();
}publicLeaveRecords(String name, Date leaveTime, String reason) {super();this.name =name;this.leaveTime =leaveTime;this.reason =reason;
}publicLeaveRecords(Integer id, String name, Date leaveTime, String reason) {super();this.id =id;this.name =name;this.leaveTime =leaveTime;this.reason =reason;
}//省略了getter和setter方法
}
LeaveRecordsService.java
packagecn.jbit.leaveReccords.service;importjava.util.List;importjava.util.Map;importcn.jbit.leaveReccords.entity.LeaveRecords;importcn.jbit.leaveReccords.util.Page;public interfaceLeaveRecordsService {/*** 分页
*@parampage*/
public void RecordsList(Pagepage);/*** 添加请假信息
*
*@paramleaveRecords
*@return
*/
publicInteger insertLeaveRecords(LeaveRecords leaveRecords);/*** 删除请假信息
*
*@paramid
*@return
*/
public Integer deleteLeaveRecords(intid);
}
LeaveRecordsServiceImpl.java
packagecn.jbit.leaveReccords.service.impl;importjava.util.List;importjava.util.Map;importcn.jbit.leaveReccords.dao.LeaveRecordsDao;importcn.jbit.leaveReccords.dao.impl.LeaveRecordsDaoImpl;importcn.jbit.leaveReccords.entity.LeaveRecords;importcn.jbit.leaveReccords.service.LeaveRecordsService;importcn.jbit.leaveReccords.util.Page;public class LeaveRecordsServiceImpl implementsLeaveRecordsService {
LeaveRecordsDao leaveRecordsDao= newLeaveRecordsDaoImpl();
@OverridepublicInteger insertLeaveRecords(LeaveRecords leaveRecords) {returnleaveRecordsDao.addLeaveRecords(leaveRecords);
}
@Overridepublic Integer deleteLeaveRecords(intid) {returnleaveRecordsDao.deleteLeaveRecords(id);
}
@Overridepublic void RecordsList(Pagepage) {int count=leaveRecordsDao.count();//获取所有消息的数量
page.setTotalCount(count);//判断传入的页面是否合法
if(page.getPageIndex()>page.getTotalPageCount()) {//确保页面不会超过总页数
page.setPageIndex(page.getTotalPageCount());
}
List dataList=leaveRecordsDao.selecteLeaveRecords(page.getPageIndex(), page.getPageSize());
page.setDataList(dataList);
}
}
LeaveRecordsDaoTest.java
packagecn.jbit.leaveReccords.test;importjava.text.ParseException;importjava.text.SimpleDateFormat;importjava.util.Date;importjava.util.HashMap;importjava.util.List;importjava.util.Map;importorg.junit.Test;importcn.jbit.leaveReccords.dao.LeaveRecordsDao;importcn.jbit.leaveReccords.dao.impl.LeaveRecordsDaoImpl;importcn.jbit.leaveReccords.entity.LeaveRecords;importcn.jbit.leaveReccords.service.LeaveRecordsService;importcn.jbit.leaveReccords.service.impl.LeaveRecordsServiceImpl;public classLeaveRecordsDaoTest {
@Testpublic voidLeaveRecords() {
LeaveRecordsService recordsService=newLeaveRecordsServiceImpl();//添加
String date="2018-08-07";
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
Date leaveTime=null;try{
leaveTime=(Date)sdf.parse(date);
}catch(ParseException e) {
e.printStackTrace();
}
LeaveRecords leaveRecords=new LeaveRecords("www", leaveTime, "successful");int result=recordsService.insertLeaveRecords(leaveRecords);
System.out.println(result);//删除
int num=recordsService.deleteLeaveRecords(20);
System.out.println(num);//分页查询
LeaveRecordsDao recordsDao=newLeaveRecordsDaoImpl();
List list=recordsDao.selecteLeaveRecords(1, 5);for(LeaveRecords leaveRecords2 : list) {
System.out.println(leaveRecords2.getName());
}//查询
Map param=new HashMap();
param.put("records", new LeaveRecords(null, null, null, null));
List listinfo=recordsService.findLeaveRecords(param);for(LeaveRecords leaveRecords2 : listinfo) {
System.out.println(leaveRecords2.getLeaveTime());
}
}
}
LeaveRecordsServlet.java
packagecn.jbit.leaveReccords.servlet;importjava.io.IOException;importjava.io.PrintWriter;importjava.text.ParseException;importjava.text.SimpleDateFormat;importjava.util.Date;importjava.util.List;importjavax.servlet.ServletException;importjavax.servlet.annotation.WebServlet;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importcn.jbit.leaveReccords.entity.LeaveRecords;importcn.jbit.leaveReccords.service.LeaveRecordsService;importcn.jbit.leaveReccords.service.impl.LeaveRecordsServiceImpl;importcn.jbit.leaveReccords.util.Page;
@WebServlet(urlPatterns= { "/LeaveRecordsServlet" }, name = "LeaveRecordsServlet")public class LeaveRecordsServlet extendsHttpServlet {private static final long serialVersionUID = -8076807217250462175L;
@Overrideprotected voiddoGet(HttpServletRequest request, HttpServletResponse response)throwsServletException, IOException {
doPost(request, response);
}
@Overrideprotected voiddoPost(HttpServletRequest request, HttpServletResponse response)throwsServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=utf-8");
LeaveRecordsService leaveRecordsService= newLeaveRecordsServiceImpl();//根据action决定作什么操作
String action = request.getParameter("action");//分页查询
if ("list".equals(action)) {//获取当前页数参数
String pageIndex = request.getParameter("pageIndex");//获取页面大小参数(每页显示的数量)//String pageSize = request.getParameter("pageSize");
int index = 1;//设置首页为1
int size = 8;//设置页面大小为8条数据
try{if (pageIndex == null) {
index= 1;
}else{
index=Integer.parseInt(pageIndex);
}//size=Integer.parseInt(pageSize);
} catch(Exception e) {
e.printStackTrace();
}//将分页参数封装到分页对象中
Page page = new Page();
page.setPageIndex(index);
page.setPageSize(size);//调用Service层进行分页查询
leaveRecordsService.RecordsList(page);//尾页填充空白行(为了视觉美观)(效果图如下图中的空白行)
List recordsList =page.getDataList();if (recordsList.size()
recordsList.add(null);
}
page.setDataList(recordsList);//将业务层处理后的分页对象存放至request作用域中
request.setAttribute("page", page);
request.getRequestDispatcher("select.jsp").forward(request, response);
}//删除记录
if ("delete".equals(action)) {
String sid= request.getParameter("opt");int id =Integer.parseInt(sid);int deleteInfo =leaveRecordsService.deleteLeaveRecords(id);
PrintWriter out=response.getWriter();booleanresult;if (deleteInfo > 0) {
result= true;
}else{
result= false;
}
out.print(result);
out.flush();
out.close();
}//增加记录
if("insert".equals(action)) {//请假人姓名
String name=request.getParameter("name");//请假时间
String time=request.getParameter("leaveTime");
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
Date leaveTime=null;try{
leaveTime=(Date)sdf.parse(time);
}catch(ParseException e) {
e.printStackTrace();
}//请假原因
String reason=request.getParameter("reason");
LeaveRecords leaveRecords=newLeaveRecords(name, leaveTime, reason);int result=leaveRecordsService.insertLeaveRecords(leaveRecords);
PrintWriter out=response.getWriter();if(result>0) {
out.println("
+ "alert('添加成功！');"
+ "location.href='LeaveRecordsServlet?action=list'"
+ "");
}else{
out.print("
+ "alert('添加失败！')"
+ "loction='LeaveRecordsServlet?action=insert'" + "");
}
}
}
}
ConfigManager.java
packagecn.jbit.leaveReccords.util;importjava.io.IOException;importjava.io.InputStream;importjava.util.Properties;/*** 数据库参数配置文件查找工具类
*@author逆風〠飛�?
**/
public classConfigManager {private static Properties props = null;static{
InputStream is= null;
is= ConfigManager.class.getClassLoader().getResourceAsStream("database.properties");if (is == null)throw new RuntimeException("找不到数据库参数配置文件�?");
props= newProperties();try{
props.load(is);
}catch(IOException e) {throw new RuntimeException("数据库配置参数加载错误！", e);
}finally{try{
is.close();
}catch(IOException e) {
e.printStackTrace();
}
}
}public staticString getProperty(String key) {returnprops.getProperty(key);
}
}
DatabaseUtil.java
packagecn.jbit.leaveReccords.util;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;/*** 数据库连接与关闭工具类�??
**/
public classDatabaseUtil {private static String driver = ConfigManager.getProperty("driver");//数据库驱动字符串
private static String url = ConfigManager.getProperty("url"); //连接URL字符�?
private static String user = ConfigManager.getProperty("user"); //数据库用户名
private static String password = ConfigManager.getProperty("password"); //用户密码
static{try{
Class.forName(driver);
}catch(ClassNotFoundException e) {
e.printStackTrace();
}
}/*** 获取数据库连接对象�??*/
public static Connection getConnection() throwsSQLException {//获取连接并捕获异�?
Connection conn = null;try{
conn=DriverManager.getConnection(url, user, password);
}catch(SQLException e) {
e.printStackTrace();throwe;
}return conn;//返回连接对象
}/***
* 关闭数据库连�?
*@paramconn 数据库连�?
*@paramstmt Statement对象
*@paramrs 结果�?*/
public static voidcloseAll(Connection conn, Statement stmt, ResultSet rs) {//若结果集对象不为空，则关�?
try{if (rs != null && !rs.isClosed())
rs.close();
}catch(Exception e) {
e.printStackTrace();
}//若Statement对象不为空，则关�?
try{if (stmt != null && !stmt.isClosed())
stmt.close();
}catch(Exception e) {
e.printStackTrace();
}//若数据库连接对象不为空，则关�?
try{if (conn != null && !conn.isClosed())
conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
EmptyUtils.java
packagecn.jbit.leaveReccords.util;importjava.util.Collection;importjava.util.Map;/*** 判断是否为空的工具类
*@author逆風〠飛翔
**/
public classEmptyUtils {//鍒ょ┖
public static booleanisEmpty(Object obj) {if (obj == null)return true;if (obj instanceofCharSequence)return ((CharSequence) obj).length() == 0;if (obj instanceofCollection)return((Collection) obj).isEmpty();if (obj instanceofMap)return((Map) obj).isEmpty();if (obj instanceofObject[]) {
Object[] object=(Object[]) obj;if (object.length == 0) {return true;
}boolean empty = true;for (int i = 0; i < object.length; i++) {if (!isEmpty(object[i])) {
empty= false;break;
}
}returnempty;
}return false;
}public static booleanisNotEmpty(Object obj) {return !isEmpty(obj);
}private booleanvalidPropertyEmpty(Object... args) {for (int i = 0; i < args.length; i++) {if(EmptyUtils.isEmpty(args[i])) {return true;
}
}return false;
}
}
Page.java
packagecn.jbit.leaveReccords.util;importjava.util.List;/*** 分页用的基类，抽取了通用的分页参数
**/
public class Page{private int pageIndex=1;//当前页数
private int pageSize=8 ;//每页显示的行数
private int totalCount;//总记录数
private int totalPageCount;//总页数
private List dataList ;//分页结果的泛型集合
public intgetPageIndex() {returnpageIndex;
}public void setPageIndex(intpageIndex) {//判断当前页码，如果页码大于零，则显示为当前的pageIndex页面，否则pageIndex为1，即第一页
if (pageIndex > 0) {this.pageIndex =pageIndex;
}else{this.pageIndex = 1;
}
}public intgetPageSize() {returnpageSize;
}public void setPageSize(intpageSize) {//设置每页显示数据的条数
if (pageSize > 0) {this.pageSize =pageSize;
}else{this.pageSize = 5;
}
}public intgetTotalCount() {returntotalCount;
}public void setTotalCount(inttotalCount) {if (totalCount > 0) {//总记录数
this.totalCount =totalCount;//计算总页数
this.totalPageCount = (this.totalCount % this.pageSize == 0) ? (this.totalCount / this.pageSize)
: (this.totalCount / this.pageSize + 1);
}
}public intgetTotalPageCount() {returntotalPageCount;
}public void setTotalPageCount(inttotalPageCount) {this.totalPageCount =totalPageCount;
}public ListgetDataList() {returndataList;
}public void setDataList(ListdataList) {this.dataList =dataList;
}
}
配置文件代码：
database.properties
#数据库连接驱动
driver=com.mysql.jdbc.Driver
#数据库用户名
user=WebTest
#数据库密码
password=1234#连接URL字符串
url=jdbc\:mysql\://localhost\:3306/leaverecords?useSSL\=false
JSP页面代码：
查询JSP:select.jsp
String basePath= request.getScheme() + "://" + request.getServerName() + ":" +request.getServerPort()+ path + "/";%>
查询请假记录
if (recordsPage == null) {
request.getRequestDispatcher("LeaveRecordsServlet?action=list").forward(request, response);return;
}
String[] leaveRecordsInfo= { "编号", "姓名", "请假时间", "请假原因", "操作"};
request.setAttribute("leaveRecordsInfo", leaveRecordsInfo);%>
请假记录列表
${leaveRecordsInfo}
style="background-color:#9CD1F3;">
${leaveRecordsList.id}${leaveRecordsList.name}${leaveRecordsList.leaveTime}${leaveRecordsList.reason}
删除
首页
上一页
第${page.pageIndex }页/共${page.totalPageCount }页
下一页
末页
当前页
${pageNum}
.
共${page.totalCount}条. 每页显示${page.pageSize}条
删除的js：delete.js
functiondel(id) {var dele = confirm("确定要删除吗？");if (dele == true) {
$.ajax({"url" : "LeaveRecordsServlet?action=delete","type" : "post","data" : "opt=" +id,"dataType" : "text","success": success,"error": error,
});//删除成功回调函数
functionsuccess(data) {if (data == "true") {
alert("删除成功！");
location.href="LeaveRecordsServlet?action=list";
}else{
alert("删除失败！");
}
}//请求失败回调函数
functionerror(date) {
alert("请求失败!");
}
}
}
添加JSP:insert.jsp
String basePath= request.getScheme() + "://" + request.getServerName() + ":" +request.getServerPort()+ path + "/";%>
My JSP 'insert.jsp' starting page
添加请假记录
| 姓名: | |
| 请假时间: | 格式要求：yyyy-mm-dd |
| 请假原因： | |
添加的非空验证的js：insert.js
//非空验证
functioncheck() {var name = $("#name").val();var leaveTime = $("#leaveTime").val();var context=$("context").val();var reg = /^([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8])))$/;if (name == "" || leaveTime=="" || context=="") {
alert("信息未填写完整，请完善！");return false;
}if(reg.test(leaveTime)==false){
alert("日期格式有误，请重新填写！");return false;
}
}
