最近兩天寫了下老師課上留的作業：學生選課系統，感覺自己寫的特別麻煩，思路特別不清晰，平常自己總會偷懶，一些太麻煩細節的功能就不去實作了，用簡單的功能來替代，直到自己這回寫完這個系統(但自己寫的比較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知識掌味訓很淺顯，自己還需要長期的努力，
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/88266.html
標籤：Html/Css