《分级考试》试题——选课系统(未完成)
要求:包含学生,教师,管理员三个角色,以不同角色登录会进入不同的页面。学生可实现修改个人信息,浏览课程信息,选课(未完成);教师可实现修改个人信息,添加课程信息,浏览选课学生信息(未完成);管理员可实现添加学生信息,添加教师信息。最后是登录功能。
连接数据库的DBUtil.java
1 package util; 2 3 4 import java.sql.Connection; 5 import java.sql.DriverManager; 6 import java.sql.Statement; 7 import java.sql.SQLException; 8 import java.sql.ResultSet; 9 10 11 12 public class DBUtil { 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 static Connection 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 return conn; 26 } 27 28 public static void close(Statement state,Connection conn) throws SQLException { 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 void close (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 package Servlet; 2 import java.io.IOException; 3 4 import javax.servlet.ServletException; 5 import javax.servlet.annotation.WebServlet; 6 import javax.servlet.http.HttpServletRequest; 7 import javax.servlet.http.HttpServletResponse; 8 import javax.servlet.http.HttpServlet; 9 10 import Dao.LoginDao; 11 import Dao.AddTDao; 12 import Dao.AddSDao; 13 import Dao.AddCDao; 14 import Dao.UpdateTDao; 15 import Dao.UpdateSDao; 16 import Dao.UpdateCDao; 17 @WebServlet("/LessonServlet") 18 public class LessonServlet extends HttpServlet{ 19 private static final long serialVersionUID = 1L; 20 21 LoginDao login_dao=new LoginDao(); 22 AddTDao addt=new AddTDao(); 23 AddSDao adds=new AddSDao(); 24 AddCDao addc=new AddCDao(); 25 UpdateTDao upat=new UpdateTDao(); 26 UpdateSDao upas=new UpdateSDao(); 27 UpdateCDao upac=new UpdateCDao(); 28 29 protected void service(HttpServletRequest req,HttpServletResponse resp)throws ServletException,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) throws IOException, 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) throws IOException, 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) throws IOException, 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) throws IOException, 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) throws IOException, 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) throws IOException, 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页面并没有进行美化设计,只写了最基础的<input>,<table>之类的,故只会展示界面,不给出jsp代码。主要展示的是Dao包中的代码。
下面介绍登录界面:
界面
相应Dao包中的代码:LoginDao.java
1 package Dao; 2 3 import java.sql.Connection; 4 import java.sql.ResultSet; 5 import java.sql.Statement; 6 import util.DBUtil; 7 8 public class LoginDao { 9 public int select(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 return result; 40 } 41 }
jsp界面中通过<input>中"radio"类型传递参数,根据参数判断登录的是哪个角色,之后在数据库中根据角色开始核实用户名与密码,成功后在LessonServlet.java中跳转到相应的jsp页面。
下面给出不同角色的功能页,功能跳转都通过<a>标签实现:
学生:
老师:
管理员:
添加学生信息页面:
添加教师信息页面:
这两个添加信息实现方法类似,同样是jsp向后台提交表单,审核method值,执行相应方法,向数据库添加信息也是类似,只不过操作的数据库不一样。由于涉及登录功能,因此添加的信息中,用户名默认是工号/学号,密码默认123
Dao包文件:
添加学生:
1 package Dao; 2 3 import java.sql.Connection; 4 import java.sql.SQLException; 5 import java.sql.Statement; 6 import util.DBUtil; 7 8 public class AddSDao { 9 public boolean sadd(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 return f; 35 } 36 }
添加教师:
1 package Dao; 2 3 import java.sql.Connection; 4 import java.sql.SQLException; 5 import java.sql.Statement; 6 import util.DBUtil; 7 8 public class AddTDao { 9 public boolean tadd(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 return f; 35 } 36 }
接下来是添加课程信息,这个和上述两个相比都要简单一些
页面:
Dao包文件:
1 package Dao; 2 3 import java.sql.Connection; 4 import java.sql.SQLException; 5 import java.sql.Statement; 6 import util.DBUtil; 7 8 public class AddCDao { 9 public boolean cadd(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 return f; 33 } 34 }
接下来是修改个人信息的页面和相关代码实现:
学生:
登录时会将数据库内记录该角色的id一并传出,在修改时会根据id找到相应的角色信息,如图中显示出来,只要改动后点击按钮提交便可更改数据库中相应信息。
Dao包文件:
1 package Dao; 2 3 import util.DBUtil; 4 import java.sql.PreparedStatement; 5 import java.sql.Connection; 6 7 public class UpdateSDao { 8 public boolean supd(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 return f; 28 } 29 }
老师:
实现原理同上 ,但注意,教师信息姓名一旦更改,在浏览课程信息时,相应的教师名称也会修改,因此它也涉及到记录课程的数据库的修改。
Dao包文件:
老师:
1 package Dao; 2 3 import util.DBUtil; 4 import java.sql.PreparedStatement; 5 import java.sql.Connection; 6 7 public class UpdateTDao { 8 public boolean tupd(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 return f; 28 } 29 }
课程:
1 package Dao; 2 3 import util.DBUtil; 4 import java.sql.PreparedStatement; 5 import java.sql.Connection; 6 7 public class UpdateCDao { 8 public boolean cupd(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 return f; 25 } 26 }
这里课程更改需要工号作为判断标准,更改相应工号的教师姓名。
最后是浏览课程信息,这里连接数据库操作直接在jsp页面内写的,因此附上jsp代码
1 <%@ page language="java" contentType="text/html; charset=UTF-8" 2 pageEncoding="UTF-8"%> 3 <%@ page import="java.sql.*" %> 4 <!DOCTYPE html> 5 <html> 6 <head> 7 <meta charset="UTF-8"> 8 <title>浏览课程信息</title> 9 <style type="text/css"> 10 table{ 11 margin:auto; 12 } 13 </style> 14 </head> 15 <body style="text-align:center;"> 16 <% 17 try{ 18 Class.forName("com.mysql.jdbc.Driver"); 19 String url="jdbc:mysql://localhost:3306/lesson?useSSL=false"; 20 String user="root"; 21 String pass="Inazuma"; 22 Connection conn=DriverManager.getConnection(url,user,pass); 23 if(conn!=null){ 24 out.print("课程信息"); 25 out.print("<br/>"); 26 %> 27 <table border="2"> 28 <tr> 29 <td>课程名称</td> 30 <td>课程编号</td> 31 <td>任课教师</td> 32 </tr> 33 <% 34 Statement state=null; 35 ResultSet res=null; 36 String sql="select * from class"; 37 state=conn.createStatement(); 38 res=state.executeQuery(sql); 39 while(res.next()){ 40 %> 41 <tr> 42 <td><a href="SeeC.jsp?C=<%=res.getString("id")%>"><%=res.getString("name") %></a></td> 43 <td><%=res.getString("cnum") %></td> 44 <td><a href="SeeT.jsp?T=<%=res.getString("tnum")%>"><%=res.getString("teacher") %></a></td> 45 </tr> 46 <% 47 } 48 }else{ 49 out.print("连接失败"); 50 } 51 }catch(Exception e){ 52 e.printStackTrace(); 53 } 54 %> 55 </table> 56 <button type="submit"onclick="window.location.href='student.jsp'">返回</button> 57 </body> 58 </html>
从上述页面中可以看出,课程名称和教师名称是<a>标签格式,是可以点击的,点击后会弹出相应课程或教师的详细信息,实现原理同样是在jsp中嵌入java脚本,只不过与上述循环遍历不同,在遍历中加入了附加条件(行42与44传递的参数),遍历时会进行判断。满足条件才能显示出来,下面给出页面:
最后是选课界面,与浏览课程界面类似,由于后台功能没有编写完成,只给出界面:
.