JSP，SQL，MVC的选课系统

package dao; import java.sql.*; public class DBaccess { String url = "jdbc:mysql://localhost:3306/hxy1"; String user = "root"; String DbPassword = "111"; String gb = "charset=GBK"; static int cid = 10; static int scid=1; public DBaccess() { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { System.out.println("文件没有找到"); e.printStackTrace(); } } // 获取数据库的链接 public Connection getConnection() { Connection conn = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(url, user, DbPassword); } catch (SQLException e) { System.out.println("SQL 异常"); e.printStackTrace(); } catch (ClassNotFoundException e) { System.out.println("文件没有找到"); e.printStackTrace(); } return conn; } // 关闭数据库的链接 public void closeConnection(Connection conn) { try { if (conn != null) { conn.close(); } } catch (Exception e) { e.printStackTrace(); } } public void studentAddCourse(Connection conn, int i,String id1, String cid1) { try { String id = id1; String cid = cid1; if(i==1) { scid++; } String insert = "insert into scourse values(?,?,?);"; PreparedStatement prestatement = conn.prepareStatement(insert); prestatement.setInt(1, scid); prestatement.setString(2, id); prestatement.setString(3, cid); prestatement.executeUpdate(); } catch (SQLException e1) { System.out.println("SQL 异常"); e1.printStackTrace(); } } // 当学生选课或者删课的时候，剩余人数的改变 public void remain(Connection conn, String cid1, int i) { try { Statement comm = conn.createStatement(); String sql = "select *from course where cid='" + cid1 + "'"; int cremain = 0; ResultSet rs = comm.executeQuery(sql); while (rs.next()) { cremain = rs.getInt("cremain"); } if (i == 1) { cremain--; } else { cremain++; } String sql2 = "update course set cremain=? where cid=? ;"; PreparedStatement prestatement = conn.prepareStatement(sql2); prestatement.setInt(1, cremain); prestatement.setString(2, cid1); prestatement.executeUpdate(); } catch (SQLException e1) { System.out.println("SQL 异常"); e1.printStackTrace(); } } // 当两个教室相同的时候：产生冲突 public int classclash(Connection conn, String id1, String cid1) { try { String classa = null; String classb = null; String id=id1; String cid=cid1; Statement comm1 = conn.createStatement(); String sql1 = "select cclassroom from course where cid='" + cid + "'"; ResultSet rs = comm1.executeQuery(sql1); while (rs.next()) { classa = rs.getString("cclassroom"); } String sql2 = "select course.cclassroom from course,scourse where course.cid=scourse.cid and scourse.id='" + id + "'"; Statement comm2 = conn.createStatement(); ResultSet rs2 = comm2.executeQuery(sql2); while (rs2.next()) { classb = rs2.getString("cclassroom"); if (classb.equals(classa)) { return 0; } } } catch (SQLException e1) { System.out.println("SQL 异常"); e1.printStackTrace(); } return 1; } // 学生在选课的时候 删除一个课程 须输入学生的学号以及课程的id public void studentDeleteCourse(Connection conn, String id1, String cid1) { try { String id = id1; String cid = cid1; String delete = "delete from scourse where id='" + id + "' and cid='"+ cid + "';"; PreparedStatement prestatement = conn.prepareStatement(delete); prestatement.executeUpdate(); } catch (SQLException e1) { System.out.println("SQL 异常"); e1.printStackTrace(); } } // 在管理员处 添加一个课程的信息 输入课程的信息 public void adminAddCourse(Connection conn, String cid1, String cname1, String ctime1, String cteacher1, String cclassroom1, String cnumber1, int cremain1) { try { String cid = cid1; String cname = cname1; String ctime = ctime1; String cteacher = cteacher1; String cclassroom = cclassroom1; String cnumber = cnumber1; int cremain = cremain1; String insert = "insert into course values(?,?,?,?,?,?,?);"; PreparedStatement prestatement = conn.prepareStatement(insert); prestatement.setString(1, cid); prestatement.setString(2, cname); prestatement.setString(3, ctime); prestatement.setString(4, cteacher); prestatement.setString(5, cclassroom); prestatement.setString(6, cnumber); prestatement.setInt(7, cremain); prestatement.executeUpdate(); } catch (SQLException e1) { System.out.println("SQL 异常"); e1.printStackTrace(); } } // 在管理员处 修改一个课程的信息 public void adminUpdateCourse(Connection conn, String cid1, String cname1, String ctime1, String cteacher1, String cclassroom1, String cnumber1) { try { String cid = cid1; String cname = cname1; String ctime = ctime1; String cteacher = cteacher1; String cclassroom = cclassroom1; String cnumber = cnumber1; //int cremain = cremain1; String upate1 = "update course set cname=?,ctime=?,cteacher=?,cclassroom=?,cnumber=? where cid=?;"; PreparedStatement prestatement = conn.prepareStatement(upate1); prestatement.setString(1, cname); prestatement.setString(2, ctime); prestatement.setString(3, cteacher); prestatement.setString(4, cclassroom); prestatement.setString(5, cnumber); prestatement.setString(6, cid); //prestatement.setInt(7, cremain); prestatement.executeUpdate(); } catch (SQLException e1) { System.out.println("SQL 异常"); e1.printStackTrace(); } } public void registerStudent(Connection conn, String sid1, String sname1, String major1, String scollege1,String spassword1) { try { String sid = sid1; String sname = sname1; String spassword = spassword1; String scollege = scollege1; String major=major1; String insert = "insert into student values(?,?,?,?,?);"; PreparedStatement prestatement = conn.prepareStatement(insert); prestatement.setString(1, sid); prestatement.setString(2, sname); prestatement.setString(3, major); prestatement.setString(4, scollege); prestatement.setString(5, spassword); prestatement.executeUpdate(); } catch (SQLException e1) { System.out.println("SQL 异常"); e1.printStackTrace(); } } }