1.功能要求
- 查询学生选课信息,在原有学生信息界面上,加入选课界面
- 进入选课界面后,有所有课程的信息与该学生是否选择某一课程的信息
- 在选课界面,学生可更改或增加其他课程的选课信息
功能实现案例如下:
2.stucour表
2.1 student表和course表关系
一个学生可以选择多个课程,一个课程也可以被多个学生选择,所以学生和课程是多对多的关系,因此需要新建一个stucour表,表示学生选课关系
2.2 stucour属性
3.JSP文件
3.1stulist.jsp
- 增加一栏"选课"框
代码如下:
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8" import ="java.util.*,student.*"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Stu_list</title>
</head>
<body>
<form action="${ request.contextPath }/StuCour/StuListServlet" method="post">
学号:<input type="text" name="id" <c:if test="${ requestScope.stu.id ne -1}"> value="${ requestScope.stu.id }"</c:if> /><br />
姓名:<input type="text" name="name" value="${ requestScope.stu.name }"><br />
性别:<select name=sex>
<option value="" selected></option>
<option value="男" <c:if test="${ requestScope.stu.sex eq '男'}">selected</c:if> >男</option>
<option value="女" <c:if test="${ requestScope.stu.sex eq '女'}">selected </c:if> >女</option>
</select>
年龄:<input type="text" name="age" <c:if test="${ requestScope.stu.age ne -1}"> value="${ requestScope.stu.age }"</c:if>"><br />
专业:<select name="majorId">
<option value=""> </option>
<c:forEach items="${ requestScope.majors }" var="major">
<option value="${ major.id }" <c:if test="${ major.id eq requestScope.stu.major.id}">selected</c:if> >${ major.name }</option>
</c:forEach>
</select><br />
<input type="submit" value="搜索"/><input type="reset" />
</form>
<table border = "1">
<tr> <th>学号</th> <th>姓名</th> <th>性别</th><th>年龄</th><th>专业</th><th>operation</th></tr>
<c:forEach items="${requestScope.stus}" var="stu">
<tr>
<td>${ stu.id }</td>
<td>${ stu.name }</td>
<td>${ stu.sex }</td>
<td>${ stu.age }</td>
<td>${ stu.major.name }</td>
<td>
<a href="${request.contextPath}/StuCour/StuUpdServlet?id=${stu.id}"> 编辑</a>
<a href="${request.contextPath}/StuCour/StuDelServlet?id=${stu.id}"> 删除</a>
<a href="${request.contextPath}/StuCour/StuCourServlet?id=${stu.id}"> 选课</a>
</td>
</tr>
</c:forEach>
</table>
<a href="${request.contextPath}/StuCour/StuAddServlet">insert student</a>
</body>
</html>
3.2 stu_cour.jsp文件
- 显示所有课程信息
- 是否选择课程复选框
- 提交修改信息
具体代码如下:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>学生选课</title>
</head>
<body>
<form action="${request.contextPath}/StuCour/StuCourServlet" method="post">
<input type="hidden" name="stuId" value="${requestScope.stu.id}">
<table border="1">
<tr>
<th></th>
<th>课程号</th>
<th>课程名</th>
<th>教师</th>
</tr>
<c:forEach items="${requestScope.cours}" var = "cour">
<tr>
<td> <input type="checkbox" name="selCours" value="${cour.id}" <c:if test="${cour.selected eq 1}">checked</c:if>></td>
<td>${cour.id}</td>
<td>${cour.name}</td>
<td>${cour.teacher}</td>
</tr>
</c:forEach>
</table>
<input type="submit" value="选课">
</form>
</body>
</html>
4.course.Class文件
- 增加一个判断是否被选择的信息
package course.model;
public class Course {
private int id = -1;
private String name;
private String teacher;
//选课模块记录是否被选择,(0,1),0未选,1已选
private String selected;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getTeacher() {
return teacher;
}
public void setTeacher(String teacher) {
this.teacher = teacher;
}
public String getSelected() {
return selected;
}
public void setSelected(String selected) {
this.selected = selected;
}
}
5.StuCourServlet的doGet方法
- 接收学生id
- 查询该学生选课信息
- 传入所有课程,该学生id,该学生选课信息
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//接收学生id
String stuId = request.getParameter("id");
Student stu =new Student();
stu.setId(Integer.parseInt(stuId));
//根据学生id查询该学生选课信息
StuCourService stuCourService = new StuCourServiceImp();
List <Course> selectedCourses = stuCourService.ListCourByStu(stu);
//查询所有课程
CourService courService = new CourServiceImp();
List <Course> cours = courService.listAll();
//遍历所有课程,如果学生选课在所有课程中,就将对应课程的selected属性设置为“1”
for(Course cour:cours)
{
cour.setSelected("0");
for(Course selCour:selectedCourses)
{
if(cour.getId()==selCour.getId())
{
cour.setSelected("1");
}
}
}
//传入信息,因为涉及到后续的保存修改选课信息,所以这里仍然需要存入学生id信息
request.setAttribute("stu", stu);
request.setAttribute("cours", cours);
request.getRequestDispatcher("stucour/stu_cour.jsp").forward(request, response);
}
6.StuCourDao的方法规划
- 根据学生查询该学生选课信息
- 根据课程查询所有选择该课程的学生
- 添加学生选课信息
- 把某一学生的选课信息全部删除
- 把某一学生和课程的选课关系删除
- 把选择该课程的学生全部删除
package stucour.dao;
import java.util.List;
import course.model.Course;
import student.model.Student;
public interface StuCourDao {
//根据学生查询该学生选择课程
public List <Course> ListCourByStu(Student stu);
//根据课程查学生,学生还有筛选功能
public List <Student> ListStuByCour(Course cour,Student stu);
//添加学生选课信息
public boolean save(Student stu,Course cour);
//把某个学生的课全部删除
public boolean delByStu(Student stu);
//删除学生和课程对应信息
public boolean del(Student stu,Course cour);
//删除某个课程的全部学生
public boolean delByCour(Course cour);
}
7.StuCourDaoImp的具体实现
package stucour.dao.imp;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import config.DataSource;
import course.dao.CourDao;
import course.dao.imp.*;
import course.model.Course;
import major.model.Major;
import stucour.dao.StuCourDao;
import student.dao.StuDao;
import student.dao.imp.StuDaoImp;
import student.model.Student;
import student.service.StuService;
import student.service.imp.StuServiceImp;
public class StuCourDaoImp implements StuCourDao{
@Override
public List<Course> ListCourByStu(Student stu) {
// TODO Auto-generated method stub
Connection con = null;
ArrayList cours =new ArrayList();
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DataSource.url ,DataSource.username ,DataSource.password);
Statement st = con.createStatement();
CourDao courDao = new CourDaoImp();
String sql1 = "select * from stucour where student_id="+stu.getId();
ResultSet rs = st.executeQuery(sql1);
while(rs.next())
{
int courseId = rs.getInt("course_id");
Course cour = courDao.listById(courseId);
cours.add(cour);
}
return cours;
}catch(Exception e){
e.printStackTrace();
return cours;
}finally {
try {
con.close();
}catch(Exception e1)
{
e1.printStackTrace();
}
}
}
@Override
public List<Student> ListStuByCour(Course cour, Student stu) {
// TODO Auto-generated method stub
Connection con = null;
ArrayList <Student> stus = new ArrayList <Student> ();
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DataSource.url ,DataSource.username ,DataSource.password);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from stucour where course_id="+cour.getId());
StuService stuService = new StuServiceImp();
while(rs.next())
{
int studentId = rs.getInt("student_id");
stu.setId(studentId);
List<Student> stu1s = stuService.list(stu);
for(Student stu1:stu1s)
{
stus.add(stu1);
}
}
}catch(Exception e)
{
e.printStackTrace();
}
try {
con.close();
}catch (Exception e1) {
e1.printStackTrace();
}
return stus;
}
@Override
public boolean save(Student stu, Course cour) {
// TODO Auto-generated method stub
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DataSource.url ,DataSource.username ,DataSource.password);
String sql = "insert into stucour(student_id,course_id) values(?,?)";
PreparedStatement pst = con.prepareStatement(sql);
pst.setInt(1,stu.getId());
pst.setInt(2,cour.getId());
return pst.execute();
}catch(Exception e)
{
e.printStackTrace();
return false;
}finally {
try{con.close();}
catch(Exception e1)
{
e1.printStackTrace();
}
}
}
@Override
public boolean delByStu(Student stu) {
// TODO Auto-generated method stub
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DataSource.url ,DataSource.username ,DataSource.password);
Statement st = con.createStatement();
st.execute("delete from stucour where student_id ="+stu.getId());
return true;
}catch(Exception e)
{
e.printStackTrace();
return false;
}finally {
try{con.close();}
catch(Exception e1)
{
e1.printStackTrace();
}
}
}
@Override
public boolean del(Student stu, Course cour) {
// TODO Auto-generated method stub
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DataSource.url ,DataSource.username ,DataSource.password);
Statement st = con.createStatement();
String sql ="delete from stucour where student_id = ? and course_id = ?";
PreparedStatement pst = con.prepareStatement(sql);
pst.setInt(1, stu.getId());
pst.setInt(2, cour.getId());
pst.execute();
return true;
}catch(Exception e)
{
e.printStackTrace();
return false;
}finally {
try{con.close();}
catch(Exception e1)
{
e1.printStackTrace();
}
}
}
@Override
public boolean delByCour(Course cour) {
// TODO Auto-generated method stub
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DataSource.url ,DataSource.username ,DataSource.password);
Statement st = con.createStatement();
st.execute("delete from stucour where course_id ="+cour.getId());
return true;
}catch(Exception e)
{
e.printStackTrace();
return false;
}finally {
try{con.close();}
catch(Exception e1)
{
e1.printStackTrace();
}
}
}
}
8.StuCourServlet的doPost方法
- 读取学生id和课程id
- 删除该学生所选课程信息
- 保存新选课信息
- 返回主界面
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
String stuId = request.getParameter("stuId");
String [] courIds = request.getParameterValues("selCours");
///不能直接保存,因为本来就有
///先删除,再保存
///所以必须封在同一个事务中(本项目不考虑,但是其他的还需要改进)
StuCourService stuCourService = new StuCourServiceImp();
Student stu = new Student();
stu.setId(Integer.parseInt(stuId));
stuCourService.delByStu(stu);
Course cour = new Course();
for(String courId:courIds)
{
cour.setId(Integer.parseInt(courId));
stuCourService.save(stu, cour);
}
response.sendRedirect("StuListServlet");
}
9.作业
实现课程界面的选课信息
- 在所有课程信息的后面加入已选该课程学生信息和未选该课程学生信息的按钮
- 进入已选该课程学生信息界面时,要求上方仍有条件查询,每个学生右侧有退选功能
- 进入未选该课程学生信息界面时,上方也有条件查询,每个学生右侧有选课功能
- 美化界面