mysql 未选课的学生_《分级考试》试题——选课系统（未完成）

要求：包含学生，教师，管理员三个角色，以不同角色登录会进入不同的页面。学生可实现修改个人信息，浏览课程信息，选课(未完成)；教师可实现修改个人信息，添加课程信息，浏览选课学生信息(未完成)；管理员可实现添加学生信息，添加教师信息。最后是登录功能。

连接数据库的DBUtil.java

1 packageutil;2

3

4 importjava.sql.Connection;5 importjava.sql.DriverManager;6 importjava.sql.Statement;7 importjava.sql.SQLException;8 importjava.sql.ResultSet;9

10

11

12 public classDBUtil {13 public static String pr_url="jdbc:mysql://localhost:3306/lesson?useSSL=false";14 public static String pr_user="root";15 public static String pr_pass="Inazuma";16

17 public staticConnection getConn() {18 Connection conn=null;19 try{20 Class.forName("com.mysql.jdbc.Driver");21 conn=DriverManager.getConnection(pr_url, pr_user, pr_pass);22 }catch(Exception e) {23 e.printStackTrace();24 }25 returnconn;26 }27

28 public static void close(Statement state,Connection conn) throwsSQLException {29 if(state != null) {30 try{31 state.close();32 }catch(SQLException e) {33 e.printStackTrace();34 }35 }36

37 if(conn != null) {38 try{39 conn.close();40 } catch(SQLException e) {41 e.printStackTrace();42 }43 }44 }45

46 public static voidclose (ResultSet rs, Statement state, Connection conn) {47 if (rs != null) {48 try{49 rs.close();50 } catch(SQLException e) {51 e.printStackTrace();52 }53 }54

55 if (state != null) {56 try{57 state.close();58 } catch(SQLException e) {59 e.printStackTrace();60 }61 }62

63 if (conn != null) {64 try{65 conn.close();66 } catch(SQLException e) {67 e.printStackTrace();68 }69 }70 }71 }

负责数据库和jsp页面传值的Servlet类：LessonServlet.java

1 packageServlet;2 importjava.io.IOException;3

4 importjavax.servlet.ServletException;5 importjavax.servlet.annotation.WebServlet;6 importjavax.servlet.http.HttpServletRequest;7 importjavax.servlet.http.HttpServletResponse;8 importjavax.servlet.http.HttpServlet;9

