proceesql.java

来自「学生学籍管理系统」· Java 代码 · 共 1,459 行 · 第 1/4 页

                  temp_v.add(6,rs.getObject(6));
                  vc.addElement(temp_v);
	          i++; 
	           }
	         }catch(SQLException ex) {
	       
            System.err.println("SQLException: " + ex.getMessage());
        }    
        return i;   	
	}
	
	    //按学号提取SC表内容
	int getSCById(Vector vc,String id){
    	int i=0;
		try{
	
		sql = " SELECT DISTINCT * FROM SC WHERE sno = '"+id+"';";         
        	ResultSet rs = stmt.executeQuery(sql);
        	
            while(rs.next()){
            	Vector temp_v= new Vector(7);
            	  temp_v.add(0,(i+1));
                  temp_v.add(1,rs.getObject(1));
                  temp_v.add(2,rs.getObject(2));
                  temp_v.add(3,rs.getObject(3));
                  temp_v.add(4,rs.getObject(4));
                  temp_v.add(5,rs.getObject(5));
                  temp_v.add(6,rs.getObject(6));
                  vc.addElement(temp_v);
	          i++; 
	           }
	         }catch(SQLException ex) {
	       
            System.err.println("SQLException: " + ex.getMessage());
        }    
        return i;   	
	}
	
	
	
//更新SC表内容
	void UpdateSC(Object a[]){
    	int i=0;
		try{
	
		sql = " UPDATE StudentCourse SET grade="+a[4]+" WHERE sno = '"+a[0]+"' AND cno = '"+a[2]+"';";      
        	ResultSet rs = stmt.executeQuery(sql);        	

	         }catch(SQLException ex) {
	         	 JOptionPane.showMessageDialog(null,"修改无效!");
            System.err.println("SQLException: " + ex.getMessage());
        }    

	}
	
//给panelforCourse中的表传值
	int getCourseAndStuInfor(Vector vc){//给ID传值时使用这条语句
    	int i=0;
		try{
	
	   sql = "SELECT * FROM FORCOURSE";         	 	
       ResultSet rs = stmt.executeQuery(sql);     

        	
            while(rs.next()){
                  Vector temp_v= new Vector(9);
            	  temp_v.add(0,(i+1));
                  temp_v.add(1,rs.getObject(1));
                  temp_v.add(2,rs.getObject(2));
                  temp_v.add(3,rs.getObject(3));
                  temp_v.add(4,rs.getObject(4));
                  temp_v.add(5,rs.getObject(5));
                  temp_v.add(6,rs.getObject(6));
                  temp_v.add(7,rs.getObject(7));
                  temp_v.add(8,rs.getObject(8));
                  vc.addElement(temp_v);
	          i++; 
	           }
	         }catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }    
        return i;   	
	}
	
  //修改学籍信息
	public void updateStuInforDetail(Object StuInfor[]){	
     try {	 
   	 	 //0id,1name,2sex,3birthPlace,4testPlace,5graduateSchool,6totalScore
 //7birthDate,8nation,9eductionalSystem,10languageType,11degree,
 //12entranceDate16entranceDay,13politicsType,
    sql ="UPDATE STUINFOR SET name= '" +String.valueOf(StuInfor[1])+"',sex='"+String.valueOf(StuInfor[2]) +"',birthPlace='"+
    String.valueOf(StuInfor[3])+"',testPlace='"+String.valueOf(StuInfor[4]) +"',graduateSchool='"+String.valueOf(StuInfor[5])+"',totalScore="+String.valueOf(StuInfor[6]) 
    +",birthDate='"+ String.valueOf(StuInfor[7])+"',nation = '"+String.valueOf(StuInfor[8]) +"',eductionalsystem = '"+String.valueOf(StuInfor[9])+"',languageType = '"+String.valueOf(StuInfor[10])
     +"',degree='"+ String.valueOf(StuInfor[11])+"',entranceDate='"+ String.valueOf(StuInfor[12])+"',politicsType = '"+String.valueOf(StuInfor[13])+"',college = '"+String.valueOf(StuInfor[14])
    +"',classes = '" +String.valueOf(StuInfor[15])+"' WHERE id = '"+String.valueOf(StuInfor[0])+"';";
     stmt.executeUpdate(sql);            
     con.commit();                           


       	} catch(SQLException ex) {
       		JOptionPane.showMessageDialog(null, "修改无效");
            System.err.println("SQLException: " + ex.getMessage());
            	

        }             		
	}
	
	
 //修改课程信息
public void updateCourseDetail(Object course[]){	
     try {	 
 
    sql ="UPDATE COURSE SET cname= '"+String.valueOf(course[1])+"',dept = '"+String.valueOf(course[2]) +"',credit="+
            String.valueOf(course[3])+",teacher='"+String.valueOf(course[4])+"',semester="+String.valueOf(course[5])+" WHERE cno = '"+String.valueOf(course[0])+"';";
     stmt.executeUpdate(sql);            
     con.commit();                           


       	} catch(SQLException ex) {
       		JOptionPane.showMessageDialog(null, "修改无效");
            System.err.println("SQLException: " + ex.getMessage());
            	

        }             		
}
	
	
	
		//查找是否有该人
boolean weatherExistInStuInfor(String id){
		try{
		
		  sql = " SELECT id FROM STUINFOR WHERE id="+"'"+id+"'";         
          stmt.executeQuery(sql);

	         }catch(SQLException ex){
            System.err.println("SQLException: " + ex.getMessage());
            return false;
        }   
        
        return true;    	
	}
	
	//查看是否有该课程
