运行地址:http://localhost:8080/dorm/login/login.do
1.基于ssm+mysql 或者oracle的大学宿舍管理系统,三种角色分别是管理员、宿舍管理员和学生。
2.功能包括:院系管理,专业管理,年级管理,班级管理,学生管理,
宿舍楼管理员管理,宿舍楼管理,宿舍管理,床位管理,学生入住登记,学生退房管理
效果截图:
数据库设计:
/*
Navicat MySQL Data Transfer
Source Server : local
Source Server Version : 50709
Source Host : localhost:3306
Source Database : sushe
Target Server Type : MYSQL
Target Server Version : 50709
File Encoding : 65001
Date: 2019-12-24 14:37:36
*/
SET FOREIGN_KEY_CHECKS=0;
– Table structure for admin
DROP TABLE IF EXISTS admin
;
CREATE TABLE admin
(
ID
int(11) NOT NULL COMMENT ‘ID’,
USERNAME
varchar(255) DEFAULT NULL COMMENT ‘账号用户名’,
PASSWORD
varchar(255) DEFAULT NULL COMMENT ‘账号密码’,
NICKNAME
varchar(255) DEFAULT NULL COMMENT ‘用户名’,
PRIMARY KEY (ID
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘系统管理员’;
– Records of admin
INSERT INTO admin
VALUES (‘1’, ‘admin’, ‘admin’, ‘系统管理员’);
– Table structure for announcement
DROP TABLE IF EXISTS announcement
;
CREATE TABLE announcement
(
announcement_id
varchar(64) NOT NULL,
title
varchar(64) DEFAULT NULL,
content
varchar(3000) DEFAULT NULL,
create_time
date DEFAULT NULL,
PRIMARY KEY (announcement_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
– Records of announcement
INSERT INTO announcement
VALUES (‘d88f5c8f’, ‘1’, ‘111111sdfsdf’, ‘2019-11-22’);
– Table structure for apartment
DROP TABLE IF EXISTS apartment
;
CREATE TABLE apartment
(
APARTMENT_ID
varchar(255) DEFAULT NULL,
APARTMENT_NAME
varchar(255) DEFAULT NULL,
SEX
varchar(255) DEFAULT NULL,
MANAGER_ID
varchar(255) DEFAULT NULL,
TOTAL_FLOOR
varchar(255) DEFAULT NULL,
TOTAL_PEOPLE
varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
– Records of apartment
INSERT INTO apartment
VALUES (‘6189e007’, ‘1’, ‘1’, ‘1’, ‘1’, ‘1’);
– Table structure for bed
DROP TABLE IF EXISTS bed
;
CREATE TABLE bed
(
ID
varchar(128) DEFAULT NULL COMMENT ‘id’,
BEDNO
varchar(128) DEFAULT NULL COMMENT ‘床位编号,例如a,b,c,d,e,f’,
DOMITORYID
varchar(128) DEFAULT NULL COMMENT ‘所属宿舍id’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘床位信息表’;
– Records of bed
INSERT INTO bed
VALUES (‘d4514fc1134342ce9116a6ccd68ecbe2’, ‘a’, ‘60a61abb1d1b415683b727283b7e8068’);
INSERT INTO bed
VALUES (‘6e331ea97268426ea393f1266e10de27’, ‘b’, ‘60a61abb1d1b415683b727283b7e8068’);
INSERT INTO bed
VALUES (‘a17f11441f434a57aa2f228f2de91fb6’, ‘c’, ‘60a61abb1d1b415683b727283b7e8068’);
INSERT INTO bed
VALUES (‘33e48094675d40349b234250a9bf83eb’, ‘d’, ‘60a61abb1d1b415683b727283b7e8068’);
INSERT INTO bed
VALUES (‘66fbb3ce1be94501b3774a225c037793’, ‘a’, ‘2f236dbae1dd4e6a8613738faac362b2’);
INSERT INTO bed
VALUES (‘68739442d27a4bd1a64766de3d270ce2’, ‘b’, ‘2f236dbae1dd4e6a8613738faac362b2’);
INSERT INTO bed
VALUES (‘2f0d6caa3a70473a906bb9e34d270442’, ‘c’, ‘2f236dbae1dd4e6a8613738faac362b2’);
INSERT INTO bed
VALUES (‘2c2d85865bef44a0bc98342db4fe7a9f’, ‘d’, ‘2f236dbae1dd4e6a8613738faac362b2’);
INSERT INTO bed
VALUES (‘adaba6db736b4ff2a410e16ec49c9399’, ‘a’, ‘fec99305ebd64a72b7296b2bc5232c31’);
INSERT INTO bed
VALUES (‘cd975f8bb61f44b2a85c4b2ecde54e55’, ‘b’, ‘fec99305ebd64a72b7296b2bc5232c31’);
INSERT INTO bed
VALUES (‘ae75523219504dc78605127a84967fae’, ‘a’, ‘4c0e6cf1020a4605a008f7a542dab71e’);
INSERT INTO bed
VALUES (‘513a7ee3202e426d8bcea4ed06a15116’, ‘b’, ‘4c0e6cf1020a4605a008f7a542dab71e’);
INSERT INTO bed
VALUES (‘cff29ab4975049c68cec720d706e541d’, ‘a’, ‘89ac94bba92e44599bc9903b0a81429a’);
INSERT INTO bed
VALUES (‘03bc4631e8fc43bb8f5cbaa4d3a9ff5f’, ‘b’, ‘89ac94bba92e44599bc9903b0a81429a’);
INSERT INTO bed
VALUES (‘7fb63a00cb014590abd0ca94ceabe62d’, ‘c’, ‘89ac94bba92e44599bc9903b0a81429a’);
INSERT INTO bed
VALUES (‘8fd7004f208e468894b98eb537f0cd75’, ‘d’, ‘89ac94bba92e44599bc9903b0a81429a’);
INSERT INTO bed
VALUES (‘aca5c29752b44e83b8dbfde32c5868cd’, ‘a’, ‘645e9af293334a2490d1ae8b3ff564b5’);
INSERT INTO bed
VALUES (‘c8d60c357533488fa223d747edf7a87f’, ‘b’, ‘645e9af293334a2490d1ae8b3ff564b5’);
INSERT INTO bed
VALUES (‘bf5f05188cf8411a899acfc5f493f477’, ‘c’, ‘645e9af293334a2490d1ae8b3ff564b5’);
INSERT INTO bed
VALUES (‘796776d374a34c9e9ccecd2b6f537cef’, ‘d’, ‘645e9af293334a2490d1ae8b3ff564b5’);
INSERT INTO bed
VALUES (‘3cc4a41477624304a468d279d6db6300’, ‘e’, ‘645e9af293334a2490d1ae8b3ff564b5’);
INSERT INTO bed
VALUES (‘bd499484799a4b3fbc4df94189c89395’, ‘f’, ‘645e9af293334a2490d1ae8b3ff564b5’);
– Table structure for bedroom
DROP TABLE IF EXISTS bedroom
;
CREATE TABLE bedroom
(
BEDROOM_ID
varchar(255) DEFAULT NULL,
BEDROOM_NAME
varchar(255) DEFAULT NULL,
APARTMENT_ID
varchar(255) DEFAULT NULL,
STATUS
varchar(255) DEFAULT NULL,
TOTAL_BED
varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
– Records of bedroom
INSERT INTO bedroom
VALUES (‘372693ad’, ‘11’, ‘6189e007’, ‘N’, ‘11’);
– Table structure for building
DROP TABLE IF EXISTS building
;
CREATE TABLE building
(
ID
varchar(255) DEFAULT NULL COMMENT ‘id’,
BUILDINGNO
varchar(255) DEFAULT NULL COMMENT ‘宿舍楼号’,
POSITION
varchar(255) DEFAULT NULL COMMENT ‘位置’,
LAYERNUMBER
varchar(255) DEFAULT NULL COMMENT ‘宿舍层数’,
ROOMNUMBER
varchar(255) DEFAULT NULL COMMENT ‘每层房间数’,
MANAGEID
varchar(255) DEFAULT NULL COMMENT ‘管理员id’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘宿舍楼信息表’;
– Records of building
INSERT INTO building
VALUES (‘8167ded8935d4fb8881b4e85f4013f70’, ‘1号宿舍楼’, ‘东门附近’, ‘7’, ‘30’, null);
INSERT INTO building
VALUES (‘73c6770a6deb40089df9a0e828a09592’, ‘2号宿舍楼’, ‘西门附近’, ‘7’, ‘30’, null);
INSERT INTO building
VALUES (‘caebc6fa46e5474385f08b5669bb73dd’, ‘4号宿舍楼’, ‘体育馆东门’, ‘5’, ‘20’, null);
INSERT INTO building
VALUES (‘c92ac8b643fb4efcb63daa437fe834e6’, ‘3号宿舍楼’, ‘教学楼附近’, ‘7’, ‘30’, null);
– Table structure for classes
DROP TABLE IF EXISTS classes
;
CREATE TABLE classes
(
CLASS_ID
varchar(255) DEFAULT NULL,
CLASS_NAME
varchar(255) DEFAULT NULL,
COACH
varchar(255) DEFAULT NULL,
COACH_CALL
varchar(255) DEFAULT NULL,
COLLEGE_ID
varchar(255) DEFAULT NULL,
GRADE
varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
– Records of classes
– Table structure for college
DROP TABLE IF EXISTS college
;
CREATE TABLE college
(
ID
varchar(255) DEFAULT NULL COMMENT ‘id’,
NAME
varchar(255) DEFAULT NULL COMMENT ‘学院信息名’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘学院信息表’;
– Records of college
INSERT INTO college
VALUES (‘92c66bdd76ad428e8a655dd54e470552’, ‘计算机学院’);
INSERT INTO college
VALUES (‘925fb844915c4c15bf6a1b0ed77110ab’, ‘商学院’);
INSERT INTO college
VALUES (‘3553bc29568e4208ac1b6fa32fa720e2’, ‘英语系’);
INSERT INTO college
VALUES (‘89de2679fb19419785bf7671fb63e2dc’, ‘自动化’);
INSERT INTO college
VALUES (‘1ee56c3f9bef49f896896ec0c9639f28’, ‘化工学院’);
– Table structure for domitory
DROP TABLE IF EXISTS domitory
;
CREATE TABLE domitory
(
ID
varchar(255) DEFAULT NULL COMMENT ‘id’,
DOMITORYNO
varchar(255) DEFAULT NULL COMMENT ‘宿舍号’,
TEL
varchar(255) DEFAULT NULL COMMENT ‘宿舍电话’,
TYPE
int(11) DEFAULT NULL COMMENT ‘宿舍类型 1:单人间 2 :双人间 4:四人间 6:六人间’,
BUILDINGID
varchar(255) DEFAULT NULL COMMENT ‘所属的宿舍楼ID’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘宿舍信息表’;
– Records of domitory
INSERT INTO domitory
VALUES (‘2f236dbae1dd4e6a8613738faac362b2’, ‘1102’, ‘025-4561268’, ‘4’, ‘8167ded8935d4fb8881b4e85f4013f70’);
INSERT INTO domitory
VALUES (‘fec99305ebd64a72b7296b2bc5232c31’, ‘1305’, ‘025-124564654’, ‘2’, ‘8167ded8935d4fb8881b4e85f4013f70’);
INSERT INTO domitory
VALUES (‘4c0e6cf1020a4605a008f7a542dab71e’, ‘4306’, ‘025-486324’, ‘2’, ‘caebc6fa46e5474385f08b5669bb73dd’);
INSERT INTO domitory
VALUES (‘60a61abb1d1b415683b727283b7e8068’, ‘3301’, ‘025-1465235’, ‘4’, ‘c92ac8b643fb4efcb63daa437fe834e6’);
INSERT INTO domitory
VALUES (‘89ac94bba92e44599bc9903b0a81429a’, ‘1805’, ‘025-1455658’, ‘4’, ‘8167ded8935d4fb8881b4e85f4013f70’);
INSERT INTO domitory
VALUES (‘645e9af293334a2490d1ae8b3ff564b5’, ‘1806’, ‘025-7676232’, ‘6’, ‘8167ded8935d4fb8881b4e85f4013f70’);
– Table structure for grade
DROP TABLE IF EXISTS grade
;
CREATE TABLE grade
(
ID
varchar(255) DEFAULT NULL COMMENT ‘ID’,
NAME
varchar(255) DEFAULT NULL COMMENT ‘年级信息名’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘年级表’;
– Records of grade
INSERT INTO grade
VALUES (‘89c71eed9e1f4b3c898d76c7d25fc181’, ‘大一’);
INSERT INTO grade
VALUES (‘c26a885b80054aa1b2d0ca8f02a31760’, ‘大二’);
INSERT INTO grade
VALUES (‘a45d3256feeb4996b6d3aab91405c7d8’, ‘大三’);
INSERT INTO grade
VALUES (‘c965e0e1acc246c49b2507c543c0f9fd’, ‘大四’);
– Table structure for major
DROP TABLE IF EXISTS major
;
CREATE TABLE major
(
ID
varchar(255) DEFAULT NULL COMMENT ‘ID’,
NAME
varchar(255) DEFAULT NULL COMMENT ‘专业名’,
COLLEGEID
varchar(255) DEFAULT NULL COMMENT ‘所属院系’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘专业’;
– Records of major
INSERT INTO major
VALUES (‘83c77b176e0e42798bcf5f61826b9bc6’, ‘软件工程’, ‘92c66bdd76ad428e8a655dd54e470552’);
INSERT INTO major
VALUES (‘fa619a3334384c81b1d9b8f7aa3eddc3’, ‘信息管理’, ‘92c66bdd76ad428e8a655dd54e470552’);
INSERT INTO major
VALUES (‘b5cc9a47716249499a9f122a920ec13f’, ‘嵌入式’, ‘92c66bdd76ad428e8a655dd54e470552’);
INSERT INTO major
VALUES (‘4a637b76c15f481690f67882f59796bd’, ‘国际贸易’, ‘925fb844915c4c15bf6a1b0ed77110ab’);
– Table structure for manage
DROP TABLE IF EXISTS manage
;
CREATE TABLE manage
(
ID
varchar(255) DEFAULT NULL COMMENT ‘id’,
USERNAME
varchar(255) DEFAULT NULL COMMENT ‘账号用户名’,
PASSWORD
varchar(255) DEFAULT NULL COMMENT ‘账号密码’,
NICKNAME
varchar(255) DEFAULT NULL COMMENT ‘用户名’,
SEX
int(11) DEFAULT NULL COMMENT ‘性别 0:女 1:男’,
TEACHERID
varchar(255) DEFAULT NULL COMMENT ‘教工号号’,
JIGUAN
varchar(255) DEFAULT NULL COMMENT ‘籍贯’,
PHONE
varchar(255) DEFAULT NULL COMMENT ‘手机号’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘宿舍楼管理员’;
– Records of manage
INSERT INTO manage
VALUES (‘137c022b46ee4c01af3194d0e9e20d39’, ‘manage1’, ‘123456’, ‘宿管1’, ‘1’, ‘001’, ‘江苏无锡惠山’, ‘15712266886’);
INSERT INTO manage
VALUES (‘44d23ae06f9a47ac833e3d1a5773b78d’, ‘manage2’, ‘123456’, ‘宿舍2’, ‘0’, ‘002’, ‘江苏无锡’, ‘15479865656’);
INSERT INTO manage
VALUES (‘bb4b36e80bc74ebf8c82b17682b9e4b9’, ‘manage3’, ‘123456’, ‘宿管3’, ‘1’, ‘003’, ‘江苏常州’, ‘15423366554’);
INSERT INTO manage
VALUES (‘768db330474e4d918352b8ba9eeb713e’, ‘manage4’, ‘123456’, ‘宿管4’, ‘1’, ‘004’, ‘北京’, ‘14563244884’);
– Table structure for manager
DROP TABLE IF EXISTS manager
;
CREATE TABLE manager
(
MANAGER_ID
varchar(255) DEFAULT NULL,
MANAGER_NAME
varchar(255) DEFAULT NULL,
MANAGER_PASSWORD
varchar(255) DEFAULT NULL,
MANAGER_CALL
varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
– Records of manager
INSERT INTO manager
VALUES (‘1’, ‘admin’, ‘admin’, null);
– Table structure for mbrel
DROP TABLE IF EXISTS mbrel
;
CREATE TABLE mbrel
(
ID
varchar(128) DEFAULT NULL COMMENT ‘id’,
MANAGEID
varchar(128) DEFAULT NULL COMMENT ‘管理员id’,
BUILDINGID
varchar(128) DEFAULT NULL COMMENT ‘宿舍楼id’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘记录管理员和宿舍楼的关系,多对一个关系,多个管理管理一个宿舍’;
– Records of mbrel
INSERT INTO mbrel
VALUES (‘1aa130cfe2454c65baa7589a5526f138’, ‘137c022b46ee4c01af3194d0e9e20d39’, ‘8167ded8935d4fb8881b4e85f4013f70’);
INSERT INTO mbrel
VALUES (‘7f2c7295f2e54218b2f239cd5a78e368’, ‘44d23ae06f9a47ac833e3d1a5773b78d’, ‘73c6770a6deb40089df9a0e828a09592’);
INSERT INTO mbrel
VALUES (‘7ea833a6dd3e4b81a35637eea1d9dc4b’, ‘bb4b36e80bc74ebf8c82b17682b9e4b9’, ‘c92ac8b643fb4efcb63daa437fe834e6’);
– Table structure for stayrel
DROP TABLE IF EXISTS stayrel
;
CREATE TABLE stayrel
(
ID
varchar(128) DEFAULT NULL COMMENT ‘id’,
STUDENTID
varchar(128) DEFAULT NULL COMMENT ‘学生id’,
BEDID
varchar(128) DEFAULT NULL COMMENT ‘床位id’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘入住关系表,记录学生和床位的关系,一对一关系,一个学生只能入住一个床位’;
– Records of stayrel
– Table structure for student
DROP TABLE IF EXISTS student
;
CREATE TABLE student
(
ID
varchar(255) DEFAULT NULL COMMENT ‘ID’,
USERNAME
varchar(255) DEFAULT NULL COMMENT ‘账号用户名’,
PASSWORD
varchar(255) DEFAULT NULL COMMENT ‘账号密码’,
NICKNAME
varchar(255) DEFAULT NULL COMMENT ‘用户名’,
SEX
int(11) DEFAULT NULL COMMENT ‘性别 0:女 1:男’,
STUDENTID
varchar(255) DEFAULT NULL COMMENT ‘学号’,
JIGUAN
varchar(255) DEFAULT NULL COMMENT ‘籍贯’,
PHONE
varchar(255) DEFAULT NULL COMMENT ‘手机号’,
IFRUZHU
int(11) DEFAULT NULL COMMENT ‘是否入住,0:未入住,1:已入住’,
COLLEGEID
varchar(255) DEFAULT NULL COMMENT ‘学院id’,
MAJORID
varchar(255) DEFAULT NULL COMMENT ‘专业id’,
GRADEID
varchar(255) DEFAULT NULL COMMENT ‘年级id’,
TEAMID
varchar(255) DEFAULT NULL COMMENT ‘班级id’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘学生信息’;
– Records of student
INSERT INTO student
VALUES (‘b41030bf5219433ab006ca23ba88afff’, ‘lisi’, ‘123456’, ‘李四’, ‘1’, ‘1002’, ‘江苏无锡’, ‘14523366558’, ‘0’, ‘92c66bdd76ad428e8a655dd54e470552’, ‘83c77b176e0e42798bcf5f61826b9bc6’, ‘89c71eed9e1f4b3c898d76c7d25fc181’, ‘ca17232c05174130ba7586fc2a189ac5’);
INSERT INTO student
VALUES (‘c4f82ecc0a9b43abb833a95618cd8994’, ‘zhangsan’, ‘123456’, ‘张三’, ‘1’, ‘1001’, ‘江苏南京雨花台’, ‘15754455335’, ‘0’, ‘92c66bdd76ad428e8a655dd54e470552’, ‘83c77b176e0e42798bcf5f61826b9bc6’, ‘c26a885b80054aa1b2d0ca8f02a31760’, ‘a5b05ee6e6c5467fab3ae7c168128d1e’);
INSERT INTO student
VALUES (‘a58bc14bfef54ae9865f2c98b2542499’, ‘wangwu’, ‘123456’, ‘王五’, ‘1’, ‘1003’, ‘江苏苏州’, ‘14526655335’, ‘0’, ‘92c66bdd76ad428e8a655dd54e470552’, ‘83c77b176e0e42798bcf5f61826b9bc6’, ‘89c71eed9e1f4b3c898d76c7d25fc181’, ‘9dd9303620654f75a6efaa28eeaacf32’);
– Table structure for team
DROP TABLE IF EXISTS team
;
CREATE TABLE team
(
ID
varchar(255) DEFAULT NULL COMMENT ‘ID’,
NAME
varchar(255) DEFAULT NULL COMMENT ‘专业名’,
COLLEGEID
varchar(255) DEFAULT NULL COMMENT ‘所属院系’,
MAJORID
varchar(255) DEFAULT NULL COMMENT ‘所属专业’
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘班级信息表’;
– Records of team
INSERT INTO team
VALUES (‘e1601f58048d4cfa884b6889793d42ec’, ‘信管2班’, ‘92c66bdd76ad428e8a655dd54e470552’, ‘fa619a3334384c81b1d9b8f7aa3eddc3’);
INSERT INTO team
VALUES (‘a5b05ee6e6c5467fab3ae7c168128d1e’, ‘软件工程2班’, ‘92c66bdd76ad428e8a655dd54e470552’, ‘83c77b176e0e42798bcf5f61826b9bc6’);
INSERT INTO team
VALUES (‘ca17232c05174130ba7586fc2a189ac5’, ‘软件1班’, ‘92c66bdd76ad428e8a655dd54e470552’, ‘83c77b176e0e42798bcf5f61826b9bc6’);
INSERT INTO team
VALUES (‘fb0f2d79e91e407293e7fb95698f4a5f’, ‘信管1班’, ‘92c66bdd76ad428e8a655dd54e470552’, ‘fa619a3334384c81b1d9b8f7aa3eddc3’);
INSERT INTO team
VALUES (‘9dd9303620654f75a6efaa28eeaacf32’, ‘软件3班’, ‘92c66bdd76ad428e8a655dd54e470552’, ‘83c77b176e0e42798bcf5f61826b9bc6’);
– Table structure for visitor
DROP TABLE IF EXISTS visitor
;
CREATE TABLE visitor
(
VISITOR_ID
varchar(255) DEFAULT NULL,
VISITOR_NAME
varchar(255) DEFAULT NULL,
VISITOR_SEX
varchar(255) DEFAULT NULL,
VISITOR_CALL
varchar(255) DEFAULT NULL,
REASON
varchar(255) DEFAULT NULL,
BEDROOM_NAME
varchar(255) DEFAULT NULL,
VISITOR_DATE
date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
– Records of visitor