10 importDao.LoginDao;11 importDao.AddTDao;12 importDao.AddSDao;13 importDao.AddCDao;14 importDao.UpdateTDao;15 importDao.UpdateSDao;16 importDao.UpdateCDao;17 @WebServlet("/LessonServlet")18 public class LessonServlet extendsHttpServlet{19 private static final long serialVersionUID = 1L;20

21 LoginDao login_dao=newLoginDao();22 AddTDao addt=newAddTDao();23 AddSDao adds=newAddSDao();24 AddCDao addc=newAddCDao();25 UpdateTDao upat=newUpdateTDao();26 UpdateSDao upas=newUpdateSDao();27 UpdateCDao upac=newUpdateCDao();28

29 protected void service(HttpServletRequest req,HttpServletResponse resp)throwsServletException,IOException{30 req.setCharacterEncoding("UTF-8");31 String method=req.getParameter("method");32 if("login".equals(method)) {33 login(req,resp);34 }else if("AddTeacher".equals(method)) {35 addT(req,resp);36 }else if("AddStudent".equals(method)) {37 addS(req,resp);38 }else if("AddClass".equals(method)) {39 addC(req,resp);40 }else if("updateT".equals(method)) {41 upaT(req,resp);42 }else if("updateS".equals(method)) {43 upaS(req,resp);44 }/*else if("select".equals(method)) {45 sele(req,resp);46 }*/

47 }48

49 public void login(HttpServletRequest req, HttpServletResponse resp) throwsIOException, ServletException {50 req.setCharacterEncoding("UTF-8");51 String user=req.getParameter("username");52 String pass=req.getParameter("password");53 String sele=req.getParameter("sel");54

55 int result=login_dao.select(user, pass, sele);56 if(result==0) {57 req.setAttribute("message", "登录失败");58 req.getRequestDispatcher("login.jsp").forward(req,resp);59 }else{60 if(sele.equals("教师")) {61 req.setAttribute("message", "登录成功");62 req.setAttribute("Judge", result);63 req.getRequestDispatcher("teacher.jsp").forward(req,resp);64 }else if(sele.equals("学生")) {65 req.setAttribute("message", "登录成功");66 req.setAttribute("JudgeS", result);67 req.getRequestDispatcher("student.jsp").forward(req,resp);68 }else if(sele.equals("管理员")) {69 req.setAttribute("message", "登录成功");70 req.getRequestDispatcher("guanli.jsp").forward(req,resp);71 }72 }73 }74

75 public void addT(HttpServletRequest req, HttpServletResponse resp) throwsIOException, ServletException {76 req.setCharacterEncoding("UTF-8");77 String num=req.getParameter("tnum");78 String name=req.getParameter("pname");79 String sex=req.getParameter("sex");80 String sch=req.getParameter("school");81 String zhi=req.getParameter("zhicheng");82

83 if(addt.tadd(num, name, sex, sch, zhi)) {84 req.setAttribute("message", "保存成功");85 req.getRequestDispatcher("guanli.jsp").forward(req,resp);86 } else{87 req.setAttribute("message", "保存失败");88 req.getRequestDispatcher("AddTeacher.jsp").forward(req,resp);89 }90 }91

92 public void addS(HttpServletRequest req, HttpServletResponse resp) throwsIOException, ServletException {93 req.setCharacterEncoding("UTF-8");94 String num=req.getParameter("snum");95 String name=req.getParameter("pname");96 String sex=req.getParameter("sex");97 String cla=req.getParameter("class");98 String zhu=req.getParameter("zhuanye");99

100 if(adds.sadd(num, name, sex, cla, zhu)) {101 req.setAttribute("message", "保存成功");102 req.getRequestDispatcher("guanli.jsp").forward(req,resp);103 } else{104 req.setAttribute("message", "保存失败");105 req.getRequestDispatcher("AddStudent.jsp").forward(req,resp);106 }107 }108

109 public void addC(HttpServletRequest req, HttpServletResponse resp) throwsIOException, ServletException {110 req.setCharacterEncoding("UTF-8");111 String cnum=req.getParameter("cnum");112 String name=req.getParameter("cname");113 String pnum=req.getParameter("pnum");114 String tname=req.getParameter("jiaoshi");115

116 if(addc.cadd(cnum, name, pnum, tname)) {117 req.setAttribute("message", "保存成功");118 req.getRequestDispatcher("teacher.jsp").forward(req,resp);119 } else{120 req.setAttribute("message", "保存失败");121 req.getRequestDispatcher("AddClass.jsp").forward(req,resp);122 }123 }124

125 public void upaT(HttpServletRequest req, HttpServletResponse resp) throwsIOException, ServletException {126 req.setCharacterEncoding("UTF-8");127 String num=req.getParameter("tnum");128 String name=req.getParameter("pname");129 String sex=req.getParameter("sex");130 String sch=req.getParameter("school");131 String zhi=req.getParameter("zhicheng");132

133 if(upat.tupd(name, sex, sch, zhi, num)&&upac.cupd(name, num)) {134 req.setAttribute("message", "修改成功");135 req.getRequestDispatcher("teacher.jsp").forward(req,resp);136 } else{137 req.setAttribute("message", "修改失败");138 req.getRequestDispatcher("UpadateT.jsp").forward(req,resp);139 }140 }141

142 public void upaS(HttpServletRequest req, HttpServletResponse resp) throwsIOException, ServletException {143 req.setCharacterEncoding("UTF-8");144 String num=req.getParameter("snum");145 String name=req.getParameter("pname");146 String sex=req.getParameter("sex");147 String cla=req.getParameter("class");148 String zhu=req.getParameter("zhuanye");149

150 if(upas.supd(name, sex, cla, zhu, num)) {151 req.setAttribute("message", "修改成功");152 req.getRequestDispatcher("student.jsp").forward(req,resp);153 } else{154 req.setAttribute("message", "修改失败");155 req.getRequestDispatcher("UpdateS.jsp").forward(req,resp);156 }157 }158 //未完成的选课模块

159 /*public void sele(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException {160 req.setCharacterEncoding("UTF-8");161 String id=req.getParameter("StudentId");162 String have=req.getParameter("have");163

164 if() {165 req.setAttribute("message", "选课成功");166 req.getRequestDispatcher("student.jsp").forward(req,resp);167 } else {168 req.setAttribute("message", "选课失败");169 req.getRequestDispatcher("student.jsp").forward(req,resp);170 }171 }*/

172 }

由于jsp页面中提交信息方式都是以form表单格式提交，会一并传回来method值，根据method值是什么来执行相应的方法，比如登录界面的jsp表单，method值为“login”，在该Servlet文件中判定后执行相应方法(行32，33).

