学生选课系统v1.0
最近两天写了下老师课上留的作业:学生选课系统。感觉自己写的特别麻烦,思路特别不清晰,平常自己总会偷懒,一些太麻烦细节的功能就不去实现了,用简单的功能来替代,直到自己这回写完这个系统(但自己写的比较low,只有后台功能,前台几乎没有),发现一些功能虽然繁琐,但多写几次就不会感到麻烦,反而自己的思维会变得更清楚。
系统:三种用户:管理员,教师,学生。管理员来添加教师和学生,教师可以添加自己的课程,查看选课学生的信息,修改个人信息,学生可以实现选课,浏览课程信息,修改个人信息等。
主要思路:通过将用户的账号和密码同数据库相应的数据进行比对验证登陆,用户登陆时选择自己的身份。将学生,老师,课程,选课信息分别储存在数据库中。人数达到上限后改变课程的状态。重复选课后台给出提示(这个写的不太好,应在前台告知客户),不添加数据到数据库中。
这里只给出核心的代码(dao层),其余可以自己比较简单。
package dao; import java.sql.connection; import java.sql.preparedstatement; import java.sql.resultset; import java.sql.sqlexception; import java.sql.statement; import java.util.arraylist; import java.util.list; import javax.servlet.http.httpservletrequest; import util.dbutil; import bean.de; import bean.lesson; import bean.student; import bean.teacher; public class function { // 验证用户是否存在 public static boolean search(string stunumber, string code, string id, httpservletrequest request) { int temp = 0; connection conn = dbutil.getconn(); string sql = null; statement sta = null; if (id.contentequals("老师")) { sql = "select * from teacher"; } else { sql = "select * from student"; } try { sta = conn.createstatement(); resultset res = sta.executequery(sql); while (res.next()) { string pn = res.getstring("num"); string name = res.getstring("name"); request.setattribute("na", name); if (stunumber.equals(pn) && code.equals("123")) { temp = 1; break; } } res.close(); } catch (exception e) { e.printstacktrace(); } finally { dbutil.close(sta, conn); } if (temp == 0) return false; else return true; } // 增加老师数据 public static void addt(string stunumber, string name, string sex, string college, string department) throws sqlexception { string sql = "insert into teacher(num,name,sex,college,job) values('" + stunumber + "','" + name + "','" + sex + "','" + college + "','" + department + "')"; connection conn = dbutil.getconn(); conn.setautocommit(false); preparedstatement preparedstatement = conn.preparestatement(sql); preparedstatement.executeupdate(); conn.commit(); conn.close(); } // 增加学生数据 public static void adds(string stunumber, string name, string sex, string college, string department) throws sqlexception { string sql = "insert into student(num,name,sex,class,major) values('" + stunumber + "','" + name + "','" + sex + "','" + college + "','" + department + "')"; connection conn = dbutil.getconn(); conn.setautocommit(false); preparedstatement preparedstatement = conn.preparestatement(sql); preparedstatement.executeupdate(); conn.commit(); conn.close(); } // 增加课程信息 public static void addc(string stunumber, string name, string sex, string college) throws sqlexception { string sql = "insert into kecheng(number,name,people,teacher) values('" + stunumber + "','" + name + "','" + sex + "','" + college + "')"; connection conn = dbutil.getconn(); conn.setautocommit(false); preparedstatement preparedstatement = conn.preparestatement(sql); preparedstatement.executeupdate(); conn.commit(); conn.close(); } // 修改老师信息 public static void updatat(string name, string sex, string college, string de) throws sqlexception { string sql = "update teacher set sex='" + sex + "',college='" + college + "',job='" + de + "' where name='" + name + "'"; connection conn = dbutil.getconn(); conn.setautocommit(false); preparedstatement preparedstatement = conn.preparestatement(sql); preparedstatement.executeupdate(); conn.commit(); conn.close(); } // 修改学生数据 public static void updatas(string name, string sex, string college, string de) throws sqlexception { string sql = "update student set sex='" + sex + "',class='" + college + "',major='" + de + "' where name='" + name + "'"; connection conn = dbutil.getconn(); conn.setautocommit(false); preparedstatement preparedstatement = conn.preparestatement(sql); preparedstatement.executeupdate(); conn.commit(); conn.close(); } // 返回全部课程信息 public static list<de> getde_1() throws sqlexception { list<de> list = new arraylist<de>(); string sql = "select * from kecheng "; connection conn = dbutil.getconn(); try { // 连接对象coon调用createstatement()方法,创建一个执行sql语句的对象st statement st = conn.createstatement(); // 执行sql语句中的对象st调用executequery()方法,执行查询语句,将查询到的结果返回到一个结果集中 resultset rs = st.executequery(sql); // 遍历结果集对象 while (rs.next()) { de vol = new de(); vol.setnumber(rs.getstring("number")); vol.setname(rs.getstring("name")); vol.setpeople(rs.getint("people")); vol.setteacher(rs.getstring("teacher")); list.add(vol); } } catch (exception e) { system.out.println(e.getmessage()); } finally { if (conn != null) { conn.close(); } } return list; } // 返回可选课程信息 public static list<de> getde() throws sqlexception { list<de> list = new arraylist<de>(); string sql = "select * from kecheng where status='0' "; connection conn = dbutil.getconn(); try { // 连接对象coon调用createstatement()方法,创建一个执行sql语句的对象st statement st = conn.createstatement(); // 执行sql语句中的对象st调用executequery()方法,执行查询语句,将查询到的结果返回到一个结果集中 resultset rs = st.executequery(sql); // 遍历结果集对象 while (rs.next()) { de vol = new de(); vol.setnumber(rs.getstring("number")); vol.setname(rs.getstring("name")); vol.setpeople(rs.getint("people")); vol.setteacher(rs.getstring("teacher")); list.add(vol); } } catch (exception e) { system.out.println(e.getmessage()); } finally { if (conn != null) { conn.close(); } } return list; } // 返回老师详细信息 public static teacher getteacheer(string name) throws sqlexception { teacher te = new teacher(); string sql = "select * from teacher where name='" + name + "' "; connection conn = dbutil.getconn(); try { statement st = conn.createstatement(); resultset rs = st.executequery(sql); if (rs.next()) { te.setnum(rs.getstring("num")); te.setname(rs.getstring("name")); te.setsex(rs.getstring("sex")); te.setcollege(rs.getstring("college")); te.setjob(rs.getstring("job")); } } catch (exception e) { system.out.println(e.getmessage()); } finally { if (conn != null) { conn.close(); } } return te; } // 学生选课信息 public static void stu_lesson(string s_name, string le_number) throws sqlexception { string lesson_number = le_number; string lesson_name = null; string teacher_name = null; string teacher_number = null; string stu_name = s_name; string stu_num = null; connection conn = null; int a = 0; int count = 0; boolean temp = true; try { string sql = "select * from chooseke where lesson_num='" + le_number + "' and stu_name='" + s_name + "' "; conn = dbutil.getconn(); // 连接对象coon调用createstatement()方法,创建一个执行sql语句的对象st statement st = conn.createstatement(); // 执行sql语句中的对象st调用executequery()方法,执行查询语句,将查询到的结果返回到一个结果集中 resultset rs = st.executequery(sql); // 遍历结果集对象 if (rs.next()) { temp = false; } if (temp == true) { sql = "select * from kecheng where number='" + le_number + "' "; rs = st.executequery(sql); if (rs.next()) { lesson_name = rs.getstring("name"); teacher_name = rs.getstring("teacher"); } sql = "select * from teacher where name='" + teacher_name + "' "; rs = st.executequery(sql); // 遍历结果集对象 if (rs.next()) { teacher_number = rs.getstring("num"); } sql = "select * from student where name='" + stu_name + "' "; rs = st.executequery(sql); // 遍历结果集对象 if (rs.next()) { stu_num = rs.getstring("num"); } sql = "select * from kecheng where name='" + lesson_name + "' "; rs = st.executequery(sql); if (rs.next()) { a = rs.getint("people"); } sql = "select * from chooseke where lesson_name='" + lesson_name + "' "; rs = st.executequery(sql); while (rs.next()) { count++; system.out.println(count); } if (count < a) { sql = "insert into chooseke(lesson_num,lesson_name,teacher_num,teacher_name,stu_number,stu_name) values('" + lesson_number + "','" + lesson_name + "','" + teacher_number + "','" + teacher_name + "','" + stu_num + "','" + stu_name + "')"; conn.setautocommit(false); preparedstatement preparedstatement = conn.preparestatement(sql); preparedstatement.executeupdate(); conn.commit(); if ((a - count) == 1) { sql = "update kecheng set status='-1' where name='" + lesson_name + "' "; conn.setautocommit(false); preparedstatement = conn.preparestatement(sql); preparedstatement.executeupdate(); conn.commit(); } } else { system.out.println("人数已达上限,不可选"); } } else { system.out.println("与已选课冲突"); } } catch (exception e) { system.out.println(e.getmessage()); } finally { if (conn != null) { conn.close(); } } } // 返回课程详细信息 public static void getlesson(string le_name, httpservletrequest req) throws sqlexception { string lesson_name = le_name; string lesson_number = null; string teacher_name = null; string teacher_number = null; connection conn = null; lesson les = new lesson(); int a = 0; int count = 0; try { string sql = "select * from kecheng where name='" + le_name + "' "; conn = dbutil.getconn(); // 连接对象coon调用createstatement()方法,创建一个执行sql语句的对象st statement st = conn.createstatement(); // 执行sql语句中的对象st调用executequery()方法,执行查询语句,将查询到的结果返回到一个结果集中 resultset rs = st.executequery(sql); // 遍历结果集对象 if (rs.next()) { lesson_number = rs.getstring("number"); teacher_name = rs.getstring("teacher"); a = rs.getint("people"); } sql = "select * from teacher where name='" + teacher_name + "' "; rs = st.executequery(sql); if (rs.next()) { teacher_number = rs.getstring("num"); } sql = "select * from chooseke where lesson_num='" + lesson_number + "' "; rs = st.executequery(sql); while (rs.next()) { count++; } } catch (exception e) { system.out.println(e.getmessage()); } finally { if (conn != null) { conn.close(); } } les.setlesson_number(lesson_number); les.setlesson_name(lesson_name); les.setteacher_number(teacher_number); les.setteacher_name(teacher_name); les.setpe_num(count); les.setmessage(a); req.setattribute("les", les); } // 返回老师对应的课程 public static list<de> getteacher_lesson(string name) throws sqlexception { list<de> list = new arraylist<de>(); string sql = "select * from kecheng where teacher='" + name + "' "; connection conn = dbutil.getconn(); try { // 连接对象coon调用createstatement()方法,创建一个执行sql语句的对象st statement st = conn.createstatement(); // 执行sql语句中的对象st调用executequery()方法,执行查询语句,将查询到的结果返回到一个结果集中 resultset rs = st.executequery(sql); // 遍历结果集对象 while (rs.next()) { de vol = new de(); vol.setnumber(rs.getstring("number")); vol.setname(rs.getstring("name")); list.add(vol); } } catch (exception e) { system.out.println(e.getmessage()); } finally { if (conn != null) { conn.close(); } } return list; } // 返回选择该课程学生的信息 public static list<student> getstu(string number) throws sqlexception { list<student> list = new arraylist<student>(); string sql = "select * from chooseke where lesson_num= '" + number + "'"; connection conn = dbutil.getconn(); try { // 连接对象coon调用createstatement()方法,创建一个执行sql语句的对象st statement st = conn.createstatement(); // 执行sql语句中的对象st调用executequery()方法,执行查询语句,将查询到的结果返回到一个结果集中 resultset rs = st.executequery(sql); // 遍历结果集对象 while (rs.next()) { student sd = new student(); sd.setname(rs.getstring("stu_name")); sd.setnum(rs.getstring("stu_number")); list.add(sd); } } catch (exception e) { system.out.println(e.getmessage()); } finally { if (conn != null) { conn.close(); } } return list; } // 返回单个学生的详细信息 public static student get_stu(string number) throws sqlexception { student sd = new student(); string sql = "select * from student where num= '" + number + "'"; connection conn = dbutil.getconn(); try { // 连接对象coon调用createstatement()方法,创建一个执行sql语句的对象st statement st = conn.createstatement(); // 执行sql语句中的对象st调用executequery()方法,执行查询语句,将查询到的结果返回到一个结果集中 resultset rs = st.executequery(sql); // 遍历结果集对象 if (rs.next()) { sd.setnum(rs.getstring("num")); sd.setname(rs.getstring("name")); sd.setsex(rs.getstring("sex")); sd.setcla(rs.getstring("class")); sd.setmajor(rs.getstring("major")); } } catch (exception e) { system.out.println(e.getmessage()); } finally { if (conn != null) { conn.close(); } } return sd; } /** * 删除数据 */ public static void delete(string stunumber) throws sqlexception { string sql = "delete from stumanage where stumanage.stunumber = '" + stunumber + "'"; connection conn = dbutil.getconn(); conn.setautocommit(false); preparedstatement preparedstatement = conn.preparestatement(sql); preparedstatement.executeupdate(); conn.commit(); conn.close(); } /** * 查找数据 */ public static void query() throws sqlexception { string sql = "select * from stumanage"; connection conn = dbutil.getconn(); conn.setautocommit(false); preparedstatement preparedstatement = conn.preparestatement(sql); // 执行查询语句并返回结果集 resultset resultset = preparedstatement.executequery(); while (resultset.next()) { // 注意:这里要与数据库里的字段对应 string stunumber = resultset.getstring("stunumber"); string code = resultset.getstring("code"); system.out.println(stunumber + " " + code); } conn.commit(); conn.close(); } /** * 查找特定数据 */ public static void query(string stu) throws sqlexception { string sql = "select * from stumanage"; connection conn = dbutil.getconn(); conn.setautocommit(false); preparedstatement preparedstatement = conn.preparestatement(sql); // 执行查询语句并返回结果集 resultset resultset = preparedstatement.executequery(); while (resultset.next()) { // 注意:这里要与数据库里的字段对应 string stunumber = resultset.getstring("stunumber"); if (stu.contentequals(stunumber)) { } } conn.commit(); conn.close(); } }
通过这次的练习,自己的思维逻辑变得更清楚,对类的思想理解更为透彻,同时自己对jstl便签使用的也更加熟练,也认识到了el表达式的方便,但自己也发现了自己的好多问题,比如自己在上述代码中主要用的
是statement,而不是preparedstatement,这会有数据库漏洞,自己还发现在用mysql数据库时,在一个函数中只能连接数据库一次,不可多次连接关闭,只能通过不断地sql语句进行一系列操作,还有对于一些request中放置的attribute属性要注意其消亡时间。
自己的系统还有很多地方完善,自己对于web知识掌握还很浅显,自己还需要长期的努力。
上一篇: 【深度学习-语音分类】语种识别挑战赛Baseline
下一篇: 基于人脸识别的课堂签到管理系统