Java+Servlet+JSP+Mysql+Tomcat实现Web学生选课管理系统_javaweb选课系统源代码

list.add(course);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

DBUtils.closeAll(rs, preparedStatement, connection);

}

return list;

}

@Override

public List findSelectableCourse(int sno) {

Connection connection = null;

PreparedStatement preparedStatement = null;

ResultSet rs = null;

List list = new ArrayList();

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 getSelectedStudentAndCourse(int tno) {

Connection connection = null;

PreparedStatement preparedStatement = null;

ResultSet rs = null;

List list = new ArrayList();

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);

}

}

StudentDaoImpl.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.StudentDao;

import com.bluehonour.sscs.entity.ClassInfo;

import com.bluehonour.sscs.entity.CriteriaStudent;

import com.bluehonour.sscs.entity.Student;

import com.bluehonour.sscs.util.DBUtils;

public class StudentDaoImpl implements StudentDao {

@Override

public int save(Student stu) {

String sql = “insert into t_student(password,sname,phone,sex,birthday,classno,remark) values(?,?,?,?,?,?,?)”;

Object[] params = { stu.getPassword(), stu.getSname(), stu.getPhone(), stu.getSex(), stu.getBirthday(),

stu.getClassno(), stu.getRemark() };

return DBUtils.executeUpdate(sql, params);

}

@Override

public List findAll() {

Connection connection = null;

PreparedStatement preparedStatement = null;

ResultSet rs = null;

Student student = null;

List stuList = new ArrayList();

try {

// 建立连接

connection = DBUtils.getConnection();

// 向数据库发送sql命令并得到结果

String sql = “select * from t_student”;

preparedStatement = connection.prepareStatement(sql);

rs = preparedStatement.executeQuery();

// 处理返回结果

while (rs.next()) {

// 取出结果集当前行各个字段的值

int sno = rs.getInt(“sno”);

String password = rs.getString(“password”);

String sname = rs.getString(“sname”);

long phone = rs.getLong(“phone”);

String sex = rs.getString(“sex”);

Date birthday = rs.getDate(“birthday”);

int classno = rs.getInt(“classno”);

String remark = rs.getString(“remark”);

// 封装成对象

student = new Student(sno,password, sname, phone, sex, birthday, classno, remark);

stuList.add(student);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

DBUtils.closeAll(rs, preparedStatement, connection);

}

return stuList;

}

@Override

public int del(int sno) {

String sql = “delete from t_student where sno = ?”;

Object[] params = {sno };

return DBUtils.executeUpdate(sql, params);

}

@Override

public Student findById(int sno) {

Connection connection = null;

PreparedStatement preparedStatement = null;

ResultSet rs = null;

Student student = null;

try {

// 建立连接

connection = DBUtils.getConnection();

// 向数据库发送sql命令并得到结果

String sql = "select * from t_student where sno = " + sno;

preparedStatement = connection.prepareStatement(sql);

rs = preparedStatement.executeQuery();

// 处理返回结果

if (rs.next()) {

// 取出结果集当前行各个字段的值

String password = rs.getString(“password”);

String sname = rs.getString(“sname”);

long phone = rs.getLong(“phone”);

String sex = rs.getString(“sex”);

Date birthday = rs.getDate(“birthday”);

int classno = rs.getInt(“classno”);

String remark = rs.getString(“remark”);

// 封装成对象

student = new Student(sno,password, sname, phone, sex, birthday, classno, remark);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

DBUtils.closeAll(rs, preparedStatement, connection);

}

return student;

}

@Override

public int update(Student stu) {

String sql = “update t_student set sname=?,password=?,phone=?,birthday=?,sex=?,classno=?,remark=? where sno=?”;

Object[] params = { stu.getSname(),stu.getPassword(),stu.getPhone(),stu.getBirthday(),stu.getSex(),stu.getClassno(),

stu.getRemark(),stu.getSno() };

return DBUtils.executeUpdate(sql, params);

}

@Override

public Student find(String sno, String password) {

Connection connection = null;

PreparedStatement preparedStatement = null;

ResultSet rs = null;

Student student = null;

try {

//建立连接

connection = DBUtils.getConnection();

//向数据库发送sql命令并得到结果

String sql = “select * from t_student where sno = ? and password = ?”;

preparedStatement = connection.prepareStatement(sql);

preparedStatement.setString(1, sno);

preparedStatement.setString(2, password);

rs = preparedStatement.executeQuery();

//处理返回结果

if(rs.next()) {

//取出结果集当前行各个字段的值

String sname = rs.getString(“sname”);

long phone = rs.getLong(“phone”);

String sex = rs.getString(“sex”);

Date birthday = rs.getDate(“birthday”);

int classno = rs.getInt(“classno”);

String remark = rs.getString(“remark”);

//封装成对象

student = new Student(Integer.parseInt(sno), password, sname, phone, sex, birthday, classno, remark);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

//关闭数据库资源

DBUtils.closeAll(rs, preparedStatement, connection);

}

return student;

}

@Override

public List getClassInfo() {

Connection connection = null;

PreparedStatement preparedStatement = null;

ResultSet rs = null;

ClassInfo clazz = null;

List list = new ArrayList();

try {

//建立连接

connection = DBUtils.getConnection();

//向数据库发送sql命令并得到结果

String sql = “select * from t_class”;

preparedStatement = connection.prepareStatement(sql);

rs = preparedStatement.executeQuery();

//处理返回结果

while(rs.next()) {

//取出结果集当前行各个字段的值

int classno = rs.getInt(“classno”);

String cname = rs.getString(“cname”);

String cteacher = rs.getString(“cteacher”);

String classroom = rs.getString(“classroom”);

//封装成对象

clazz = new ClassInfo(classno, cname, cteacher, classroom);

list.add(clazz);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

//关闭数据库资源

DBUtils.closeAll(rs, preparedStatement, connection);

}

return list;

}

@Override

public List getForListWithCriteriaStudent(CriteriaStudent student) {

Connection connection = null;

PreparedStatement preparedStatement = null;

ResultSet rs = null;

List stuList = new ArrayList();

try {

// 建立连接

connection = DBUtils.getConnection();

// 向数据库发送sql命令并得到结果

StringBuffer sql = new StringBuffer();

sql.append(“select * from t_student”);

if(!student.getSno().equals(“”)) {

sql.append(" and sno like ‘%“+ student.getSno() +”%’");

}

if(!student.getSname().equals(“”)) {

sql.append(" and sname like ‘%“+ student.getSname() +”%’");

}

if(!student.getSex().equals(“”)) {

sql.append(" and sex =‘“+ student.getSex() +”’");

}

if(!student.getClassno().equals(“”)) {

sql.append(" and classno like ‘%“+ student.getClassno() +”%’");

}

if(!student.getRemark().equals(“”)) {

sql.append(" and remark=‘“+ student.getRemark() +”’");

}

String SQL = sql.toString();

SQL = SQL.replaceFirst(“and”, “where”);

System.out.println(SQL);

preparedStatement = connection.prepareStatement(SQL);

rs = preparedStatement.executeQuery();

// 处理返回结果

while (rs.next()) {

// 取出结果集当前行各个字段的值

int sno = rs.getInt(“sno”);

String password = rs.getString(“password”);

String sname = rs.getString(“sname”);

long phone = rs.getLong(“phone”);

String sex = rs.getString(“sex”);

Date birthday = rs.getDate(“birthday”);

int classno = rs.getInt(“classno”);

String remark = rs.getString(“remark”);

// 封装成对象

Student stu = new Student(sno,password, sname, phone, sex, birthday, classno, remark);

stuList.add(stu);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

DBUtils.closeAll(rs, preparedStatement, connection);

}

return stuList;

}

}

TeacherCourseDaoImpl.javab

---

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.TeacherCourseDao;

import com.bluehonour.sscs.entity.Course;

import com.bluehonour.sscs.entity.Teacher;

import com.bluehonour.sscs.util.DBUtils;

public class TeacherCourseDaoImpl implements TeacherCourseDao {

@Override

public int save(int cno, int tno) {

String sql = “insert into t_tc values(?,?)”;

Object[] params = {cno,tno};

return DBUtils.executeUpdate(sql, params);

}

@Override

public int delete(int cno, int tno) {

String sql = “delete from t_tc where cno = ? and tno = ?”;

Object[] params = {cno,tno};

return DBUtils.executeUpdate(sql, params);

}

@Override

public List findAll() {

Connection connection = null;

PreparedStatement preparedStatement = null;

ResultSet rs = null;

List list = new ArrayList();

try {

// 建立连接

connection = DBUtils.getConnection();

// 向数据库发送sql命令并得到结果

String sql = “select * from t_course c”

• " join t_tc tc"

• " on (c.cno = tc.cno)"

• " join t_teacher t"

• " on (tc.tno = t.tno)"

• " order by c.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);

//取出结果集中教师各个字段的值

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;

}

}

TeacherDaoImpl.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.TeacherDao;

import com.bluehonour.sscs.entity.Course;

import com.bluehonour.sscs.entity.Student;

import com.bluehonour.sscs.entity.Teacher;

import com.bluehonour.sscs.util.DBUtils;

public class TeacherDaoImpl implements TeacherDao {

@Override

public int save(Teacher teacher) {

String sql = "insert into t_teacher(tname,password,phone,hiredate,remark) values(?,?,?,?,?) ";

Object[] params = {teacher.getTname(),teacher.getPassword(),teacher.getPhone(),teacher.getHiredate(),teacher.getRemark()};

return DBUtils.executeUpdate(sql, params);

}

@Override

public List findAll() {

Connection connection = null;

PreparedStatement preparedStatement = null;

ResultSet rs = null;

List list = new ArrayList();

try {

// 建立连接

connection = DBUtils.getConnection();

// 向数据库发送sql命令并得到结果

String sql = “select * from t_teacher order by tno”;

preparedStatement = connection.prepareStatement(sql);

rs = preparedStatement.executeQuery();

// 处理返回结果

while (rs.next()) {

// 取出结果集当前行各个字段的值

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);

list.add(teacher);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

DBUtils.closeAll(rs, preparedStatement, connection);

}

return list;

}

@Override

public int delete(int tno) {

String sql = “delete from t_teacher where tno = ?”;

Object[] params = {tno };

return DBUtils.executeUpdate(sql, params);

}

@Override

public Teacher findById(int tno) {

Connection connection = null;

PreparedStatement preparedStatement = null;

ResultSet rs = null;

Teacher teacher = null;

try {

// 建立连接

connection = DBUtils.getConnection();

// 向数据库发送sql命令并得到结果

String sql = "select * from t_teacher where tno = " + tno;

preparedStatement = connection.prepareStatement(sql);

rs = preparedStatement.executeQuery();

// 处理返回结果

if (rs.next()) {

// 取出结果集当前行各个字段的值

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 = new Teacher(tno, tname, password, phone, hiredate, remark);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

DBUtils.closeAll(rs, preparedStatement, connection);

}

return teacher;

}

@Override

public int update(Teacher teacher) {

String sql = “update t_teacher set tname=?,password=?,phone=?,hiredate=?,remark=? where tno=?”;

Object[] params = {teacher.getTname(),teacher.getPassword(),teacher.getPhone(),teacher.getHiredate(),teacher.getRemark(),teacher.getTno()};

return DBUtils.executeUpdate(sql, params);

}

@Override

public List getAssumeCourse(int tno) {

Connection connection = null;

PreparedStatement preparedStatement = null;

ResultSet rs = null;

Course course = null;

List list = new ArrayList<>();

try {

// 建立连接

connection = DBUtils.getConnection();

// 向数据库发送sql命令并得到结果

String sql = "select c.* from t_tc tc " +

"LEFT JOIN t_teacher t on t.tno = tc.tno " +

"LEFT JOIN t_course c on c.cno = tc.cno " +

"where tc.tno = " + tno ;

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 = new Course(cno,name, credit, periodstart, periodend);

list.add(course);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

DBUtils.closeAll(rs, preparedStatement, connection);

}

return list;

}

}

addAdmin.jsp

---

<%@ page language=“java” contentType=“text/html; charset=UTF-8”

pageEncoding=“UTF-8”%>

<%@ taglib prefix=“c” uri=“http://java.sun.com/jsp/jstl/core” %>

<%-- ${error }

--%>

<%

if(request.getAttribute(“error”) != null){

%>

<%

} else{

%>

<%

}

%>

管理员账号： <input type=“text” name=“userId” id=“userId”">请输入4-10位用户名

真实姓名： <input type=“text” name=“userName”

id=“userName” value=“”>请输入您的真实姓名

密码： <input type=“password” name=“passWord”

id=“passWord” value=“” size=“20px”>密码为6-16位

确认密码： <input type=“password” name=“rePassWord”

id=“rePassWord” value=“” size=“20px”>请再次输入密码

年龄： 请输入年龄

成绩： 请输入成绩

入职时间： <input type=“text” name=“enterDate”

id=“enterDate” value=“” οnfοcus=“WdatePicker({highLineWeekDay:true,isShowToday:true,isShowWeek:true})”>请输入入职时间

简介：

addCourse.jsp

---

<%@ page language=“java” contentType=“text/html; charset=UTF-8”

pageEncoding=“UTF-8”%>

<%@ taglib prefix=“c” uri=“http://java.sun.com/jsp/jstl/core” %>

<%

if(request.getAttribute(“error”) != null){

%>

<%

} else{

%>

<%

}

%>

课程名称：

学分：

开课日期:

结课日期:

${error }

addStudent.jsp

---

<%@ page language=“java” contentType=“text/html; charset=UTF-8”

pageEncoding=“UTF-8”%>

<%@ taglib prefix=“c” uri=“http://java.sun.com/jsp/jstl/core” %>

<%

if(request.getAttribute(“error”) != null){

%>

<%

} else{

%>

<%

}

%>

学生姓名：

请输入学生姓名

密码：

密码为6-16位

班级：

--请选择班级--

<c:forEach items=“${classList }” var=“clazz”>

--${clazz.classno}班--

</c:forEach>

请输入班级

性别：

男

女

关联手机号:

请输入手机号

出生年月日:

请输入出生年月日

评论:

--请选择-- 优秀 良好 合格 差劲

请对该学生进行评论

${error }

addTeacher.jsp

---

<%@ page language=“java” contentType=“text/html; charset=UTF-8”

pageEncoding=“UTF-8”%>

<%@ taglib prefix=“c” uri=“http://java.sun.com/jsp/jstl/core”%>

href=“<c:url value=‘/css/add.css’/>”>