注：由于jsp页面并没有进行美化设计，只写了最基础的,

下面介绍登录界面：

界面

相应Dao包中的代码：LoginDao.java

1 packageDao;2

3 importjava.sql.Connection;4 importjava.sql.ResultSet;5 importjava.sql.Statement;6 importutil.DBUtil;7

8 public classLoginDao {9 public intselect(String username,String password,String sel) {10 int result=0;11 Connection conn=DBUtil.getConn();12 Statement state=null;13 String sql="select * from ";14 if(sel.equals("教师")){15 sql+="teacher";16 }else if(sel.equals("学生")) {17 sql+="student";18 }else if(sel.equals("管理员")) {19 sql+="guanli";20 }21 ResultSet res=null;22 try{23 state=conn.createStatement();24 res=state.executeQuery(sql);25 while(res.next()) {26 String JudgeUsername=res.getString("username");27 String JudgePassword=res.getString("password");28 int id=res.getInt("id");29 if(JudgeUsername.equals(username)&&JudgePassword.equals(password)) {30 result=id;31 break;32 }33 }34 }catch(Exception e) {35 e.printStackTrace();36 }finally{37 DBUtil.close(res, state, conn);38 }39 returnresult;40 }41 }

jsp界面中通过中"radio"类型传递参数，根据参数判断登录的是哪个角色，之后在数据库中根据角色开始核实用户名与密码，成功后在LessonServlet.java中跳转到相应的jsp页面。

下面给出不同角色的功能页，功能跳转都通过标签实现：

学生：

老师：

管理员：

添加学生信息页面：

添加教师信息页面：

这两个添加信息实现方法类似，同样是jsp向后台提交表单，审核method值，执行相应方法，向数据库添加信息也是类似，只不过操作的数据库不一样。由于涉及登录功能，因此添加的信息中，用户名默认是工号/学号，密码默认123

Dao包文件：

添加学生：

1 packageDao;2

3 importjava.sql.Connection;4 importjava.sql.SQLException;5 importjava.sql.Statement;6 importutil.DBUtil;7

8 public classAddSDao {9 public booleansadd(String num,String name,String sex,String cla,String zhu) {10 boolean f=false;11 String user=num;12 String pass="123";13 String sql="insert into student(username,password,name,sex,class,snum,zhuanye)"+

14 "values('"+user+"','"+pass+"','"+name+"','"+sex+"','"+cla+"','"+num+"','"+zhu+"')";15 Connection conn =DBUtil.getConn();16 Statement state = null;17 int a = 0;18 try{19 state =conn.createStatement();20 a=state.executeUpdate(sql);21 }catch(Exception e) {22 e.printStackTrace();23 }finally{24 try{25 DBUtil.close(state, conn);26 } catch(SQLException e) {27 e.printStackTrace();28 }29 }30

31 if (a > 0) {32 f = true;33 }34 returnf;35 }36 }

添加教师：

1 packageDao;2

3 importjava.sql.Connection;4 importjava.sql.SQLException;5 importjava.sql.Statement;6 importutil.DBUtil;7

8 public classAddTDao {9 public booleantadd(String num,String name,String sex,String sch,String zhi) {10 boolean f=false;11 String user=num;12 String pass="123";13 String sql="insert into teacher(username,password,name,sex,school,tnum,zhicheng)"+

14 "values('"+user+"','"+pass+"','"+name+"','"+sex+"','"+sch+"','"+num+"','"+zhi+"')";15 Connection conn =DBUtil.getConn();16 Statement state = null;17 int a = 0;18 try{19 state =conn.createStatement();20 a=state.executeUpdate(sql);21 }catch(Exception e) {22 e.printStackTrace();23 }finally{24 try{25 DBUtil.close(state, conn);26 } catch(SQLException e) {27 e.printStackTrace();28 }29 }30

31 if (a > 0) {32 f = true;33 }34 returnf;35 }36 }

接下来是添加课程信息，这个和上述两个相比都要简单一些

页面：

Dao包文件：

1 packageDao;2

3 importjava.sql.Connection;4 importjava.sql.SQLException;5 importjava.sql.Statement;6 importutil.DBUtil;7

