一、系统介绍
1.开发环境
开发工具:IDEA2018.2
JDK版本:jdk1.8
Mysql版本:8.0.13
2.技术选型
1.后端:使用Java+Servlet进行开发,使用过滤器来验证用户是否登录,使用MVC进行分层。
2.数据库:使用JDBC连接数据库,使用getResourceAsStream获取数据库配置文件的信息。
3.前端:使用Bootstrap作为前端框架来开发,使用到jstl来遍历数据。jsp来展示页面。
3.系统功能
1.学生
1.登录系统
2.缺勤记录
缺勤记录查询
3.修改密码
4.退出系统
2.宿舍管理员
1.登录系统
2.学生查看
查看学生信息
3.缺勤记录管理
缺勤记录的增删改查。
4.修改密码
5.退出系统
3.系统管理员
1.登录系统
2.宿舍管理员管理
宿舍管理员信息的增删改查。
3.学生信息管理
学生信息的增删改查。
4.宿舍楼信息管理
宿舍楼信息的增删改查。
5.缺勤记录管理
缺勤记录删除
6.修改密码
7.退出系统
4.数据库
/* Navicat Premium Data Transfer Source Server : Mysql Source Server Type : MySQL Source Server Version : 80013 Source Host : localhost:3306 Source Schema : jsp_servlet_drom Target Server Type : MySQL Target Server Version : 80013 File Encoding : 65001 Date: 22/07/2021 20:46:38 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_admin -- ---------------------------- DROP TABLE IF EXISTS `t_admin`; CREATE TABLE `t_admin` ( `adminId` int(11) NOT NULL AUTO_INCREMENT, `userName` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `password` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `sex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `tel` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`adminId`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_admin -- ---------------------------- INSERT INTO `t_admin` VALUES (1, 'admin', 'admin', '管理员', '男', '1828888888'); -- ---------------------------- -- Table structure for t_dorm -- ---------------------------- DROP TABLE IF EXISTS `t_dorm`; CREATE TABLE `t_dorm` ( `dormId` int(11) NOT NULL AUTO_INCREMENT, `dormBuildId` int(11) NULL DEFAULT NULL, `dormName` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `dormType` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `dormNumber` int(11) NULL DEFAULT NULL, `dormTel` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`dormId`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_dorm -- ---------------------------- INSERT INTO `t_dorm` VALUES (1, 1, '220', '男', 4, '110'); -- ---------------------------- -- Table structure for t_dormbuild -- ---------------------------- DROP TABLE IF EXISTS `t_dormbuild`; CREATE TABLE `t_dormbuild` ( `dormBuildId` int(11) NOT NULL AUTO_INCREMENT, `dormBuildName` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `dormBuildDetail` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`dormBuildId`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_dormbuild -- ---------------------------- INSERT INTO `t_dormbuild` VALUES (1, '1栋', '一栋信息介绍'); INSERT INTO `t_dormbuild` VALUES (2, '2栋', '二栋信息介绍'); INSERT INTO `t_dormbuild` VALUES (3, '3栋', '三栋信息介绍'); INSERT INTO `t_dormbuild` VALUES (4, '4栋', '四栋信息介绍'); INSERT INTO `t_dormbuild` VALUES (5, '5栋', '五栋信息介绍'); -- ---------------------------- -- Table structure for t_dormmanager -- ---------------------------- DROP TABLE IF EXISTS `t_dormmanager`; CREATE TABLE `t_dormmanager` ( `dormManId` int(11) NOT NULL AUTO_INCREMENT, `userName` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `password` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `dormBuildId` int(11) NULL DEFAULT NULL, `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `sex` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `tel` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`dormManId`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_dormmanager -- ---------------------------- INSERT INTO `t_dormmanager` VALUES (1, 'dromadmin', '123456', 4, '宿舍管理员', '男', '18212346589'); -- ---------------------------- -- Table structure for t_record -- ---------------------------- DROP TABLE IF EXISTS `t_record`; CREATE TABLE `t_record` ( `recordId` int(11) NOT NULL AUTO_INCREMENT, `studentNumber` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `studentName` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `dormBuildId` int(11) NULL DEFAULT NULL, `dormName` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `date` date NULL DEFAULT NULL, `detail` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`recordId`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_record -- ---------------------------- INSERT INTO `t_record` VALUES (1, '001', '李四', 4, '120', '2014-01-01', '回家'); -- ---------------------------- -- Table structure for t_student -- ---------------------------- DROP TABLE IF EXISTS `t_student`; CREATE TABLE `t_student` ( `studentId` int(11) NOT NULL AUTO_INCREMENT, `stuNum` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `password` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `dormBuildId` int(11) NULL DEFAULT NULL, `dormName` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `sex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `tel` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`studentId`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 32 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_student -- ---------------------------- INSERT INTO `t_student` VALUES (1, '001', '123456', '李四', 4, '120', '男', '123456'); INSERT INTO `t_student` VALUES (2, '002', '123456', '王五', 5, '201', '男', '123456'); SET FOREIGN_KEY_CHECKS = 1;
5.工程截图
二、系统展示
1.登录界面
2.学生-主页面
3.学生-缺勤记录
4.学生-修改密码
5.宿舍管理员-主页面
6.宿舍管理员-学生查看
7.宿舍管理员-缺勤记录
8.宿舍管理员-修改密码
9.系统管理员-主页面
10.系统管理员-宿舍管理员管理
11.系统管理员-学生管理
12.系统管理员-宿舍楼管理
13.系统管理员-缺勤记录
14.系统管理员-修改密码
三、部分代码
DormBuildDao
package com.lero.dao; import com.lero.model.DormBuild; import com.lero.model.DormManager; import com.lero.model.PageBean; import com.lero.util.StringUtil; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; public class DormBuildDao { public static String dormBuildName(Connection con, int dormBuildId) throws Exception { String sql = "select * from t_dormBuild where dormBuildId=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setInt(1, dormBuildId); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { return rs.getString("dormBuildName"); } return null; } public List<DormBuild> dormBuildList(Connection con, PageBean pageBean, DormBuild s_dormBuild) throws Exception { List<DormBuild> dormBuildList = new ArrayList<DormBuild>(); StringBuffer sb = new StringBuffer("select * from t_dormBuild t1"); if (StringUtil.isNotEmpty(s_dormBuild.getDormBuildName())) { sb.append(" where t1.dormBuildName like '%" + s_dormBuild.getDormBuildName() + "%'"); } if (pageBean != null) { sb.append(" limit " + pageBean.getStart() + "," + pageBean.getPageSize()); } PreparedStatement pstmt = con.prepareStatement(sb.toString()); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { DormBuild dormBuild = new DormBuild(); dormBuild.setDormBuildId(rs.getInt("dormBuildId")); dormBuild.setDormBuildName(rs.getString("dormBuildName")); dormBuild.setDetail(rs.getString("dormBuildDetail")); dormBuildList.add(dormBuild); } return dormBuildList; } public int dormBuildCount(Connection con, DormBuild s_dormBuild) throws Exception { StringBuffer sb = new StringBuffer("select count(*) as total from t_dormBuild t1"); if (StringUtil.isNotEmpty(s_dormBuild.getDormBuildName())) { sb.append(" where t1.dormBuildName like '%" + s_dormBuild.getDormBuildName() + "%'"); } PreparedStatement pstmt = con.prepareStatement(sb.toString()); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { return rs.getInt("total"); } else { return 0; } } public DormBuild dormBuildShow(Connection con, String dormBuildId) throws Exception { String sql = "select * from t_dormBuild t1 where t1.dormBuildId=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1, dormBuildId); ResultSet rs = pstmt.executeQuery(); DormBuild dormBuild = new DormBuild(); if (rs.next()) { dormBuild.setDormBuildId(rs.getInt("dormBuildId")); dormBuild.setDormBuildName(rs.getString("dormBuildName")); dormBuild.setDetail(rs.getString("dormBuildDetail")); } return dormBuild; } public int dormBuildAdd(Connection con, DormBuild dormBuild) throws Exception { String sql = "insert into t_dormBuild values(null,?,?)"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1, dormBuild.getDormBuildName()); pstmt.setString(2, dormBuild.getDetail()); return pstmt.executeUpdate(); } public int dormBuildDelete(Connection con, String dormBuildId) throws Exception { String sql = "delete from t_dormBuild where dormBuildId=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1, dormBuildId); return pstmt.executeUpdate(); } public int dormBuildUpdate(Connection con, DormBuild dormBuild) throws Exception { String sql = "update t_dormBuild set dormBuildName=?,dormBuildDetail=? where dormBuildId=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1, dormBuild.getDormBuildName()); pstmt.setString(2, dormBuild.getDetail()); pstmt.setInt(3, dormBuild.getDormBuildId()); return pstmt.executeUpdate(); } public boolean existManOrDormWithId(Connection con, String dormBuildId) throws Exception { boolean isExist = false; // String sql="select * from t_dormBuild,t_dormManager,t_connection where dormManId=managerId and dormBuildId=buildId and dormBuildId=?"; String sql = "select *from t_dormManager where dormBuildId=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1, dormBuildId); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { isExist = true; } else { isExist = false; } String sql1 = "select * from t_dormBuild t1,t_dorm t2 where t1.dormBuildId=t2.dormBuildId and t1.dormBuildId=?"; PreparedStatement p = con.prepareStatement(sql1); p.setString(1, dormBuildId); ResultSet r = pstmt.executeQuery(); if (r.next()) { return isExist; } else { return false; } } public List<DormManager> dormManWithoutBuild(Connection con) throws Exception { List<DormManager> dormManagerList = new ArrayList<DormManager>(); String sql = "SELECT * FROM t_dormManager WHERE dormBuildId IS NULL OR dormBuildId=0"; PreparedStatement pstmt = con.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { DormManager dormManager = new DormManager(); dormManager.setDormBuildId(rs.getInt("dormBuildId")); dormManager.setDormManagerId(rs.getInt("dormManId")); dormManager.setName(rs.getString("name")); dormManager.setUserName(rs.getString("userName")); dormManager.setSex(rs.getString("sex")); dormManager.setTel(rs.getString("tel")); dormManagerList.add(dormManager); } return dormManagerList; } public List<DormManager> dormManWithBuildId(Connection con, String dormBuildId) throws Exception { List<DormManager> dormManagerList = new ArrayList<DormManager>(); String sql = "select *from t_dormManager where dormBuildId=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1, dormBuildId); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { DormManager dormManager = new DormManager(); dormManager.setDormBuildId(rs.getInt("dormBuildId")); dormManager.setDormManagerId(rs.getInt("dormManId")); dormManager.setName(rs.getString("name")); dormManager.setUserName(rs.getString("userName")); dormManager.setSex(rs.getString("sex")); dormManager.setTel(rs.getString("tel")); dormManagerList.add(dormManager); } return dormManagerList; } public int managerUpdateWithId(Connection con, String dormManagerId, String dormBuildId) throws Exception { String sql = "update t_dormManager set dormBuildId=? where dormManId=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1, dormBuildId); pstmt.setString(2, dormManagerId); return pstmt.executeUpdate(); } }
DormManagerDao
package com.lero.dao; import com.lero.model.DormManager; import com.lero.model.PageBean; import com.lero.util.StringUtil; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; public class DormManagerDao { public List<DormManager> dormManagerList(Connection con, PageBean pageBean, DormManager s_dormManager) throws Exception { List<DormManager> dormManagerList = new ArrayList<DormManager>(); StringBuffer sb = new StringBuffer("SELECT * FROM t_dormManager t1 ORDER BY t1.userName"); if (StringUtil.isNotEmpty(s_dormManager.getName())) { sb.append(" where t1.name like '%" + s_dormManager.getName() + "%'"); } else if (StringUtil.isNotEmpty(s_dormManager.getUserName())) { sb.append(" where t1.userName like '%" + s_dormManager.getUserName() + "%'"); } if (pageBean != null) { sb.append(" limit " + pageBean.getStart() + "," + pageBean.getPageSize()); } PreparedStatement pstmt = con.prepareStatement(sb.toString()); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { DormManager dormManager = new DormManager(); dormManager.setDormManagerId(rs.getInt("dormManId")); int dormBuildId = rs.getInt("dormBuildId"); dormManager.setDormBuildId(dormBuildId); dormManager.setDormBuildName(DormBuildDao.dormBuildName(con, dormBuildId)); dormManager.setName(rs.getString("name")); dormManager.setSex(rs.getString("sex")); dormManager.setUserName(rs.getString("userName")); dormManager.setTel(rs.getString("tel")); dormManager.setPassword(rs.getString("password")); dormManagerList.add(dormManager); } return dormManagerList; } public int dormManagerCount(Connection con, DormManager s_dormManager) throws Exception { StringBuffer sb = new StringBuffer("select count(*) as total from t_dormManager t1"); if (StringUtil.isNotEmpty(s_dormManager.getName())) { sb.append(" where t1.name like '%" + s_dormManager.getName() + "%'"); } else if (StringUtil.isNotEmpty(s_dormManager.getUserName())) { sb.append(" where t1.userName like '%" + s_dormManager.getUserName() + "%'"); } PreparedStatement pstmt = con.prepareStatement(sb.toString()); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { return rs.getInt("total"); } else { return 0; } } public DormManager dormManagerShow(Connection con, String dormManagerId) throws Exception { String sql = "select * from t_dormManager t1 where t1.dormManId=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1, dormManagerId); ResultSet rs = pstmt.executeQuery(); DormManager dormManager = new DormManager(); if (rs.next()) { dormManager.setDormManagerId(rs.getInt("dormManId")); dormManager.setDormBuildId(rs.getInt("dormBuildId")); dormManager.setName(rs.getString("name")); dormManager.setSex(rs.getString("sex")); dormManager.setUserName(rs.getString("userName")); dormManager.setTel(rs.getString("tel")); dormManager.setPassword(rs.getString("password")); } return dormManager; } public int dormManagerAdd(Connection con, DormManager dormManager) throws Exception { String sql = "insert into t_dormManager values(null,?,?,null,?,?,?)"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1, dormManager.getUserName()); pstmt.setString(2, dormManager.getPassword()); pstmt.setString(3, dormManager.getName()); pstmt.setString(4, dormManager.getSex()); pstmt.setString(5, dormManager.getTel()); return pstmt.executeUpdate(); } public int dormManagerDelete(Connection con, String dormManagerId) throws Exception { String sql = "delete from t_dormManager where dormManId=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1, dormManagerId); return pstmt.executeUpdate(); } public int dormManagerUpdate(Connection con, DormManager dormManager) throws Exception { String sql = "update t_dormManager set userName=?,password=?,name=?,sex=?,tel=? where dormManId=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1, dormManager.getUserName()); pstmt.setString(2, dormManager.getPassword()); pstmt.setString(3, dormManager.getName()); pstmt.setString(4, dormManager.getSex()); pstmt.setString(5, dormManager.getTel()); pstmt.setInt(6, dormManager.getDormManagerId()); return pstmt.executeUpdate(); } public boolean haveManagerByUser(Connection con, String userName) throws Exception { String sql = "select * from t_dormmanager t1 where t1.userName=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1, userName); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { return true; } return false; } }