JSP学籍管理系统+SQL Server2000附带文档

package com; import java.sql.*; import java.util.*; import javabean.ClassInfo; import javabean.CourseInfo; import javabean.ScoreInfo; import javabean.StudentInfo; import javabean.SubjectInfo; import javabean.TeacherInfo; public class DAO { Statement st=null; Connection con=null; ResultSet rs=null; PreparedStatement ps = null; int numm=4; public DAO() { try{ Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=studentManager"; con=DriverManager.getConnection(url,"sa","sa"); System.out.println("连接成功"); }catch(Exception e) { System.out.println(e.getMessage()); } } public int connect(String userName,String passWord){ int num = 0; try{ System.out.println(userName+":"+passWord); String sql="select count(*) from 操作员 where dname ='"+userName+"' and dpass='"+passWord+"'"; st=con.createStatement(); rs=st.executeQuery(sql); if(rs.next()){ num=rs.getInt(1); } System.out.println(num); }catch(Exception e){ System.out.println(e.getMessage()); e.printStackTrace(); }finally { this.close(); } return num; } public int Check(String userName,String passWord){ int num = 0; try{ System.out.println(userName+":"+passWord); String sql="select count(*) from 操作员 where dname ='"+userName+"' and dpass='"+passWord+"'"; st=con.createStatement(); rs=st.executeQuery(sql); if(rs.next()){ num=rs.getInt(1); } System.out.println(num); }catch(Exception e){ System.out.println(e.getMessage()); e.printStackTrace(); }finally { this.close(); } return num; } public ArrayList<CourseInfo> listAllCourse(int page) { ArrayList<CourseInfo> users = null; try { String sql = "select top " + numm + " * from Course where CourseID not in(select top " + numm*(page-1) + " CourseID from Course order by CourseID)"; st = con.createStatement(); rs = st.executeQuery(sql); users = new ArrayList<CourseInfo>(); while(rs.next()) { CourseInfo course = new CourseInfo(); course.setId(rs.getInt(1)); course.setClassName(rs.getString(2)); course.setSubjectName(rs.getString(3)); course.setTeacher(rs.getString(4)); course.setBegindate(rs.getString(5)); course.setEnddate(rs.getString(6)); course.setRemark(rs.getString(7)); users.add(course); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { this.close(); } return users; } public int listTotlaNum() { int n = 0; try { String sql = "select count(*) from Course"; st = con.createStatement(); rs = st.executeQuery(sql); if(rs.next()) { n = rs.getInt(1); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { this.close(); } int a = (n+numm-1)/numm; return a; //n:总行数 num:每页显示的行数 } public ArrayList<ClassInfo> listAllClass(){ ArrayList<ClassInfo> arrayList=new ArrayList<ClassInfo>(); try{ String sql="select * from Class"; ps=con.prepareStatement(sql); rs=ps.executeQuery(); while(rs.next()){ ClassInfo classInfo=new ClassInfo(); classInfo.setClassName(rs.getString(1)); classInfo.setDirector(rs.getString(2)); classInfo.setCurrentCount(rs.getInt(3)); classInfo.setEntranceDate(rs.getString(4)); classInfo.setRemark(rs.getString(5)); arrayList.add(classInfo); } }catch(Exception e){ e.printStackTrace(); arrayList=null; }finally{ try{ this.close(); }catch(final Exception e){ e.printStackTrace(); } } return arrayList; } public ArrayList<ScoreInfo> listAllScore(int page){ ArrayList<ScoreInfo> arrayList=new ArrayList<ScoreInfo>(); try{ String sql="select top " + numm + " * from Score where ScoreID not in(select top " + numm*(page-1) + " ScoreID from Score order by ScoreID)"; ps=con.prepareStatement(sql); rs=ps.executeQuery(); while(rs.next()){ ScoreInfo scoreInfo=new ScoreInfo(); scoreInfo.setStudentNo(rs.getString("StudentNo")); scoreInfo.setCourseID(rs.getString("CourseID")); scoreInfo.setScore(rs.getString("Score")); arrayList.add(scoreInfo); } }catch(Exception e){ e.printStackTrace(); arrayList=null; }finally{ try{ this.close(); }catch(final Exception e){ e.printStackTrace(); } } return arrayList; } public int listTotlaNumScore() { int n = 0; try { String sql = "select count(*) from Score"; st = con.createStatement(); rs = st.executeQuery(sql); if(rs.next()) { n = rs.getInt(1); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { this.close(); } int a = (n+numm-1)/numm; return a; //n:总行数 num:每页显示的行数 } public ArrayList<StudentInfo> listAllStudent(int studentNo){ ArrayList<StudentInfo> array=new ArrayList<StudentInfo>(); try{ String sql="select * from Student where StudentNo='"+studentNo+"'"; ps=con.prepareStatement(sql); rs=ps.executeQuery(); while(rs.next()){ StudentInfo studentInfo=new StudentInfo(); studentInfo.setStudentName(rs.getString(2)); studentInfo.setGender(rs.getString(3)); studentInfo.setBirthday(rs.getString(4)); studentInfo.setClassName(rs.getString(5)); studentInfo.setPicture(rs.getString(6)); studentInfo.setRemark(rs.getString(7)); array.add(studentInfo); } }catch(Exception e){ e.printStackTrace(); array=null; }finally{ try{ this.close(); }catch(final Exception e){ e.printStackTrace(); } } return array; } public ArrayList<StudentInfo> listStudent(int page){ ArrayList<StudentInfo> array=new ArrayList<StudentInfo>(); try{ String sql="select top " + numm + " * from Student where StudentNo not in(select top " + numm*(page-1) + " StudentNo from Student order by StudentNo) "; ps=con.prepareStatement(sql); rs=ps.executeQuery(); while(rs.next()){ StudentInfo studentInfo=new StudentInfo(); studentInfo.setStudentName(rs.getString(2)); studentInfo.setGender(rs.getString(3)); studentInfo.setBirthday(rs.getString(4)); studentInfo.setClassName(rs.getString(5)); studentInfo.setPicture(rs.getString(6)); studentInfo.setRemark(rs.getString(7)); array.add(studentInfo); } }catch(Exception e){ e.printStackTrace(); array=null; }finally{ try{ this.close(); }catch(final Exception e){ e.printStackTrace(); } } return array; } public int listTotlaNumStudent() { int n = 0; try { String sql = "select count(*) from Student"; st = con.createStatement(); rs = st.executeQuery(sql); if(rs.next()) { n = rs.getInt(1); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { this.close(); } int a = (n+numm-1)/numm; return a; //n:总行数 num:每页显示的行数 } public ArrayList<TeacherInfo> listAllTeacher(){ ArrayList<TeacherInfo> array=new ArrayList<TeacherInfo>(); try{ String sql="select * from Teacher"; ps=con.prepareStatement(sql); rs=ps.executeQuery(); while(rs.nex