coursedatabase.java

一个用于学生注册选课信息管理的简单的管理系统

import java.util.*;
import java.util.Vector;
import java.io.*;
import java.sql.*;
public class CourseDatabase
{
    private String url="jdbc:odbc:StudentAdministrator";
    private String driver="sun.jdbc.odbc.JdbcOdbcDriver";
    private Connection aConnection;
    private Statement aStatement;
    private String courseclassid;
    private String coursename;
    private String coursexuefen;
    private String courseteacher;
    private String coursetime;
    private String courselocation;
    private int coursenum;
    private String coursepreview;
    private SCourse aSCourse;
    private Vector aSection=new Vector();
    
    public void initialize()
    {
        try
        {
            Class.forName(driver);
            aConnection=DriverManager.getConnection(url,"","");
            aStatement=aConnection.createStatement();
        }
        catch(ClassNotFoundException e)
        {   System.out.println(e);    }
        catch(SQLException e)
        {   System.out.println(e);    }
    }
    public void terminate()
    {
        try
        {
            aStatement.close();
            aConnection.close();
        }
        catch(SQLException e)
        {   System.out.println(e);    }
    }
  /*
    public SCourse findSCourse(SCourse sc) throws NotFoundException
    {
        SCourse newSCourse=null;
        String time=sc.getCourseClassTime();
        String location=sc.getCourseClassRoom();
        String sqlQuery="select * from 课程列表 where 上课时间='"+time+"' and 地点='"+location+"'";
                try
        {
            ResultSet rs=aStatement.executeQuery(sqlQuery);
            boolean gotIt=rs.next();
            if(gotIt)
            {
                coursename=rs.getString(1);
                coursexuefen=rs.getString(2);
                courseteacher=rs.getString(3);
                coursetime=rs.getString(4);
                courselocation=rs.getString(5);
                coursenum=rs.getString(6);
                coursenumlef=rs.getString(7);
                newSCourse=new SCourse(coursename,coursexuefen,courseteacher,coursetime,courselocation,coursenum,coursenumlef);
            }
            else 
            {   throw(new NotFoundException());  }
            rs.close();
        }
        catch(SQLException e)
        {   System.out.println(e); System.out.println("错误一");   }
        return newSCourse;
    }
   */
    public SCourse FindSCourse(String s) throws NotFoundException
    {
        SCourse newSCourse=null;
        String sqlQuery="select * from courseClass where courseClassId='"+s+"'";
        try
        {
            ResultSet rs=aStatement.executeQuery(sqlQuery);
            boolean gotIt=rs.next();
            if(gotIt)
            {
            }
            else 
            {   throw(new NotFoundException());  }
            rs.close();
        }
        catch(SQLException e)
        {   System.out.println(e); System.out.println("错误一");   }
        return newSCourse;
    }
  public SCourse otherFindSCourse(String s) throws NotFoundException
    {
        SCourse newSCourse=null;
        String sqlQuery="select * from course where courseNo='"+s+"'";
        try
        {
            ResultSet rs=aStatement.executeQuery(sqlQuery);
            boolean gotIt=rs.next();
            if(gotIt)
            {
             }
            else 
            {   throw(new NotFoundException());  }
            rs.close();
        }
        catch(SQLException e)
        {   System.out.println(e); System.out.println("错误一");   }
        return newSCourse;
    }
 
public void deleteCourse(String s1)
    {
        String sqlQuery="delete from courseClass where courseClassId='"+s1+"'";
        try
        {            
            int result=aStatement.executeUpdate(sqlQuery);
        }
        catch(SQLException e)
        {   System.out.println(e);    }
    }
  public void otherAddSCourse(String s1,String s2,String s3,int s4,String s5,String s6)
    {
        String sqlQuery="insert into courseClass(courseClassId,courseClassRoom,courseClassTime,courseClassNum,courseClassTeachNo,courseClassCourseId) "
                +"values('"+s1+"','"+s2+"','"+s3+"',"
                +s4+",'"+s5+"','"+s6+"')";
            try
            {    int result=aStatement.executeUpdate(sqlQuery);     }
            catch(SQLException e)
            {   System.out.println(e);      }       
    }
    public void addSCourse(String s1,String s2,String s3,String s4,String s5)
    {
        String sqlQuery="insert into course(courseNo,courseName,coursePreview,courseXuefen,coursePartmentNo) "
                +"values('"+s1+"','"+s2+"','"+s3+"','"
                +s4+"','"+s5+"')";
            try
            {    int result=aStatement.executeUpdate(sqlQuery);     }
            catch(SQLException e)
            {   System.out.println(e);      }       
    }
  public void addSC(String s1,String s2,String s3)
    {
        String sqlQuery="insert into sc(studId,courseClassId,courseGrade) "
                +"values('"+s1+"','"+s2+"','"+s3+"')";
            try
            { 
                int result=aStatement.executeUpdate(sqlQuery);  
            }
            catch(SQLException e)
            {   
                System.out.println(e);  
            }       
    }
    public Vector getSectionData()
    {
        // "课程班号","课程名","学分","授课老师","上课时间","地点","选课余额","前修课程"
        aSection.removeAllElements();
        String sqlQuery="select courseClass.courseClassId,course.courseName,course.courseXuefen,teacher.teachName,courseClass.courseClassTime,courseClass.courseClassRoom,courseClass.courseClassNum,course.coursePreview from courseClass,course,teacher where course.courseNo=courseClass.courseClassCourseId and teacher.teachId=courseClass.courseClassTeachNo";
        try
        {
            ResultSet rs=aStatement.executeQuery(sqlQuery);
            boolean moreData=rs.next();
            if(moreData)
                while(moreData)
                {
                    courseclassid=rs.getString(1);
                    coursename=rs.getString(2);
                    coursexuefen=rs.getString(3);
                    courseteacher=rs.getString(4);
                    coursetime=rs.getString(5);
                    courselocation=rs.getString(6);
                    coursenum=rs.getInt("courseClassNum");
                    coursepreview=rs.getString(8);
                    aSCourse=new SCourse(courseclassid,coursename,coursexuefen,courseteacher,
                            coursetime,courselocation,coursenum,coursepreview); 
                    aSection.add(aSCourse);       
                    moreData=rs.next();
                }
            rs.close();
        }
        catch(SQLException e)
        {   System.out.println(e);    }
        return aSection;
    }
    public Vector getAStudentGrade(String studId,String classNo)
    {
        // "课程班号","课程名","学分","授课老师","上课时间","地点","选课余额","前修课程"
        aSection.removeAllElements();
        String sqlQuery="select course.courseName,sc.courseGrade,course.courseXuefen from courseClass,course,sc,student where student.studId='"+studId+"' and student.studClassNo='"+classNo+"' and sc.studId=student.studId and sc.courseClassId=courseClass.courseClassId and courseClass.courseClassCourseId=course.courseNo";
        try
        {
            ResultSet rs=aStatement.executeQuery(sqlQuery);
            boolean moreData=rs.next();
            if(moreData)
                while(moreData)
                { 
                    coursename=rs.getString(1);
                    coursepreview=rs.getString(2);
                    coursexuefen=rs.getString(3);                   
                    Grade aGrade=new Grade(coursename,coursepreview,coursexuefen); 
                    aSection.add(aGrade);       
                    moreData=rs.next();
                }
            rs.close();
        }
        catch(SQLException e)
        {   System.out.println(e);    }
        return aSection;
    }
    public Vector getOtherAStudentGrade(String studId,String studParmentNo)
    {
        // "课程班号","课程名","学分","授课老师","上课时间","地点","选课余额","前修课程"
        aSection.removeAllElements();
        String sqlQuery="select student.studName,student.studClassNo,course.courseName,sc.courseGrade,course.courseXuefen from courseClass,course,sc,student where student.studId='"+studId+"' and student.studPartmentNo='"+studParmentNo+"' and sc.studId=student.studId and sc.courseClassId=courseClass.courseClassId and courseClass.courseClassCourseId=course.courseNo";
        try
        {
            ResultSet rs=aStatement.executeQuery(sqlQuery);
            boolean moreData=rs.next();
            if(moreData)
                while(moreData)
                { 
                    courseclassid=rs.getString(1);
                    coursename=rs.getString(2);
                    courseteacher=rs.getString(3); 
                    coursetime=rs.getString(4); 
                    coursexuefen=rs.getString(5); 
                    Grade aGrade=new Grade(courseclassid,coursename,courseteacher,coursetime,coursexuefen); 
                    aSection.add(aGrade);       
                    moreData=rs.next();
                }
            rs.close();
        }
        catch(SQLException e)
        {   System.out.println(e);    }
        return aSection;
    }
    public Vector getACourseGrade(String courseName,String classNo)
    {
        aSection.removeAllElements();
        String sqlQuery="select student.studName,student.studSex,sc.courseGrade,course.courseXuefen from courseClass,course,sc,student where course.courseName='"+courseName+"' and student.studClassNo='"+classNo+"' and sc.studId=student.studId and sc.courseClassId=courseClass.courseClassId and courseClass.courseClassCourseId=course.courseNo";
        try
        {
            ResultSet rs=aStatement.executeQuery(sqlQuery);
            boolean moreData=rs.next();
            if(moreData)
                while(moreData)
                { 
                    coursename=rs.getString(1);
                    coursepreview=rs.getString(2);
                    coursexuefen=rs.getString(3);
                    courseclassid=rs.getString(4);
                    Grade aGrade=new Grade(coursename,coursepreview,coursexuefen,courseclassid); 
                    aSection.add(aGrade);       
                    moreData=rs.next();
                }
            rs.close();
        }
        catch(SQLException e)
        {   System.out.println(e);    }
        return aSection;
    }
    public Vector getOtherACourseGrade(String courseName,String studParmentNo)
    {
        aSection.removeAllElements();
        String sqlQuery="select student.studName,student.studClassNo,sc.courseGrade,course.courseXuefen from courseClass,course,sc,student where course.courseName='"+courseName+"' and student.studPartmentNo='"+studParmentNo+"' and sc.studId=student.studId and sc.courseClassId=courseClass.courseClassId and courseClass.courseClassCourseId=course.courseNo";
        
        try
        {
            ResultSet rs=aStatement.executeQuery(sqlQuery);
            boolean moreData=rs.next();
            if(moreData)
                while(moreData)
                { 
                    coursename=rs.getString(1);
                    coursepreview=rs.getString(2);
                    coursexuefen=rs.getString(3);
                    courseclassid=rs.getString(4);
                    Grade aGrade=new Grade(coursename,coursepreview,coursexuefen,courseclassid); 
                    aSection.add(aGrade);       
                    moreData=rs.next();
                }
            rs.close();
        }
        catch(SQLException e)
        {   System.out.println(e);    }
        return aSection;
    }
    public Vector getMingDan(String courseClassId)
    {
        // "课程班号","课程名","学分","授课老师","上课时间","地点","选课余额","前修课程"
        aSection.removeAllElements();
        String sqlQuery="select student.studId,student.studName,student.studSex,student.studPartmentNo,student.studClassNo from sc,student where sc.courseClassId='"+courseClassId+"' and sc.studId=student.studId";
        try
        {
            ResultSet rs=aStatement.executeQuery(sqlQuery);
            boolean moreData=rs.next();
            if(moreData)
                while(moreData)
                { 
                    courseclassid=rs.getString(1);
                    coursename=rs.getString(2);
                    courseteacher=rs.getString(3); 
                    coursetime=rs.getString(4); 
                    coursexuefen=rs.getString(5); 
                    Grade aGrade=new Grade(courseclassid,coursename,courseteacher,coursetime,coursexuefen); 
                    aSection.add(aGrade);       
                    moreData=rs.next();
                }
            rs.close();
        }
        catch(SQLException e)
        {   System.out.println(e);    }
        return aSection;
    }
    public CourseDatabase()
    {
        initialize();
    }   
    
}