目录
一、系统介绍
1.开发环境
2.技术选型
3.系统功能
4.数据库
5.工程截图
二、系统展示
1.注册页面
2.登录页面
3.学生-主页面
4.学生-个人信息-查询登录密码
5.学生-个人信息-查看个人信息
6.学生-个人信息-修改个人信息
7.学生-成绩管理-成绩信息查看
8.教师-主页面
9.教师-个人信息-查询登录密码
10.教师-个人信息-查看个人信息
11.教师-个人信息-修改个人信息
12.教师-课程管理-课程信息查看
13.教师-课程管理-添加课程信息
14.教师-成绩管理-成绩信息查看
15.教师-成绩管理-添加学生成绩
16.教师-学生管理-查询学生信息
17.管理员-主页面
18.管理员-个人信息
19.管理员-课程管理-课程信息查看
20.管理员-课程管理-添加课程信息
21.管理员-成绩管理-成绩信息查看
22.管理员-成绩管理-添加学生成绩
23.管理员-成绩管理-学生总成绩
24.管理员-教师管理-查询教师信息
25.管理员-教师管理-添加教师信息
26.管理员-学生管理-查询学生信息
27.管理员-学生管理-添加学生信息
28.管理员-查询所有账号
三、部分代码
CourseDaoImpl
ScoreDaoImpl
StudentDaoImpl
TeacherDaoImpl
UserDaoImpl
DBCon
register.jsp
login.jsp
selectScore.jsp
四、其他
1.其他系统实现
1.JavaWeb系统系列实现
2.JavaSwing系统系列实现
2.获取源码
3.运行项目
4.备注
5.支持博主
一、系统介绍
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.成绩管理
成绩信息查看
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_score
Target Server Type : MySQL
Target Server Version : 80013
File Encoding : 65001
Date: 03/07/2021 16:46:10
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`course_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`course_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程名',
`course_credit` int(11) NOT NULL COMMENT '学分',
`course_hours` int(11) NOT NULL COMMENT '学时',
`course_teacher` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '任课教师',
`coursedate` date NOT NULL COMMENT '开课时间',
PRIMARY KEY (`course_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, 'C++程序设计教程', 5, 60, '汪维清', '2017-03-02');
INSERT INTO `course` VALUES (2, 'java入门基础', 3, 60, '肖兴江', '2017-02-09');
INSERT INTO `course` VALUES (3, '计算机组成原理', 3, 60, '胡继宽', '2017-02-09');
INSERT INTO `course` VALUES (4, '信息检索', 2, 60, '郑蔚', '2017-02-09');
INSERT INTO `course` VALUES (5, ' *** 作系统原理', 4, 60, '丁华峰', '2018-03-15');
INSERT INTO `course` VALUES (6, '管理信息系统', 4, 60, '杜治国', '2019-02-11');
-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`role_id` int(11) NOT NULL COMMENT '角色编号',
`role_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '角色名称',
`role_info` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '角色信息',
PRIMARY KEY (`role_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES (0, '学生', '学生查看成绩');
INSERT INTO `role` VALUES (1, '教师', '教师 *** 作');
INSERT INTO `role` VALUES (2, '管理员', '管理员 *** 作');
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`score_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '成绩编号',
`stu_num` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号',
`stu_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
`stu_class` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '班级',
`course_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '科目',
`score_grade` double(11, 2) NOT NULL COMMENT '成绩',
`major` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '专业',
PRIMARY KEY (`score_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 112 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, '170340', '张三', '计科1701', 'java入门基础', 96.50, '计算机');
INSERT INTO `score` VALUES (2, '170340', '张三', '计科1701', 'C++程序设计教程', 85.00, '计算机');
INSERT INTO `score` VALUES (3, '160341', '王五', '信管1601', 'java入门基础', 62.60, '信息管理与信息系统');
INSERT INTO `score` VALUES (4, '160341', '王五', '信管1601', 'C++程序设计教程', 85.00, '信息管理与信息系统');
INSERT INTO `score` VALUES (5, '170340', '张三', '计科1701', '计算机组成原理', 69.00, '计算机');
INSERT INTO `score` VALUES (6, '170340', '张三', '计科1701', '信息检索', 95.00, '计算机');
INSERT INTO `score` VALUES (7, '170340', '张三', '计科1701', ' *** 作系统原理', 89.00, '计算机');
INSERT INTO `score` VALUES (8, '160341', '王五', '计科1701', 'C++程序设计教程', 95.00, '计算机');
INSERT INTO `score` VALUES (9, '160341', '王五', '信管1601', 'java入门基础', 92.00, '信息管理与信息系统');
INSERT INTO `score` VALUES (10, '160341', '王五', '信管1601', '计算机组成原理', 83.00, '信息管理与信息系统');
INSERT INTO `score` VALUES (11, '170339', '李四', '计科1701', 'java入门基础', 78.20, '计算机');
INSERT INTO `score` VALUES (12, '170339', '李四', '计科1701', '信息检索', 98.00, '计算机');
INSERT INTO `score` VALUES (13, '170339', '李四', '计科1701', '计算机组成原理', 76.00, '计算机');
INSERT INTO `score` VALUES (14, '170339', '李四', '计科1701', ' *** 作系统原理', 69.80, '计算机');
INSERT INTO `score` VALUES (15, '170339', '李四', '计科1701', 'C++程序设计教程', 89.00, '计算机');
INSERT INTO `score` VALUES (16, '170343', '陈留', '计科1701', 'java入门基础', 80.50, '计算机');
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`stu_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
`stu_num` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '账号',
`stu_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
`stu_sex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '性别',
`stu_age` int(11) NOT NULL COMMENT '年龄',
`stu_class` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '班级',
`major` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '专业',
`department` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '院系',
PRIMARY KEY (`stu_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 35 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '160341', '王五', '女', 21, '信管1601', '信息管理与信息系统', '商贸学院');
INSERT INTO `student` VALUES (2, '170340', '张三', '男', 20, '计科1701', '计算机', '商贸学院');
INSERT INTO `student` VALUES (3, '170339', '李四', '男', 20, '计科1701', '计算机', '商贸学院');
INSERT INTO `student` VALUES (4, '170343', '陈留', '男', 40, '计科1701', '计算机', '商贸学院');
INSERT INTO `student` VALUES (5, '160342', '盛祎琛', '女', 19, '信管1602', '信息管理与信息系统', '商贸学院');
INSERT INTO `student` VALUES (6, '160343', '闫玉平', '女', 20, '信管1601', '信息管理与信息系统', '商贸学院');
INSERT INTO `student` VALUES (7, '160344', '陈淑婷', '女', 20, '信管1601', '信息管理与信息系统', '商贸学院');
INSERT INTO `student` VALUES (8, '160345', '周梦琪', '女', 20, '信管1601', '信息管理与信息系统', '商贸学院');
INSERT INTO `student` VALUES (9, '160346', '曾智', '女', 20, '信管1601', '信息管理与信息系统', '商贸学院');
INSERT INTO `student` VALUES (11, '160348', '多罗罗', '男', 25, '信管1601', '信息管理与信息系统', '商贸学院');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tea_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
`tea_num` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '工号',
`tea_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
`tea_sex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '性别',
`tea_age` int(11) NOT NULL COMMENT '年龄',
`tea_course` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '所任课程',
`major` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '专业',
`department` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '院系',
PRIMARY KEY (`tea_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '1123', '肖兴江', '男', 30, 'java入门基础', '计算机', '商贸学院');
INSERT INTO `teacher` VALUES (2, '1124', '汪维清', '男', 25, 'C++程序设计教程', '计算机', '商贸学院');
INSERT INTO `teacher` VALUES (3, '1125', '胡继宽', '男', 32, '计算机组成原理', '信息管理与信息系统', '商贸学院');
INSERT INTO `teacher` VALUES (4, '1126', '郑蔚', '女', 34, '信息检索', '信息管理与信息系统', '商贸学院');
INSERT INTO `teacher` VALUES (5, '1127', '丁华峰', '男', 40, ' *** 作系统原理', '计算机', '商贸学院');
INSERT INTO `teacher` VALUES (6, '1128', '杜治国', '男', 35, '管理信息系统', '信息管理与信息系统', '商贸学院');
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`user_num` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户账号',
`user_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
`password` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',
`phone` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户电话',
`role_id` int(11) NOT NULL COMMENT '角色编号（外键）',
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 43 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '160341', '王五', '123456', '14829726746', 0);
INSERT INTO `user` VALUES (2, '170340', '张三', '123456', '15869483651', 0);
INSERT INTO `user` VALUES (3, '170339', '李四', '123456', '13589462584', 0);
INSERT INTO `user` VALUES (6, '1123', '肖兴江', '123456', '17418953551', 1);
INSERT INTO `user` VALUES (7, '1124', '汪维清', '123456', '15897683584', 1);
INSERT INTO `user` VALUES (9, '1125', '胡继宽', '123456', '12378945862', 1);
INSERT INTO `user` VALUES (10, '1126', '郑蔚', '123456', '13589462584', 1);
INSERT INTO `user` VALUES (11, '1001', '管理员', 'admin', '18179586325', 2);
INSERT INTO `user` VALUES (12, '1127', '丁华峰', '123456', '16123598785', 1);
INSERT INTO `user` VALUES (13, '1128', '杜治国', '123456', '13158794456', 1);
INSERT INTO `user` VALUES (14, '160344', '陈淑婷', '123456', '14829726746', 0);
INSERT INTO `user` VALUES (15, '160345', '周梦琪', '123456', '14829726746', 0);
-- ----------------------------
-- Triggers structure for table student
-- ----------------------------
DROP TRIGGER IF EXISTS `stuLogin`;
delimiter ;;
CREATE TRIGGER `stuLogin` AFTER DELETE ON `student` FOR EACH ROW begin
delete from user where user_num=old.stu_num;
end
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table teacher
-- ----------------------------
DROP TRIGGER IF EXISTS `teaLogin`;
delimiter ;;
CREATE TRIGGER `teaLogin` AFTER DELETE ON `teacher` FOR EACH ROW begin
delete from user where user_num=old.tea_num;
end
;;
delimiter ;
SET FOREIGN_KEY_CHECKS = 1;
package dao.Impl;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.List;
import model.Course;
import model.PageBean;
import dao.CourseDao;
import db.DBCon;
public class CourseDaoImpl implements CourseDao {
private DBCon dbCon = new DBCon();
private Course course;
private PageBean pageBean;
public int addCos(Course course, String courseDate) {
System.out.println(courseDate);
// Date courseDate=(Date) course.getCourseDate();
// DateFormat
// data=DateFormat.getDateTimeInstance(DateFormat.MEDIUM,DateFormat.MEDIUM);
String sql = "insert into course(course_name,course_credit,course_hours,course_teacher,coursedate) values('"
+ course.getCourseName()
+ "',"
+ course.getCourseCredit()
+ ","
+ course.getCourseHours()
+ ",'"
+ course.getCourseTeacher() + "',DATE('" + courseDate + "'))";
int rs = dbCon.query(sql);
return rs;
}
public int deleteCos(int courseID) {
String sql = "delete from course where course_id=" + courseID;
int rs = dbCon.query(sql);
return rs;
}
public int editCos(Course course, String courseDate) {
String sql = "update course set course_name='" + course.getCourseName()
+ "',course_credit=" + course.getCourseCredit()
+ ",course_hours=" + course.getCourseHours()
+ ",course_teacher='" + course.getCourseTeacher()
+ "',coursedate=DATE('" + courseDate + "') where course_id="
+ course.getCourseID();
int rs = dbCon.query(sql);
return rs;
}
public List findAll() {
List list = new ArrayList();
String sql = "select * from course";
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
int courseID = rs.getInt("course_id");
String courseName = rs.getString("course_name");
int courseCredit = rs.getInt("course_credit");
int courseHours = rs.getInt("course_hours");
String courseTea = rs.getString("course_teacher");
Date courseDate = rs.getDate("coursedate");
course = new Course(courseID, courseName, courseCredit,
courseHours, courseTea, courseDate);
list.add(course);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public List findOne(int courseID) {
List list = new ArrayList();
String sql = "select * from course where course_id=" + courseID;
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
int courseid = rs.getInt("course_id");
String courseName = rs.getString("course_name");
int courseCredit = rs.getInt("course_credit");
int courseHours = rs.getInt("course_hours");
String courseTea = rs.getString("course_teacher");
Date courseDate = rs.getDate("coursedate");
course = new Course(courseid, courseName, courseCredit,
courseHours, courseTea, courseDate);
list.add(course);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public Course selectOneCos(Course cours) {
String sql = "select * from course where course_name='"
+ cours.getCourseName() + "' and course_teacher='"
+ cours.getCourseTeacher() + "'";
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
int courseid = rs.getInt("course_id");
String courseName = rs.getString("course_name");
int courseCredit = rs.getInt("course_credit");
int courseHours = rs.getInt("course_hours");
String courseTea = rs.getString("course_teacher");
Date courseDate = rs.getDate("coursedate");
course = new Course(courseid, courseName, courseCredit,
courseHours, courseTea, courseDate);
}
} catch (SQLException e) {
e.printStackTrace();
}
return course;
}
public PageBean courseListPage(int pageNo, int pageCount) {
int totalCount=0;
List list = new ArrayList();
String sql = "select * from course limit "+((pageNo-1)*pageCount)+","+pageCount;
String sqlCount="select count(*) from course";
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
int courseID = rs.getInt("course_id");
String courseName = rs.getString("course_name");
int courseCredit = rs.getInt("course_credit");
int courseHours = rs.getInt("course_hours");
String courseTea = rs.getString("course_teacher");
Date courseDate = rs.getDate("coursedate");
course = new Course(courseID, courseName, courseCredit,
courseHours, courseTea, courseDate);
list.add(course);
}
rs=dbCon.find(sqlCount);
while(rs.next()){
totalCount=rs.getInt(1);
}
pageBean=new PageBean(list,totalCount,pageNo,pageCount);
} catch (SQLException e) {
e.printStackTrace();
}
return pageBean;
}
}
package dao.Impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import model.PageBean;
import model.Score;
import model.ScoreCou;
import model.ScoreSum;
import dao.ScoreDao;
import db.DBCon;
public class ScoreDaoImpl implements ScoreDao {
private Score score = null;
private ScoreCou scoreCou = null;
private PageBean pageBean = null;
private ScoreSum scoreSum = null;
private DBCon dbCon = new DBCon();
public int addScore(Score score) {
String sql = "insert into score(stu_num,stu_name,stu_class,course_name,score_grade,major) values('"
+ score.getStuNum()
+ "','"
+ score.getStuName()
+ "','"
+ score.getStuClass()
+ "','"
+ score.getCourseName()
+ "',"
+ score.getScoreGrade() + ",'" + score.getMajor() + "')";
int rs = dbCon.query(sql);
return rs;
}
public int deleteScore(int scoreID) {
String sql = "delete from score where score_id=" + scoreID;
int rs = dbCon.query(sql);
return rs;
}
public int editCos(Score score) {
String sql = "update score set stu_num='" + score.getStuNum()
+ "',stu_name='" + score.getStuName() + "',stu_class='"
+ score.getStuClass() + "',course_name='"
+ score.getCourseName() + "',score_grade="
+ score.getScoreGrade() + ",major='" + score.getMajor()
+ "' where score_id=" + score.getScoreID();
int rs = dbCon.query(sql);
return rs;
}
public List findAll() {
List list = new ArrayList();
String sql = "select * from score";
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
int scoreID = rs.getInt("score_id");
String stuNum = rs.getString("stu_num");
String stuName = rs.getString("stu_name");
String stuClass = rs.getString("stu_class");
String courseName = rs.getString("course_name");
double scoreGrade = rs.getDouble("score_grade");
String major = rs.getString("major");
Score score = new Score(scoreID, stuNum, stuName, stuClass,
courseName, scoreGrade, major);
list.add(score);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public List findOne(String stuNum) {
List list = new ArrayList();
String sql = "select score.course_name,course_credit,course_hours,course_teacher,major,coursedate,score_grade "
+ "from score,course where score.course_name=course.course_name and stu_num='"
+ stuNum + "'";
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
String courseName = rs.getString("course_name");
int courseCredit = rs.getInt("course_credit");
int courseHours = rs.getInt("course_hours");
String courseTea = rs.getString("course_teacher");
String major = rs.getString("major");
Date courseDate = rs.getDate("coursedate");
double scoreGrade = rs.getDouble("score_grade");
ScoreCou scoreCou = new ScoreCou(courseName, courseCredit,
courseHours, courseTea, major, courseDate, scoreGrade);
list.add(scoreCou);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public List selectScore(int scoreID) {
List list = new ArrayList();
String sql = "select * from score where score_id=" + scoreID;
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
int scoreid = rs.getInt("score_id");
String stuNum = rs.getString("stu_num");
String stuName = rs.getString("stu_name");
String stuClass = rs.getString("stu_class");
String courseName = rs.getString("course_name");
double scoreGrade = rs.getDouble("score_grade");
String major = rs.getString("major");
Score score = new Score(scoreid, stuNum, stuName, stuClass,
courseName, scoreGrade, major);
list.add(score);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public Score selectScoreInfo(Score scor) {
String sql = "select * from score where stu_num='" + scor.getStuNum()
+ "' and course_name='" + scor.getCourseName() + "'";
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
int scoreid = rs.getInt("score_id");
String stuNum = rs.getString("stu_num");
String stuName = rs.getString("stu_name");
String stuClass = rs.getString("stu_class");
String courseName = rs.getString("course_name");
double scoreGrade = rs.getDouble("score_grade");
String major = rs.getString("major");
score = new Score(scoreid, stuNum, stuName, stuClass,
courseName, scoreGrade, major);
}
} catch (SQLException e) {
e.printStackTrace();
}
return score;
}
public PageBean scoreListPage(int pageNo, int pageCount, String stuNum) {
int totalCount = 0;
List list = new ArrayList();
String sql = "select score.course_name,course_credit,course_hours,course_teacher,major,coursedate,score_grade "
+ "from score,course where score.course_name=course.course_name and stu_num='"
+ stuNum
+ "' limit "
+ ((pageNo - 1) * pageCount)
+ ","
+ pageCount;
String sqlCount = "select count(*) from score,course where score.course_name=course.course_name and stu_num='"
+ stuNum + "'";
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
String courseName = rs.getString("course_name");
int courseCredit = rs.getInt("course_credit");
int courseHours = rs.getInt("course_hours");
String courseTea = rs.getString("course_teacher");
String major = rs.getString("major");
Date courseDate = rs.getDate("coursedate");
double scoreGrade = rs.getDouble("score_grade");
ScoreCou scoreCou = new ScoreCou(courseName, courseCredit,
courseHours, courseTea, major, courseDate, scoreGrade);
list.add(scoreCou);
}
rs = dbCon.find(sqlCount);
while (rs.next()) {
totalCount = rs.getInt(1);
}
pageBean = new PageBean(list, totalCount, pageNo, pageCount);
} catch (SQLException e) {
dbCon.close();
e.printStackTrace();
}
return pageBean;
}
public PageBean scoreListPage(int pageNo, int pageCount) {
int totalCount = 0;
List list = new ArrayList();
String sql = "select * from score limit " + ((pageNo - 1) * pageCount)
+ "," + pageCount;
String sqlCount = "select count(*) from score";
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
int scoreID = rs.getInt("score_id");
String stuNum = rs.getString("stu_num");
String stuName = rs.getString("stu_name");
String stuClass = rs.getString("stu_class");
String courseName = rs.getString("course_name");
double scoreGrade = rs.getDouble("score_grade");
String major = rs.getString("major");
score = new Score(scoreID, stuNum, stuName, stuClass,
courseName, scoreGrade, major);
list.add(score);
}
rs = dbCon.find(sqlCount);
while (rs.next()) {
totalCount = rs.getInt(1);
}
pageBean = new PageBean(list, totalCount, pageNo, pageCount);
} catch (SQLException e) {
dbCon.close();
e.printStackTrace();
}
return pageBean;
}
public List findStuOne(String stuNum, String courseName) {
List list = new ArrayList();
String sql = "select score.course_name,course_credit,course_hours,course_teacher,major,coursedate,score_grade "
+ "from score,course where score.course_name=course.course_name and stu_num='"
+ stuNum + "' and course.course_name='" + courseName + "'";
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
String coursename = rs.getString("course_name");
int courseCredit = rs.getInt("course_credit");
int courseHours = rs.getInt("course_hours");
String courseTea = rs.getString("course_teacher");
String major = rs.getString("major");
Date courseDate = rs.getDate("coursedate");
double scoreGrade = rs.getDouble("score_grade");
scoreCou = new ScoreCou(coursename, courseCredit, courseHours,
courseTea, major, courseDate, scoreGrade);
list.add(scoreCou);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public PageBean selectScoreSum(int pageNo, int pageCount) {
int totalCount = 0;
int i = 1;
List list = new ArrayList();
String sql = "select score.stu_num,score.stu_name,score.stu_class,score.major, sum(score.score_grade) as total,avg(score.score_grade) as avg "
+ "from score group by score.stu_num order by total DESC limit "
+ ((pageNo - 1) * pageCount) + "," + pageCount;
String sqlCount = "select count(*) from (select stu_num from score group by score.stu_num ) as a";
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
int number = i;
String stuNum = rs.getString("stu_num");
String stuName = rs.getString("stu_name");
String stuClass = rs.getString("stu_class");
String major = rs.getString("major");
double total = rs.getDouble("total");
double avg = rs.getDouble("avg");
scoreSum = new ScoreSum(number, stuNum, stuName, stuClass,
major, total, avg);
list.add(scoreSum);
i++;
}
rs = dbCon.find(sqlCount);
while (rs.next()) {
totalCount = rs.getInt(1);
}
pageBean = new PageBean(list, totalCount, pageNo, pageCount);
} catch (SQLException e) {
dbCon.close();
e.printStackTrace();
}
return pageBean;
}
public PageBean selectStuScore(int pageNo, int pageCount, Score score) {
int totalCount = 0;
List list = new ArrayList();
String sql = "select * from score where stu_num like '%"
+ score.getStuNum() + "%' and course_name like '%"
+ score.getCourseName() + "%' and stu_name like '%"
+ score.getStuName() + "%' and stu_class like '%"
+ score.getStuClass() + "%' and major like '%"
+ score.getMajor() + "%' limit " + ((pageNo - 1) * pageCount)
+ "," + pageCount;
String sqlCount = "select count(*) from score where stu_num like '%"
+ score.getStuNum() + "%' and course_name like '%"
+ score.getCourseName() + "%' and stu_name like '%"
+ score.getStuName() + "%' and stu_class like '%"
+ score.getStuClass() + "%' and major like '%"
+ score.getMajor() + "%'";
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
int scoreID = rs.getInt("score_id");
String stuNum = rs.getString("stu_num");
String stuName = rs.getString("stu_name");
String stuClass = rs.getString("stu_class");
String courseName = rs.getString("course_name");
double scoreGrade = rs.getDouble("score_grade");
String major = rs.getString("major");
score = new Score(scoreID, stuNum, stuName, stuClass,
courseName, scoreGrade, major);
list.add(score);
}
rs = dbCon.find(sqlCount);
while (rs.next()) {
totalCount = rs.getInt(1);
}
pageBean = new PageBean(list, totalCount, pageNo, pageCount);
} catch (SQLException e) {
dbCon.close();
e.printStackTrace();
}
return pageBean;
}
public PageBean selectTeaScore(int pageNo, int pageCount, String teaName,
Score score) {
int totalCount = 0;
List list = new ArrayList();
String sql = "select score.score_id,score.stu_num,score.stu_name,score.stu_class,score.course_name,score.score_grade,score.major from score,course where score.course_name=course.course_name and course.course_teacher='"
+ teaName
+ "' and score.stu_name like '%"
+ score.getStuName()
+ "%' and score.stu_class like '%"
+ score.getStuClass()
+ "%' and score.stu_num like '%"
+ score.getStuNum()
+ "%' limit "
+ ((pageNo - 1) * pageCount)
+ "," + pageCount;
String sqlCount = "select count(*) from score,course where score.course_name=course.course_name and course.course_teacher='"
+ teaName
+ "' and score.stu_name like '%"
+ score.getStuName()
+ "%' and score.stu_class like '%"
+ score.getStuClass()
+ "%' and score.stu_num like '%"
+ score.getStuNum()
+ "'";
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
int scoreID = rs.getInt("score_id");
String stuNum = rs.getString("stu_num");
String stuName = rs.getString("stu_name");
String stuClass = rs.getString("stu_class");
String courseName = rs.getString("course_name");
double scoreGrade = rs.getDouble("score_grade");
String major = rs.getString("major");
score = new Score(scoreID, stuNum, stuName, stuClass,
courseName, scoreGrade, major);
list.add(score);
}
rs = dbCon.find(sqlCount);
while (rs.next()) {
totalCount = rs.getInt(1);
}
pageBean = new PageBean(list, totalCount, pageNo, pageCount);
} catch (SQLException e) {
dbCon.close();
e.printStackTrace();
}
return pageBean;
}
}
package dao.Impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import model.PageBean;
import model.Student;
import dao.StudentDao;
import db.DBCon;
public class StudentDaoImpl implements StudentDao {
private DBCon dbCon = new DBCon();
private Student student;
private PageBean pageBean;
public int addStu(Student student) {
String sql = "insert into student(stu_num,stu_name,stu_sex,stu_age,stu_class,major,department) values('"
+ student.getStuNum()
+ "','"
+ student.getStuName()
+ "','"
+ student.getStuSex()
+ "',"
+ student.getStuAge()
+ ",'"
+ student.getStuClass()
+ "','"
+ student.getMajor()
+ "','"
+ student.getDepartment() + "')";
int rs = dbCon.query(sql);
return rs;
}
public int deleteStu(String stuNum) {
String sql = "delete from student where stu_num='" + stuNum + "'";
int rs = dbCon.query(sql);
return rs;
}
public int editStu(Student student) {
String sql = "update student set stu_name='" + student.getStuName()
+ "',stu_sex='" + student.getStuSex() + "',stu_age="
+ student.getStuAge() + ",stu_class='" + student.getStuClass()
+ "',major='" + student.getMajor() + "',department='"
+ student.getDepartment() + "' where stu_num='"
+ student.getStuNum() + "'";
int rs=dbCon.query(sql);
return rs;
}
public List findAll() {
List list = new ArrayList();
String sql = "select * from student";
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
int stuID = rs.getInt("stu_id");
String stuNum = rs.getString("stu_num");
String stuName = rs.getString("stu_name");
String stuSex = rs.getString("stu_sex");
int stuAge = rs.getInt("stu_age");
String stuClass = rs.getString("stu_class");
String major = rs.getString("major");
String department = rs.getString("department");
student = new Student(stuID, stuNum, stuName, stuSex, stuAge,
stuClass, major, department);
list.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public List findOne(String stuNum) {
List list = new ArrayList();
String sql="select * from student where stu_num='"+stuNum+"'";
ResultSet rs=dbCon.find(sql);
try {
while(rs.next()){
int stuID = rs.getInt("stu_id");
String stunum = rs.getString("stu_num");
String stuName = rs.getString("stu_name");
String stuSex = rs.getString("stu_sex");
int stuAge = rs.getInt("stu_age");
String stuClass = rs.getString("stu_class");
String major = rs.getString("major");
String department = rs.getString("department");
student = new Student(stuID, stunum, stuName, stuSex, stuAge,
stuClass, major, department);
list.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
//pageNo当前页码，pageCount每页显示总条数
public PageBean stuListPage(int pageNo, int pageCount) {
int totalCount=0;
List list = new ArrayList();
String sql="select * from student limit "+((pageNo-1)*pageCount)+","+pageCount;
String sqlCount="select count(*) from student";
try {
ResultSet rs=dbCon.find(sql);
while(rs.next()){
int stuID = rs.getInt("stu_id");
String stuNum = rs.getString("stu_num");
String stuName = rs.getString("stu_name");
String stuSex = rs.getString("stu_sex");
int stuAge = rs.getInt("stu_age");
String stuClass = rs.getString("stu_class");
String major = rs.getString("major");
String department = rs.getString("department");
student = new Student(stuID, stuNum, stuName, stuSex, stuAge,
stuClass, major, department);
list.add(student);
}
rs=dbCon.find(sqlCount);
while(rs.next()){
totalCount=rs.getInt(1);
}
pageBean=new PageBean(list , totalCount, pageNo, pageCount);
} catch (SQLException e) {
dbCon.close();
e.printStackTrace();
}
return pageBean;
}
}
package dao.Impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.PageBean;
import model.Student;
import model.Teacher;
import dao.TeacherDao;
import db.DBCon;
public class TeacherDaoImpl implements TeacherDao{
private DBCon dbCon = new DBCon();
private Teacher teacher;
private PageBean pageBean;
public int addTea(Teacher teacher) {
String sql = "insert into teacher(tea_num,tea_name,tea_sex,tea_age,tea_course,major,department) values('"
+ teacher.getTeaNum()
+ "','"
+ teacher.getTeaName()
+ "','"
+ teacher.getTeaSex()
+ "',"
+ teacher.getTeaAge()
+ ",'"
+ teacher.getTeaCourse()
+ "','"
+ teacher.getMajor()
+ "','"
+ teacher.getDepartment() + "')";
int rs = dbCon.query(sql);
return rs;
}
public int deleteTea(String teaNum) {
String sql = "delete from teacher where tea_num='" + teaNum + "'";
int rs = dbCon.query(sql);
return rs;
}
public int editTea(Teacher teacher) {
String sql = "update teacher set tea_name='" + teacher.getTeaName()
+ "',tea_sex='" + teacher.getTeaSex() + "',tea_age="
+ teacher.getTeaAge() + ",tea_course='" + teacher.getTeaCourse()
+ "',major='" + teacher.getMajor() + "',department='"
+ teacher.getDepartment() + "' where tea_num='"
+ teacher.getTeaNum() + "'";
int rs=dbCon.query(sql);
return rs;
}
public List findAll() {
List list=new ArrayList();
String sql="select * from teacher";
ResultSet rs=dbCon.find(sql);
try {
while (rs.next()) {
int teaID = rs.getInt("tea_id");
String teaNum = rs.getString("tea_num");
String teaName = rs.getString("tea_name");
String teaSex = rs.getString("tea_sex");
int teaAge = rs.getInt("tea_age");
String teaCourse = rs.getString("tea_course");
String major = rs.getString("major");
String department = rs.getString("department");
teacher = new Teacher(teaID, teaNum, teaName, teaSex, teaAge,
teaCourse, major, department);
list.add(teacher);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public List findOne(String teaNum) {
List list=new ArrayList();
String sql="select * from teacher where tea_num='"+teaNum+"'";
ResultSet rs=dbCon.find(sql);
try {
while (rs.next()) {
int teaID = rs.getInt("tea_id");
String teanum = rs.getString("tea_num");
String teaName = rs.getString("tea_name");
String teaSex = rs.getString("tea_sex");
int teaAge = rs.getInt("tea_age");
String teaCourse = rs.getString("tea_course");
String major = rs.getString("major");
String department = rs.getString("department");
teacher = new Teacher(teaID, teanum, teaName, teaSex, teaAge,
teaCourse, major, department);
list.add(teacher);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public PageBean teaListPage(int pageNo, int pageCount) {
List list=new ArrayList();
int totalCount=0;
String sql="select * from teacher limit "+((pageNo-1)*totalCount)+","+totalCount;
String sqlCount="select count(*) from teacher";
ResultSet rs=dbCon.find(sql);
try {
while(rs.next()){
int teaID = rs.getInt("tea_id");
String teanum = rs.getString("tea_num");
String teaName = rs.getString("tea_name");
String teaSex = rs.getString("tea_sex");
int teaAge = rs.getInt("tea_age");
String teaCourse = rs.getString("tea_course");
String major = rs.getString("major");
String department = rs.getString("department");
teacher = new Teacher(teaID, teanum, teaName, teaSex, teaAge,
teaCourse, major, department);
list.add(teacher);
}
rs=dbCon.find(sqlCount);
while(rs.next()){
totalCount=rs.getInt(1);
}
} catch (SQLException e) {
dbCon.close();
e.printStackTrace();
}
pageBean=new PageBean(list,totalCount,pageNo,pageCount);
return pageBean;
}
}
package dao.Impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.UsRole;
import model.User;
import dao.UserDao;
import db.DBCon;
public class UserDaoImpl implements UserDao {
private DBCon dbCon = new DBCon();
private User user;
private UsRole usRole;
public User login(User user) {
String sql = "select *from user where user_num='" + user.getUserNum()
+ "'";
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
int userID = rs.getInt("user_id");
String userNum = rs.getString("user_num");
String userName = rs.getString("user_name");
String pwd = rs.getString("password");
String phone = rs.getString("phone");
int roleID = rs.getInt("role_id");
user = new User(userID, userNum, userName, pwd, phone, roleID);
}
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
public int editPwd(User user) {
String sql = "update user set password='" + user.getPassword()
+ "' where user_num='" + user.getUserNum() + "'";
int rs = dbCon.query(sql);
return rs;
}
public int addUser(User user) {
String sql = "insert into user(user_num,user_name,password,phone,role_id) values('"
+ user.getUserNum()
+ "','"
+ user.getUserName()
+ "','"
+ user.getPassword()
+ "',"
+ user.getPhone()
+ ","
+ user.getRoleID() + ")";
int rs = dbCon.query(sql);
return rs;
}
public List findAll() {
List list = new ArrayList();
String sql = "select user_id,user_num,user_name,password,phone,role_name from user,role where user.role_id=role.role_id";
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
int userID = rs.getInt("user_id");
String userNum = rs.getString("user_num");
String userName = rs.getString("user_name");
String pwd = rs.getString("password");
String phone = rs.getString("phone");
String roleName = rs.getString("role_name");
usRole = new UsRole(userID, userNum, userName, pwd, phone,
roleName);
list.add(usRole);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public List selectOneUser(int userID) {
List list = new ArrayList();
String sql = "select * from user where user_id=" + userID;
ResultSet rs = dbCon.find(sql);
try {
while (rs.next()) {
int userid = rs.getInt("user_id");
String userNum = rs.getString("user_num");
String userName = rs.getString("user_name");
String pwd = rs.getString("password");
String phone = rs.getString("phone");
int roleID = rs.getInt("role_id");
user = new User(userID, userNum, userName, pwd, phone, roleID);
list.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public int editUser(User user) {
String sql = "update user set user_name='" + user.getUserName()
+ "',password=" + user.getPassword() + ",phone='"
+ user.getPhone() + "' where user_num='" + user.getUserNum()
+ "'";
int rs = dbCon.query(sql);
return rs;
}
}
package db;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class DBCon {
Connection con = null;
Statement st = null;
ResultSet rs = null;
String driver=null;
String url = null;
String username = null;
String password = null;
public Connection dbCon() {
try {
InputStream is=DBCon.class.getClassLoader().getResourceAsStream("db.properties");
Properties prop=new Properties();
try {
prop.load(is);
driver=prop.getProperty("driver");
url=prop.getProperty("url");
username=prop.getProperty("username");
password=prop.getProperty("password");
} catch (IOException e1) {
e1.printStackTrace();
}
Class.forName(driver);
/*Class.forName("com.mysql.jdbc.Driver");
url = "jdbc:mysql:///sams?useUnicode=true&characterEncoding=utf8";
username = "root";
password = "root";*/
try {
con = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return con;
}
/*
* 增删改
*/
public int query(String sql) {
int rs = 0;
con = dbCon();
try {
st = con.createStatement();
rs = st.executeUpdate(sql);
} catch (SQLException e) {
close();
e.printStackTrace();
}
return rs;
}
/*
* 查
*/
public ResultSet find(String sql) {
try {
con = dbCon();
st = con.createStatement();
rs = st.executeQuery(sql);
} catch (SQLException e) {
close();
e.printStackTrace();
}
return rs;
}
/*
* 关闭数据库
*/
public void close() {
try {
if (rs != null)
rs.close();
if (st != null)
st.close();
if (con != null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
注册页面