jsp+servlet+jdbc 学生教务选课系统 使用数据库连接池

package user.dao; import java.io.UnsupportedEncodingException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Vector; import user.bean.student; import user.bean.user; public class StudentDAO extends UserDAO { public StudentDAO() { super(); } public StudentDAO (Connection conn) { super(conn); } public void save(user user) throws SQLException{ student student = (student) user; String sql; sql = "INSERT INTO stu_basic VALUES('" + student.getId()+ "','" + student.getName() + "','" + student.getPw() + "','" + student.getXueyuan() + "','" + student.getZhuanye() + "','" +student.getGrade()+"','" + student.getBanji() +"','" +student.getId()+"');"; System.out.println(sql); db.executeUpdate(sql); System.out.print(8765); } public student getStudent(String id) { String sql; student stu = new student(); ResultSet rs; sql = "SELECT * FROM stu_basic WHERE stu_id='" + id + "';"; try { rs = db.executeQuery(sql); if(rs.next()){ stu.setId(rs.getString("stu_id")); stu.setName(rs.getString("stu_name")); stu.setPw(rs.getString("stu_pw")); stu.setXueyuan(rs.getString("stu_xueyuan")); stu.setZhuanye(rs.getString("stu_zhuanye")); stu.setGrade(rs.getString("stu_grade")); stu.setBanji(rs.getString("stu_class")); stu.setCourseId(rs.getString("stu_coursetable")); return stu; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } public Vector<student> getAllStudents() throws SQLException { Vector<student> students = new Vector<student>(); String sql = "select * from stu_basic"; ResultSet rs; rs = db.executeQuery(sql); while(rs.next()) { student stu = new student(); stu.setId(rs.getString("stu_id")); stu.setName(rs.getString("stu_name")); stu.setPw(rs.getString("stu_pw")); stu.setXueyuan(rs.getString("stu_xueyuan")); stu.setZhuanye(rs.getString("stu_zhuanye")); stu.setGrade(rs.getString("stu_grade")); stu.setBanji(rs.getString("stu_class")); stu.setCourseId(rs.getString("stu_coursetable")); students.addElement(stu); } return students; } public Vector<student> getClassStudents(int classNum) throws SQLException { Vector<student> students = new Vector<student>(); String sql = "select * from stu_basic where stu_class = "+classNum+";"; ResultSet rs; rs = db.executeQuery(sql); while(rs.next()) { student stu = new student(); stu.setId(rs.getString("stu_id")); stu.setName(rs.getString("stu_name")); stu.setPw(rs.getString("stu_pw")); stu.setXueyuan(rs.getString("stu_xueyuan")); stu.setZhuanye(rs.getString("stu_zhuanye")); stu.setGrade(rs.getString("stu_grade")); stu.setBanji(rs.getString("stu_class")); stu.setCourseId(rs.getString("stu_coursetable")); students.addElement(stu); } return students; } public String[] getStudentIDsOfClass(int classNumber) throws SQLException { String sql; sql = "SELECT COUNT(*) FROM stu_basic WHERE stu_class = " + classNumber + ";"; ResultSet rs = db.executeQuery(sql); int count = -1; if (rs.next()) { count = rs.getInt(1); } sql = "SELECT stu_id FROM stu_baic WHERE stu_class = " + classNumber + ";"; rs = db.executeQuery(sql); String[] studentIDs = new String[count]; int i = 0; while (rs.next()) { studentIDs[i] = rs.getString(1); i ++; } return studentIDs; } /** * @author Yanbing Bai * @param ID * @param name * @param gradeNum * @param classNum * @return An instance of Vector containing the query results * @version * @throws SQLException * @throws UnsupportedEncodingException */ public Vector<student> queryStudents(String ID ,String name,String gradeNum,String classNum) throws SQLException, UnsupportedEncodingException{ //Get an container for the query result Vector result = new Vector(); //Modify the ID for the sql ID = "%"+ID+"%"; name = "%"+name+"%"; gradeNum = "%"+gradeNum+"%"; classNum = "%"+classNum+"%"; //Combine the params into a sql sentense String sql = "select stu_id,stu_name,stu_pw,stu_xueyuan,stu_zhuanye,stu_class,stu_coursetable from " + "stu_basic where stu_id like '"+ID+"' && name like '"+name+"' " + "&& stu_grade like '"+gradeNum+"' && stu_class like '"+classNum+"'"; //Query ResultSet rs = this.db.executeQuery(sql); //Get the results while(rs.next()){ //Create an instance of StudentBean student stu = new student(); //Set the attributes of the instance of StudentBean stu.setId(new String(rs.getBytes("ID"),"GB2312")); stu.setName(new String(rs.getBytes("name"),"GB2312")); stu.setPw(new String(rs.getBytes("stu_pw"),"")); try{ stu.setGrade(rs.getString(5)); stu.setBanji(rs.getString(6)); } catch(NumberFormatException e){ //If Exception occurs set the number with 0 stu.setGrade(""); stu.setBanji(""); } //Add the instance of StudentBean into container result.add(stu); } //Return the container return result; } public void update(user user) throws SQLException { student student = ( student ) user; String sql = "UPDATE student SET stu_name='" + student.getName() +"'," + "stu_pw='" + student.getPw() + "' WHERE stu_id='" + student.getId() + "'"; db.executeUpdate(sql); System.out.println(sql); } public void update(String id,String newpw) throws SQLException { //student student = ( student ) user; String sql ="UPDATE stu_basic SET stu_pw='" + newpw +"' WHERE stu_id='" + id + "'"; System.out.println(sql); db.executeUpdate(sql); } public void update(String id,String name,String pw,String xueyuan,String zhuanye,String grade,String banji) throws SQLException { //student student = ( student ) user; String sql ="UPDATE stu_basic SET stu_name='" + name +"',stu_pw='"+pw+"'," + "stu_zhuanye='"+zhuanye+"',stu_grade='"+grade+"',stu_class='"+banji+"',stu_coursetable='"+id+"' WHERE stu_id='" + id + "'"; System.out.println(s