8 public classAddCDao {9 public booleancadd(String cnum,String name,String pnum,String tname) {10 boolean f=false;11 String sql="insert into class(name,cnum,pnum,teacher)"+

12 "values('"+name+"','"+cnum+"','"+pnum+"','"+tname+"')";13 Connection conn =DBUtil.getConn();14 Statement state = null;15 int a = 0;16 try{17 state =conn.createStatement();18 a=state.executeUpdate(sql);19 }catch(Exception e) {20 e.printStackTrace();21 }finally{22 try{23 DBUtil.close(state, conn);24 } catch(SQLException e) {25 e.printStackTrace();26 }27 }28

29 if (a > 0) {30 f = true;31 }32 returnf;33 }34 }

接下来是修改个人信息的页面和相关代码实现：

学生：

登录时会将数据库内记录该角色的id一并传出，在修改时会根据id找到相应的角色信息，如图中显示出来，只要改动后点击按钮提交便可更改数据库中相应信息。

Dao包文件：

1 packageDao;2

3 importutil.DBUtil;4 importjava.sql.PreparedStatement;5 importjava.sql.Connection;6

7 public classUpdateSDao {8 public booleansupd(String name,String sex,String cla,String zhu,String snum) {9 boolean f=false;10 try{11 Connection connu=DBUtil.getConn();12 PreparedStatement sqlu=connu.prepareStatement("update student set name=?,sex=?,class=?,zhuanye=? where snum=?");13 sqlu.setString(1,name);14 sqlu.setString(2,sex);15 sqlu.setString(3,cla);16 sqlu.setString(4,zhu);17 sqlu.setString(5,snum);18 int a=sqlu.executeUpdate();19 if(a>0) {20 f=true;21 }22 sqlu.close();23 connu.close();24 }catch(Exception e) {25 e.printStackTrace();26 }27 returnf;28 }29 }

老师：

实现原理同上 ，但注意，教师信息姓名一旦更改，在浏览课程信息时，相应的教师名称也会修改，因此它也涉及到记录课程的数据库的修改。

Dao包文件:

老师：

1 packageDao;2

3 importutil.DBUtil;4 importjava.sql.PreparedStatement;5 importjava.sql.Connection;6

7 public classUpdateTDao {8 public booleantupd(String name,String sex,String sch,String zhi,String tnum) {9 boolean f=false;10 try{11 Connection connu=DBUtil.getConn();12 PreparedStatement sqlu=connu.prepareStatement("update teacher set name=?,sex=?,school=?,zhicheng=? where tnum=?");13 sqlu.setString(1,name);14 sqlu.setString(2, sex);15 sqlu.setString(3,sch);16 sqlu.setString(4,zhi);17 sqlu.setString(5, tnum);18 int a=sqlu.executeUpdate();19 if(a>0) {20 f=true;21 }22 sqlu.close();23 connu.close();24 }catch(Exception e) {25 e.printStackTrace();26 }27 returnf;28 }29 }

课程：

1 packageDao;2

3 importutil.DBUtil;4 importjava.sql.PreparedStatement;5 importjava.sql.Connection;6

7 public classUpdateCDao {8 public booleancupd(String name,String tnum) {9 boolean f=false;10 try{11 Connection connu=DBUtil.getConn();12 PreparedStatement sqlu=connu.prepareStatement("update class set teacher=? where tnum=?");13 sqlu.setString(1,name);14 sqlu.setString(2, tnum);15 int a=sqlu.executeUpdate();16 if(a>0) {17 f=true;18 }19 sqlu.close();20 connu.close();21 }catch(Exception e) {22 e.printStackTrace();23 }24 returnf;25 }26 }

这里课程更改需要工号作为判断标准，更改相应工号的教师姓名。

最后是浏览课程信息，这里连接数据库操作直接在jsp页面内写的，因此附上jsp代码

1

2 pageEncoding="UTF-8"%>

3

4

5

6

7

8

浏览课程信息

9

10 table{

11 margin:auto;

12 }

13

14

15

16

17 try{18 Class.forName("com.mysql.jdbc.Driver");19 Stringurl="jdbc:mysql://localhost:3306/lesson?useSSL=false";20 Stringuser="root";21 Stringpass="Inazuma";22 Connection conn=DriverManager.getConnection(url,user,pass);23 if(conn!=null){24 out.print("课程信息");25 out.print("
");26 %>

27

28

29

课程名称

30

课程编号

31

任课教师

32

33

34 Statement state=null;35 ResultSet res=null;36 Stringsql="select * from class";37 state=conn.createStatement();38 res=state.executeQuery(sql);39 while(res.next()){40 %>

41

42

">

43

44

">

45

46

47 }48 }else{49 out.print("连接失败");50 }51 }catch(Exception e){52 e.printStackTrace();53 }54 %>

55

56 返回

57

58