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;
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
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;
}
}
ScoreDaoImpl
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;
}
}
StudentDaoImpl
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;
}
}
TeacherDaoImpl
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;
}
}
UserDaoImpl
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;
}
}
DBCon
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;
最后
这份《“java高分面试指南”-25分类227页1000+题50w+字解析》同样可分享给有需要的朋友，感兴趣的伙伴们可挑战一下自我，在不看答案解析的情况，测试测试自己的解题水平，这样也能达到事半功倍的效果！（好东西要大家一起看才香）
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;
}
}
DBCon
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;
最后
这份《“java高分面试指南”-25分类227页1000+题50w+字解析》同样可分享给有需要的朋友，感兴趣的伙伴们可挑战一下自我，在不看答案解析的情况，测试测试自己的解题水平，这样也能达到事半功倍的效果！（好东西要大家一起看才香）
[外链图片转存中…(img-VFP1kYuy-1725077019684)]
[外链图片转存中…(img-vAKJCvDv-1725077019685)]
605
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
