该教室预约系统的环境是JDK1.8,mysql8.0的版本,该系统主要是完成对教室的预约功能,其中在预约教室的时候,我们需要填写教室号,预约的开始日期和结束日期,如果该教室存在且该时间段空闲则可以预约成功,如果导入了我的数据库,那里默认有一个管理员账户,其账号和密码都是root,管理员主要负责对教室的增删功能,源码和数据库都在github上
这里给出github上的源码和数据库https://github.com/uphe/classroomsystem
网盘链接:https://pan.baidu.com/s/14boqsgHwRahXrmmzta_B9g
提取码:aprl
下面对该系统的各个包进行一个介绍,首先说下model层的包,该包下的内容对应我们数据库中的用户表和教室表
然后是我们的dao层的包,该包完成了我们数据的增删改查操作
然后是我们的util包,该包完成了数据库的连接与关闭以及对字符串和数字的处理
然后是我们的user包,该包完成了用户登录与注册时前后端数据的联系
然后时我们的room包,该报完成的时我们进行增删改查操作的时候,通过后台数据做出相应的响应
最后就是我们的web了,这里主要是完成页面的显示以及人机的交互
下面演示一下运行结果,首先是登录页面
然后是注册页面
然后是管理员页面
最后是用户页面,这里预约了一个520号教室
这里给出class_room_system.sql数据
-- MySQL dump 10.13 Distrib 8.0.17, for Win64 (x86_64)
--
-- Host: localhost Database: class_room_system
-- ------------------------------------------------------
-- Server version 8.0.17
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `room`
--
DROP TABLE IF EXISTS `room`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `room` (
`roomID` int(255) NOT NULL AUTO_INCREMENT COMMENT '教室号自增',
`roomName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`roomEven` varchar(255) DEFAULT NULL COMMENT '申请教室原因',
`roomStart` bigint(255) DEFAULT NULL COMMENT '申请教室的开始时间',
`roomEnd` bigint(255) DEFAULT NULL COMMENT '申请教室的结束时间',
PRIMARY KEY (`roomID`)
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `room`
--
LOCK TABLES `room` WRITE;
/*!40000 ALTER TABLE `room` DISABLE KEYS */;
INSERT INTO `room` VALUES (52,'1314',NULL,0,0),(53,'520',NULL,0,0);
/*!40000 ALTER TABLE `room` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `user`
--
DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user` (
`userID` int(10) NOT NULL AUTO_INCREMENT COMMENT '用户id',
`userName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
`userPwd` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户密码',
`userPower` int(255) NOT NULL COMMENT '用户权限',
PRIMARY KEY (`userID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `user`
--
LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES (1,'root','root',0),(2,'123','123',1);
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2019-11-28 18:51:37
其中有两个JavaBean
package com.model;
public class User {
private int userID;
private String userName;
private String userPwd;
private int userPower;
public int getUserID() {
return userID;
}
public void setUserID(int userID) {
this.userID = userID;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserPwd() {
return userPwd;
}
public void setUserPwd(String userPwd) {
this.userPwd = userPwd;
}
public int getUserPower() {
return userPower;
}
public void setUserPower(int userPower) {
this.userPower = userPower;
}
}
package com.model;
public class Room {
private int roomID;
private String roomName;
private String roomEven;
private long roomStart;
private long roomEnd;
public int getRoomID() {
return roomID;
}
public void setRoomID(int roomID) {
this.roomID = roomID;
}
public String getRoomName() {
return roomName;
}
public void setRoomName(String roomName) {
this.roomName = roomName;
}
public String getRoomEven() {
return roomEven;
}
public void setRoomEven(String roomEven) {
this.roomEven = roomEven;
}
public long getRoomStart() {
return roomStart;
}
public void setRoomStart(long roomStart) {
this.roomStart = roomStart;
}
public long getRoomEnd() {
return roomEnd;
}
public void setRoomEnd(long roomEnd) {
this.roomEnd = roomEnd;
}
}
这里简单的介绍下room的Dao实现,其中包括了CRUD的功能
package com.dao;
import com.util.CloseUtil;
import com.util.DbUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class RoomInsert {
public int insert(String roomName,String message,long startTime,long endTime) {
DbUtil dbUtil = new DbUtil();
Connection conn = null;
try {
conn = dbUtil.getConn();
String sql = "insert into room values(null,?,?,?,?)";
PreparedStatement pst = (PreparedStatement) conn.prepareStatement(sql);
pst.setString(1, roomName);
pst.setString(2, message);
pst.setLong(3, startTime);
pst.setLong(4, endTime);
return pst.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
new CloseUtil().close(conn);
}
return 0;
}
}
package com.dao;
import com.util.CloseUtil;
import com.util.DbUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class RoomDelete {
public int delete(String roomName) {
DbUtil dbUtil = new DbUtil();
Connection conn = null;
try {
conn = dbUtil.getConn();
String sql = "delete from room where roomName=?";
PreparedStatement pst = (PreparedStatement) conn.prepareStatement(sql);
pst.setString(1, roomName);
return pst.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
new CloseUtil().close(conn);
}
return 0;
}
public int deleteNull(String roomName) {
DbUtil dbUtil = new DbUtil();
Connection conn = null;
try {
conn = dbUtil.getConn();
String sql = "delete from room where roomStart=0 and roomName=? ";
PreparedStatement pst = (PreparedStatement) conn.prepareStatement(sql);
pst.setString(1, roomName);
return pst.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
new CloseUtil().close(conn);
}
return 0;
}
}
package com.dao;
import com.util.CloseUtil;
import com.util.DbUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class RoomUpdate {
public int update(String roomName,long startTime) {
DbUtil dbUtil = new DbUtil();
Connection conn = null;
try {
conn = dbUtil.getConn();
String sql = "update room set roomEven = null,roomStart = 0 , roomEnd = 0 where roomName = ? and roomStart = ?";
PreparedStatement pst = (PreparedStatement) conn.prepareStatement(sql);
pst.setString(1, roomName);
pst.setLong(2, startTime);
return pst.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
new CloseUtil().close(conn);
}
return 0;
}
}
package com.dao;
import com.model.Room;
import com.model.User;
import com.util.CloseUtil;
import com.util.DbUtil;
import com.util.StringUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class RoomSelect {
public List<Room> selectRoomName(String roomName) {
DbUtil dbUtil = new DbUtil();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
User user = null;
List<Room> list = new ArrayList<>();
try {
conn = dbUtil.getConn();
//创建Sql命令
String sql = "select * from room where roomName = ?";
//创建Sql命令对象
ps = conn.prepareStatement(sql);
//给占位符赋值
ps.setString(1,roomName);
rs = ps.executeQuery();
while (rs.next()) {
Room room = new Room();
room.setRoomID(rs.getInt("roomID"));
room.setRoomName(rs.getString("RoomName"));
room.setRoomEven(rs.getString("roomEven"));
room.setRoomStart(rs.getLong("roomStart"));
room.setRoomEnd(rs.getLong("roomEnd"));
list.add(room);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
new CloseUtil().close(conn);
}
return list;
}
public List<Room> selectAll() {
DbUtil dbUtil = new DbUtil();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
User user = null;
List<Room> list = new ArrayList<>();
try {
conn = dbUtil.getConn();
//创建Sql命令
String sql = "select * from room";
//创建Sql命令对象
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
Room room = new Room();
room.setRoomID(rs.getInt("roomID"));
room.setRoomName(rs.getString("RoomName"));
room.setRoomEven(rs.getString("roomEven"));
room.setRoomStart(rs.getLong("roomStart"));
room.setRoomEnd(rs.getLong("roomEnd"));
list.add(room);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
new CloseUtil().close(conn);
}
return list;
}
public List<Room> selectNull() {
DbUtil dbUtil = new DbUtil();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
User user = null;
List<Room> list = new ArrayList<>();
try {
conn = dbUtil.getConn();
//创建Sql命令
String sql = "select * from room where roomStart = 0 and roomEnd = 0;";
//创建Sql命令对象
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
Room room = new Room();
room.setRoomID(rs.getInt("roomID"));
room.setRoomName(rs.getString("RoomName"));
room.setRoomEven(rs.getString("roomEven"));
room.setRoomStart(rs.getLong("roomStart"));
room.setRoomEnd(rs.getLong("roomEnd"));
list.add(room);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
new CloseUtil().close(conn);
}
return list;
}
public List<Room> selectNotNull() {
DbUtil dbUtil = new DbUtil();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
User user = null;
List<Room> list = new ArrayList<>();
try {
conn = dbUtil.getConn();
//创建Sql命令
String sql = "select * from room where roomStart != 0 and roomEnd != 0;";
//创建Sql命令对象
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
Room room = new Room();
room.setRoomID(rs.getInt("roomID"));
room.setRoomName(rs.getString("RoomName"));
room.setRoomEven(rs.getString("roomEven"));
room.setRoomStart(rs.getLong("roomStart"));
room.setRoomEnd(rs.getLong("roomEnd"));
list.add(room);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
new CloseUtil().close(conn);
}
return list;
}
}
在页面展示用到的是JSP,这里给出一个登录首页的jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>登录页面</title>
<link rel="stylesheet" href="mainStyle.css" type="text/css" media="all" />
</head>
<body>
<div class="container">
<h1>教室管理系统</h1>
<div class="signIn">
<form action="login" method="post">
<input type="text" class="userName" name="userName" placeholder="账号" />
<input type="password" class="userPwd pass" name="userPwd" placeholder="密码" /><br>
<input type="submit" name="login" value="登录" />
<input type="submit" name="register" value="注册" />
</form>
</div>
</div>
</body>
</html>