一、系统介绍
1.软件环境
Java：jdk1.8
Mysql：8.0.13
Tomcat：8.5.23
2.系统功能
学生
1.查看个人信息
2.选课
3.查看已选课程
教师
1.查看个人信息
2.评分
3.查看任课课程
管理员
1.管理员功能
(1).查看个人信息
(2).添加新的管理员
2.学生功能
(1).添加学生
(2).获取所有学生
3.课程功能
(1).添加课程
(2).查询课程
4.教师功能
(1).添加教师
(2)获取所有教师
3.数据库
/* Navicat Premium Data Transfer Source Server : MySQL Source Server Type : MySQL Source Server Version : 80013 Source Host : localhost:3306 Source Schema : jsp_servlet_selectcourse Target Server Type : MySQL Target Server Version : 80013 File Encoding : 65001 Date: 23/06/2021 20:46:30 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_admin -- ---------------------------- DROP TABLE IF EXISTS `t_admin`; CREATE TABLE `t_admin` ( `userid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `username` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `password` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(3) NULL DEFAULT NULL, `score` decimal(5, 1) NULL DEFAULT NULL, `introduction` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `enterdate` date NULL DEFAULT NULL, PRIMARY KEY (`userid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_admin -- ---------------------------- INSERT INTO `t_admin` VALUES ('admin', '管理员', 'admin', 21, 100.0, ' ', '2018-06-12'); INSERT INTO `t_admin` VALUES ('admin1', '水坚石青', 'admin1', 25, 99.0, '', '2021-06-22'); -- ---------------------------- -- Table structure for t_class -- ---------------------------- DROP TABLE IF EXISTS `t_class`; CREATE TABLE `t_class` ( `classno` int(4) NOT NULL AUTO_INCREMENT, `cname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `cteacher` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `classroom` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`classno`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1531 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_class -- ---------------------------- INSERT INTO `t_class` VALUES (1520, '软工', '赵丽', '综阶1'); INSERT INTO `t_class` VALUES (1521, '软工', '齐兴斌', '综阶2'); INSERT INTO `t_class` VALUES (1522, '软工', '张志斌', '综阶3'); INSERT INTO `t_class` VALUES (1523, '软工', '郭小英', '综阶5'); INSERT INTO `t_class` VALUES (1524, '软工', '郭新峰', '综阶6'); INSERT INTO `t_class` VALUES (1525, '软工', '王若慧', '综阶7'); INSERT INTO `t_class` VALUES (1526, '软工', '贾春华', '综阶8'); INSERT INTO `t_class` VALUES (1527, '软工', '朱云雷', '综阶9'); INSERT INTO `t_class` VALUES (1528, '软工', '李雪梅', '综阶10'); INSERT INTO `t_class` VALUES (1529, '软工', '张举 ', '综阶11'); INSERT INTO `t_class` VALUES (1530, '软工', '米晓萍', '综阶12'); INSERT INTO `t_class` VALUES (1531, '软工', '张建英', '综阶13'); -- ---------------------------- -- Table structure for t_course -- ---------------------------- DROP TABLE IF EXISTS `t_course`; CREATE TABLE `t_course` ( `cno` int(4) NOT NULL AUTO_INCREMENT, `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `credit` int(1) NULL DEFAULT NULL, `periodstart` date NULL DEFAULT NULL, `periodend` date NULL DEFAULT NULL, PRIMARY KEY (`cno`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1009 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_course -- ---------------------------- INSERT INTO `t_course` VALUES (1001, '数据库', 4, '2018-02-01', '2018-08-08'); INSERT INTO `t_course` VALUES (1002, '数据结构', 4, '2018-02-01', '2018-08-08'); INSERT INTO `t_course` VALUES (1003, 'j2ee', 4, '2018-02-01', '2018-08-08'); INSERT INTO `t_course` VALUES (1004, '计算机网络', 4, '2018-02-01', '2018-08-08'); INSERT INTO `t_course` VALUES (1005, '计算机组成原理', 4, '2018-02-01', '2018-08-08'); INSERT INTO `t_course` VALUES (1007, '编译原理', 4, '2018-02-01', '2018-08-08'); INSERT INTO `t_course` VALUES (1008, 'C语言', 4, '2018-02-01', '2018-02-01'); INSERT INTO `t_course` VALUES (1009, 'c++', 4, '2018-01-02', '2018-05-28'); INSERT INTO `t_course` VALUES (1010, '1', 1, '2021-06-22', '2021-06-22'); -- ---------------------------- -- Table structure for t_sc -- ---------------------------- DROP TABLE IF EXISTS `t_sc`; CREATE TABLE `t_sc` ( `sno` int(10) NOT NULL, `cno` int(4) NOT NULL, `tno` int(4) NOT NULL, `score` decimal(5, 2) NULL DEFAULT NULL, PRIMARY KEY (`sno`, `cno`, `tno`) USING BTREE, INDEX `t_sc_ibfk_2`(`cno`) USING BTREE, INDEX `t_sc_ibfk_3`(`tno`) USING BTREE, CONSTRAINT `t_sc_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `t_student` (`sno`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `t_sc_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `t_course` (`cno`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `t_sc_ibfk_3` FOREIGN KEY (`tno`) REFERENCES `t_teacher` (`tno`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_sc -- ---------------------------- INSERT INTO `t_sc` VALUES (2015001, 1002, 1001, 100.00); INSERT INTO `t_sc` VALUES (2015001, 1004, 1001, 99.00); INSERT INTO `t_sc` VALUES (2015001, 1004, 1006, NULL); INSERT INTO `t_sc` VALUES (2015001, 1005, 1002, NULL); INSERT INTO `t_sc` VALUES (2015001, 1007, 1004, NULL); INSERT INTO `t_sc` VALUES (2015001, 1008, 1002, NULL); INSERT INTO `t_sc` VALUES (2015001, 1008, 1004, NULL); INSERT INTO `t_sc` VALUES (2015001, 1008, 1005, NULL); -- ---------------------------- -- Table structure for t_student -- ---------------------------- DROP TABLE IF EXISTS `t_student`; CREATE TABLE `t_student` ( `sno` int(9) NOT NULL AUTO_INCREMENT, `password` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `phone` bigint(11) NULL DEFAULT NULL, `sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `birthday` date NULL DEFAULT NULL, `classno` int(4) NULL DEFAULT NULL, `remark` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`sno`) USING BTREE, INDEX `t_student_ibfk_1`(`classno`) USING BTREE, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`classno`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB AUTO_INCREMENT = 2015570 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_student -- ---------------------------- INSERT INTO `t_student` VALUES (2015001, '123456', '李四', 15788888888, '女', '2021-06-22', 1525, '优秀'); INSERT INTO `t_student` VALUES (2015002, '123456', '王茹', 15788888888, '女', '2018-05-28', 1520, '良好'); INSERT INTO `t_student` VALUES (2015003, '123456', '张三', 15788888888, '女', '2018-05-28', 1520, '良好'); INSERT INTO `t_student` VALUES (2015004, '123456', '王五', 15788888888, '女', '2018-05-28', 1520, '优秀'); INSERT INTO `t_student` VALUES (2015005, '123456', '李浩', 15788888888, '女', '2018-05-28', 1520, '合格'); INSERT INTO `t_student` VALUES (2015006, '123456', '黄县', 15788888888, '女', '2018-05-28', 1520, '良好'); INSERT INTO `t_student` VALUES (2015007, '123456', '钱一', 15788888888, '女', '2018-05-28', 1520, '优秀'); INSERT INTO `t_student` VALUES (2015009, '123456', '赵括', 15788888888, '女', '2018-05-28', 1520, '优秀'); INSERT INTO `t_student` VALUES (2015010, '123456', '赵括', 15788888888, '女', '2018-05-28', 1520, '优秀'); -- ---------------------------- -- Table structure for t_tc -- ---------------------------- DROP TABLE IF EXISTS `t_tc`; CREATE TABLE `t_tc` ( `cno` int(4) NOT NULL, `tno` int(4) NOT NULL, PRIMARY KEY (`cno`, `tno`) USING BTREE, INDEX `t_tc_ibfk_2`(`tno`) USING BTREE, CONSTRAINT `t_tc_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_course` (`cno`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `t_tc_ibfk_2` FOREIGN KEY (`tno`) REFERENCES `t_teacher` (`tno`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_tc -- ---------------------------- INSERT INTO `t_tc` VALUES (1001, 1001); INSERT INTO `t_tc` VALUES (1002, 1001); INSERT INTO `t_tc` VALUES (1004, 1001); INSERT INTO `t_tc` VALUES (1009, 1001); INSERT INTO `t_tc` VALUES (1005, 1002); INSERT INTO `t_tc` VALUES (1008, 1002); INSERT INTO `t_tc` VALUES (1002, 1003); INSERT INTO `t_tc` VALUES (1004, 1003); INSERT INTO `t_tc` VALUES (1007, 1004); INSERT INTO `t_tc` VALUES (1008, 1004); INSERT INTO `t_tc` VALUES (1008, 1005); INSERT INTO `t_tc` VALUES (1004, 1006); -- ---------------------------- -- Table structure for t_teacher -- ---------------------------- DROP TABLE IF EXISTS `t_teacher`; CREATE TABLE `t_teacher` ( `tno` int(4) NOT NULL AUTO_INCREMENT, `tname` 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, `phone` bigint(11) NULL DEFAULT NULL, `hiredate` date NULL DEFAULT NULL, `remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`tno`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1006 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_teacher -- ---------------------------- INSERT INTO `t_teacher` VALUES (1001, '张志斌', '123456', 15788888888, '2017-07-20', '张老师是一个超级幽默的老师，教学认真，态度友好，有自己独有的教学方法，深得学生喜爱'); INSERT INTO `t_teacher` VALUES (1002, '白茹意', '123456', 15766666666, '2018-03-06', '白老师工作认真负责，不推卸责任'); INSERT INTO `t_teacher` VALUES (1003, '郭新峰', '123456', 15733333333, '2018-05-14', '<span style=\"font-family:Arial Black;\"><span style=\"color:#E53333;\"><span style=\"color:#E53333;\">郭老师很认真负责</span></span></span>'); INSERT INTO `t_teacher` VALUES (1004, '赵丽', '123456', 15722222222, '2018-04-03', NULL); INSERT INTO `t_teacher` VALUES (1005, '齐兴斌', '123456', 15711111111, '2004-05-28', NULL); INSERT INTO `t_teacher` VALUES (1006, '尹少平', '123456', 15777777777, '2014-06-11', NULL); SET FOREIGN_KEY_CHECKS = 1;
二、系统展示
1.登录页面
2.学生-主页面
3.学生-查看个人信息
4.学生-选择课程
5.学生-查看已选课程
6.教师-主页面
7.教师-查看个人信息
8.教师-评分
9.教师-查看任课信息
10.管理员-主页面
11.管理员-管理员功能-查看个人信息
12.管理员-管理员功能-添加新的管理员
13.管理员-学生功能-添加学生
14.管理员-学生功能-获取所有学生
15.管理员-课程功能-添加课程
16.管理员-课程功能-查询课程
17.管理员-教师功能-添加教师
18.管理员-教师功能-获取所有教师
三、部分代码
AdminDaoImpl.java
package com.bluehonour.sscs.dao.impl; import java.sql.Connection; import java.util.Date; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.bluehonour.sscs.dao.AdminDao; import com.bluehonour.sscs.entity.Admin; import com.bluehonour.sscs.util.DBUtils; public class AdminDaoImpl implements AdminDao{ @Override public Admin find(String userId, String password) { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet rs = null; Admin admin = null; try { //建立连接 connection = DBUtils.getConnection(); //向数据库发送sql命令并得到结果 String sql = "select * from t_admin where userid = ? and password = ?"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, userId); preparedStatement.setString(2, password); rs = preparedStatement.executeQuery(); //处理返回结果 if(rs.next()) { //取出结果集当前行各个字段的值 String userName = rs.getString("username"); int age = rs.getInt("age"); double score = rs.getDouble("score"); Date enterDate = rs.getDate("enterdate"); String introduction = rs.getString("introduction"); //封装成对象 admin = new Admin(userId, userName, password, age, score, enterDate, introduction); } } catch (SQLException e) { e.printStackTrace(); } finally { //关闭数据库资源 DBUtils.closeAll(rs, preparedStatement, connection); } return admin; } @Override public int save(Admin admin) { String sql = "insert into t_admin values(?,?,?,?,?,?,?)"; Object[] params = {admin.getUserId(),admin.getUserName(),admin.getPassword(),admin.getAge(), admin.getScore(),admin.getIntroduction(),admin.getEnterDate()}; return DBUtils.executeUpdate(sql, params); } }
CourseDaoImpl.java
package com.bluehonour.sscs.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import java.util.List; import com.bluehonour.sscs.dao.CourseDao; import com.bluehonour.sscs.entity.Course; import com.bluehonour.sscs.util.DBUtils; public class CourseDaoImpl implements CourseDao{ @Override public int save(Course course) { String sql = "insert into t_course (name,credit,periodstart,periodend) values(?,?,?,?) "; Object[] params = {course.getName(), course.getCredit(), course.getPeriodstart(), course.getPeriodend()}; return DBUtils.executeUpdate(sql, params); } @Override public List<Course> findAll() { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet rs = null; List<Course> list = new ArrayList<Course>(); try { // 建立连接 connection = DBUtils.getConnection(); // 向数据库发送sql命令并得到结果 String sql = "select * from t_course order by cno"; preparedStatement = connection.prepareStatement(sql); rs = preparedStatement.executeQuery(); // 处理返回结果 while (rs.next()) { // 取出结果集当前行各个字段的值 int cno = rs.getInt("cno"); String name = rs.getString("name"); int credit = rs.getInt("credit"); Date periodstart = rs.getDate("periodstart"); Date periodend = rs.getDate("periodend"); // 封装成对象 Course course = new Course(cno,name, credit, periodstart, periodend); list.add(course); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtils.closeAll(rs, preparedStatement, connection); } return list; } }
StudentCourseTeacherDaoImpl.java
package com.bluehonour.sscs.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import java.util.List; import com.bluehonour.sscs.dao.StudentCourseTeacherDao; import com.bluehonour.sscs.entity.Course; import com.bluehonour.sscs.entity.StudentCourse; import com.bluehonour.sscs.entity.Teacher; import com.bluehonour.sscs.util.DBUtils; public class StudentCourseTeacherDaoImpl implements StudentCourseTeacherDao { @Override public int save(int sno, int cno, int tno) { String sql = "insert into t_sc(sno,cno,tno) values(?,?,?)"; Object[] params = {sno,cno,tno}; return DBUtils.executeUpdate(sql, params); } @Override public List<Course> findSelectedCourse(int sno) { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet rs = null; List<Course> list = new ArrayList<Course>(); try { // 建立连接 connection = DBUtils.getConnection(); // 向数据库发送sql命令并得到结果 String sql = "select * from t_course c" + " join t_sc sc" + " on (c.cno = sc.cno)" + " join t_teacher t" + " on (sc.tno = t.tno)" + " where sno = " + sno; preparedStatement = connection.prepareStatement(sql); rs = preparedStatement.executeQuery(); // 处理返回结果 while (rs.next()) { // 取出结果集当前行课程各个字段的值 int cno = rs.getInt("cno"); String name = rs.getString("name"); int credit = rs.getInt("credit"); Date periodstart = rs.getDate("periodstart"); Date periodend = rs.getDate("periodend"); // 封装成课程对象 Course course = new Course(cno,name, credit, periodstart, periodend); //取出结果集中教师各个字段的值 int tno = rs.getInt("tno"); String tname = rs.getString("tname"); String password = rs.getString("password"); long phone = rs.getLong("phone"); Date hiredate = rs.getDate("hiredate"); String remark = rs.getString("remark"); //封装成教师对象 Teacher teacher = new Teacher(tno,tname, password, phone, hiredate, remark); //将教师加入课程 course.setTeacher(teacher); list.add(course); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtils.closeAll(rs, preparedStatement, connection); } return list; } @Override public List<Course> findSelectableCourse(int sno) { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet rs = null; List<Course> list = new ArrayList<Course>(); try { // 建立连接 connection = DBUtils.getConnection(); // 向数据库发送sql命令并得到结果 String sql = "SELECT c.*, t.* FROM t_tc a " + "LEFT JOIN t_course c " + "ON a.cno = c.cno " + "LEFT JOIN t_teacher t " + "ON a.tno = t.tno " + "WHERE (a.cno, a.tno) NOT IN " + "( SELECT cno,tno " + "FROM t_sc " + "WHERE sno = " + sno +") "; preparedStatement = connection.prepareStatement(sql); rs = preparedStatement.executeQuery(); // 处理返回结果 while (rs.next()) { // 取出结果集当前行课程各个字段的值 int cno = rs.getInt("cno"); String name = rs.getString("name"); int credit = rs.getInt("credit"); Date periodstart = rs.getDate("periodstart"); Date periodend = rs.getDate("periodend"); // 封装成课程对象 Course course = new Course(cno,name, credit, periodstart, periodend); //取出结果集中教师各个字段的值 int tno = rs.getInt("tno"); String tname = rs.getString("tname"); String password = rs.getString("password"); long phone = rs.getLong("phone"); Date hiredate = rs.getDate("hiredate"); String remark = rs.getString("remark"); //封装成教师对象 Teacher teacher = new Teacher(tno,tname, password, phone, hiredate, remark); //将教师加入课程 course.setTeacher(teacher); list.add(course); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtils.closeAll(rs, preparedStatement, connection); } return list; } @Override public int removeStudentDistributedCourse(int sno, int cno, int tno) { String sql = "delete from t_sc where sno = ? and cno = ? and tno = ?"; Object[] params = {sno,cno,tno}; return DBUtils.executeUpdate(sql, params); } @Override public List<StudentCourse> getSelectedStudentAndCourse(int tno) { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet rs = null; List<StudentCourse> list = new ArrayList<StudentCourse>(); try { // 建立连接 connection = DBUtils.getConnection(); // 向数据库发送sql命令并得到结果 String sql = "SELECT" + " s.sno," + " s.sname," + " s.classno," + " clazz.cname," + " c.cno," + " c. NAME," + " c.credit," + " sc.score" + " FROM" + " t_student s" + " LEFT JOIN t_class clazz ON clazz.classno = s.classno" + " LEFT JOIN t_sc sc ON sc.sno = s.sno" + " LEFT JOIN t_course c ON c.cno = sc.cno" + " WHERE" + " sc.tno = " + tno + " ORDER BY" + " c.cno," + " s.sno"; preparedStatement = connection.prepareStatement(sql); rs = preparedStatement.executeQuery(); // 处理返回结果 while (rs.next()) { int cno = rs.getInt("cno"); String name = rs.getString("name"); int credit = rs.getInt("credit"); int sno = rs.getInt("sno"); int classno = rs.getInt("classno"); String sname = rs.getString("sname"); String cname = rs.getString("cname"); double score = rs.getDouble("score"); //封装成教师对象 StudentCourse sc = new StudentCourse(sno, sname, classno, cname, cno, name, credit, score); list.add(sc); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtils.closeAll(rs, preparedStatement, connection); } return list; } @Override public int courseRemark(int sno, int cno, int tno, double score) { String sql = "update t_sc set score = ? where sno = ? and cno = ? and tno = ?"; Object[] params = {score,sno,cno,tno}; return DBUtils.executeUpdate(sql, params); } }