boolean	weatherExistInCourse(String cno){
	try{
		
		  sql = " SELECT cno FROM COURSE WHERE cno="+"'"+cno+"'";         
          stmt.executeQuery(sql);

	         }catch(SQLException ex){
            System.err.println("SQLException: " + ex.getMessage());
            return false;
        }   
        
        return true;    	
	}

	
	
	//按学号删除学籍信息
 public void DistroyStuInforByID(String id){
		try{
		
		sql =" DELETE FROM STUINFOR WHERE id = "+"'"+id+"'";      
        	      	
        	       	stmt.executeUpdate(sql);
             	con.commit();	         
        	this.DistroySCByID(id);//删除选课信息
        	this.DistroyStudentDetailByID(id);
        	}catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            JOptionPane.showMessageDialog(null, "操作失败，请检查用户名是否正确！");
     
        }       	

	}
	
	//按学号删除选课信息
 public void DistroyStudentDetailByID(String id){
		try{
		
		sql =" DELETE FROM DETAIL WHERE id = "+"'"+id+"'";      
        	      	
        	       	stmt.executeUpdate(sql);
             	con.commit();	         
        	this.DistroyIDTABLE(id);
        	}catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            JOptionPane.showMessageDialog(null, "操作失败，请检查用户名是否正确！");
     
        }       	

	}
	
	
  //按课程号删除课程信息
 public void DistroySCByID(String sno){
		try{
		
		sql =" DELETE FROM StudentCourse WHERE sno = "+"'"+sno+"'";      
        	      	
        	       	stmt.executeUpdate(sql);
             	con.commit();	         
        	
        	}catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            JOptionPane.showMessageDialog(null, "操作失败");
     
        }       	

	}
  //按课程号删除课程信息
 public void DistroySCByIDAndCno(String sno,String cno){
		try{
		
		sql =" DELETE FROM StudentCourse WHERE sno = "+"'"+sno+"' AND cno ='"+cno+"';";      
        	      	
        	       	stmt.executeUpdate(sql);
             	con.commit();	         
        	
        	}catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            JOptionPane.showMessageDialog(null, "操作失败");
     
        }       	

	}	
	
	
   //按院系删除学生信息
 public void  DistroyStuInforByCollege(String college){
		try{
		
		  sql =" DELETE FROM STUINFOR WHERE college = "+"'"+college+"'";      
        	      	
        	       	stmt.executeUpdate(sql);
             	con.commit();	         
          this.DistroySCByCollege(college);
        	}catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            JOptionPane.showMessageDialog(null, "操作失败，请检查用户名是否正确！");
  
        }       	
}

	//按院系删除密码表	
  public void DistroyIDTABLEByCollege(String college){
  	
  	try{
		
		sql =" DELETE FROM IDTABLE WHERE ID =(SELECT id FROM STUINFOR WHERE college = '"+college+"');";      
        	    	      	
        	    stmt.executeUpdate(sql);
             	con.commit();	         
        	
        	}catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }  
  	
  }

   //按院系删课程信息
 public void  DistroySCByCollege(String college){
		try{
		
		  sql =" DELETE FROM StudentCourse WHERE sno IN (SELECT id FROM STUINFOR WHERE college = '"+college+"');";      
        	      	
        	       	stmt.executeUpdate(sql);
             	con.commit();	         
        	this.DistroyIDTABLEByCollege(college);
        	}catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            JOptionPane.showMessageDialog(null, "操作失败！");
  
        }       	
}
	
     //按班级删除学生信息
 public void DistroyStuInforByClasses(String classes){
		try{
		
		  sql =" DELETE FROM STUINFOR WHERE classes = "+"'"+classes+"'";      
        	      	
        	       	stmt.executeUpdate(sql);
             	con.commit();	         
        	
        	}catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            JOptionPane.showMessageDialog(null, "操作失败！");
      
        }       	

	}
	
	//按班级删除选课信息
 public void DistroySCByClasses(String classes){
		try{
		
		  sql =" DELETE FROM StudentCourse WHERE sno IN (SELECT id FROM STUINFOR WHERE classes = '"+classes+"');";   
        	      	
        	       	stmt.executeUpdate(sql);
             	con.commit();	         
        	this.DistroyIDTABLEByClasses(classes);
        	}catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            JOptionPane.showMessageDialog(null, "操作失败！");
      
        }       	

	}
	
   //按班级删除密码表	
  public void DistroyIDTABLEByClasses(String classes){
  	
  	try{
		
		sql =" DELETE FROM IDTABLE WHERE ID IN (SELECT id FROM STUINFOR WHERE classes = '"+classes+"');" ;     
        	    	      	
        	    stmt.executeUpdate(sql);
             	con.commit();	         
        	
        	}catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }  
  	
  }
	
  //按删除全部学生信息
 public void DistroyAllStuInfor(){
		try{
		
		  sql =" DELETE FROM STUINFOR; DELETE FORM DETAIL; DELETE FROM StudentCourse; DELETE FROM IDTABLE WHERE MARK=1; ";      
        	      	
        	       	stmt.executeUpdate(sql);
             	con.commit();	         
        	
        	}catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            JOptionPane.showMessageDialog(null, "操作失败！");
   
        }       	

	}
	
	//删除全部课程信息
 public void DistroyAllCourse(){
		try{
		
		  sql =" DELETE FROM SC";      
        	      	
        	       	stmt.executeUpdate(sql);
             	con.commit();	         
        	
        	}catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            JOptionPane.showMessageDialog(null, "操作失败");
   
        }       	

	}
	
}