course.java

挺好的东东

package com.room;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.util.DBConn;

public class Course{
	
	public int id;
	
    public String subjectname;
    
    public String teacher;
    
    public String student;
	
    public Course() {
   	 
	 }
    

//新增课程
    public Boolean Add() throws Exception{
  	  Connection conn=null;
  	  PreparedStatement stmt =null;
  	  ResultSet rs =null;
  	  String sql ="insert into t_subject (subjectname,teacher) values (?,?)";
        boolean result=false;
        
        try{
		   
      	 conn=DBConn.getConn();
      	 stmt= conn.prepareStatement(sql); 
      	 stmt.setString(1,this.subjectname);
      	 stmt.setString(2,this.teacher);
      	 
      	 stmt.executeUpdate(); 
      	  
      		 result=true; 
      	
      	}
        catch(Exception e){
      		e.printStackTrace(); 
      	}
      	finally{
      	      try{
      	        if(rs!=null)
      	          rs.close();
      	        if(stmt!=null)
      	          stmt.close();
      	        if(conn!=null)
      	          conn.close();
      	      }catch(Exception e){
      	    	  e.printStackTrace(); 
      	      }
      	    }
         return result;    	 
    } 
    
    
//  查看全部开课信息
    public ArrayList ViewAll() throws Exception{
  	  Connection conn=null;
  	  PreparedStatement stmt =null;
  	  ResultSet rs =null;
  	  String sql ="select * from t_subject where teacher=?";
        ArrayList<Course> result=new ArrayList<Course>();
        
        try{
      	 conn=DBConn.getConn();
      	 stmt= conn.prepareStatement(sql); 
      	 stmt.setString(1,this.teacher);     	
      	 rs = stmt.executeQuery();    	 
      	     	  
      	  while (rs.next()){
      		  Course aa=new Course();
      		  aa.id=rs.getInt("id");
      		  aa.subjectname=rs.getString("subjectname").trim();

      		  result.add(aa);
      		  
      	  }
      	
      	}
        catch(Exception e){
      		e.printStackTrace(); 
      	}
      	finally{
      	      try{
      	        if(rs!=null)
      	          rs.close();
      	        if(stmt!=null)
      	          stmt.close();
      	        if(conn!=null)
      	          conn.close();
      	      }catch(Exception e){
      	    	  e.printStackTrace(); 
      	      }
      	    }
         return result;    	 
    }
    
    
//  查看开课信息
    public Boolean View() throws Exception{
  	  Connection conn=null;
  	  PreparedStatement stmt =null;
  	  ResultSet rs =null;
  	  String sql ="select * from t_subject where id=?";
        boolean result=false;
        
        try{
      	 conn=DBConn.getConn();
      	 stmt= conn.prepareStatement(sql); 
      	 stmt.setInt(1,this.id);     	
      	 rs = stmt.executeQuery();    	 
      	     	  
      	  if (rs.next()){
      		  
      		  id=rs.getInt("id");
      		  subjectname=rs.getString("subjectname").trim();
      		  student=rs.getString("student");

      		  result=true;
      		  
      	  }
      	
      	}
        catch(Exception e){
      		e.printStackTrace(); 
      	}
      	finally{
      	      try{
      	        if(rs!=null)
      	          rs.close();
      	        if(stmt!=null)
      	          stmt.close();
      	        if(conn!=null)
      	          conn.close();
      	      }catch(Exception e){
      	    	  e.printStackTrace(); 
      	      }
      	    }
         return result;    	 
    }
    
//  课程名称修改
    public Boolean Edit() throws Exception{
  	  Connection conn=null;
  	  PreparedStatement stmt =null;
  	  ResultSet rs =null;
  	  String sql ="update t_subject set subjectname=? where id=?";
        boolean result=false;
        
        try{
		   
      	 conn=DBConn.getConn();
      	 stmt= conn.prepareStatement(sql); 
      	 stmt.setString(1,this.subjectname);
      	 stmt.setInt(2,this.id);
      	 
      	 stmt.executeUpdate(); 
      	  
      		 result=true; 
      	
      	}
        catch(Exception e){
      		e.printStackTrace(); 
      	}
      	finally{
      	      try{
      	        if(rs!=null)
      	          rs.close();
      	        if(stmt!=null)
      	          stmt.close();
      	        if(conn!=null)
      	          conn.close();
      	      }catch(Exception e){
      	    	  e.printStackTrace(); 
      	      }
      	    }
         return result;    	 
    } 
    
//  删除课程
    public Boolean Del() throws Exception{
  	  Connection conn=null;
  	  PreparedStatement stmt =null;
  	  ResultSet rs =null;
  	  String sql ="delete from t_subject where id=?";
        boolean result=false;
        
        try{
      	 
        //删除课程信息	
      	 conn=DBConn.getConn();
      	 stmt= conn.prepareStatement(sql); 
      	 stmt.setInt(1,id);
         stmt.executeUpdate(); 
      	 
      	 //获取该课程作业信息
      	 sql="select * from  t_work where subjectid=?";
      	 stmt= conn.prepareStatement(sql); 
      	 stmt.setInt(1,id);
      	 rs=stmt.executeQuery();
      	 
      	 while(rs.next()){
      		 int workid=rs.getInt("id");
      		 
      		 //删除学生作业信息
      		 sql="delete from s_work where workid=?";
          	 stmt= conn.prepareStatement(sql); 
          	 stmt.setInt(1,workid);
             stmt.executeUpdate(); 
      	 }
      	 
      	 //删除该课程作业信息
  		 sql="delete from t_work where subjectid=?";
      	 stmt= conn.prepareStatement(sql); 
      	 stmt.setInt(1,id);
         stmt.executeUpdate(); 
      	  
      		 result=true; 
      	
      	}
        catch(Exception e){
      		e.printStackTrace(); 
      	}
      	finally{
      	      try{
      	        if(rs!=null)
      	          rs.close();
      	        if(stmt!=null)
      	          stmt.close();
      	        if(conn!=null)
      	          conn.close();
      	      }catch(Exception e){
      	    	  e.printStackTrace(); 
      	      }
      	    }
         return result;    	 
    } 
    
    
 //学生选课信息更改
    public Boolean AddStudents() throws Exception{
  	  Connection conn=null;
  	  PreparedStatement stmt =null;
  	  ResultSet rs =null;
  	  String sql ="update t_subject set student=student+? where id=?";
        boolean result=false;
        
        try{
		   
      	 conn=DBConn.getConn();
      	 stmt= conn.prepareStatement(sql); 
      	 stmt.setString(1,this.student);
      	 stmt.setInt(2,this.id);
      	 
      	 stmt.executeUpdate(); 
      	  
      		 result=true; 
      	
      	}
        catch(Exception e){
      		e.printStackTrace(); 
      	}
      	finally{
      	      try{
      	        if(rs!=null)
      	          rs.close();
      	        if(stmt!=null)
      	          stmt.close();
      	        if(conn!=null)
      	          conn.close();
      	      }catch(Exception e){
      	    	  e.printStackTrace(); 
      	      }
      	    }
         return result;    	 
    } 
    
    //学生选课信息
    public String ViewStudents() throws Exception{
  	  Connection conn=null;
  	  PreparedStatement stmt =null;
  	  ResultSet rs =null;
  	  String sql ="select * from t_subject  where id=?";
        
  	  String aa=""; 
  	  
        try{
		   
      	 conn=DBConn.getConn();
      	 stmt= conn.prepareStatement(sql); 
      	 stmt.setInt(1,this.id);
      	 
      	 rs=stmt.executeQuery(); 
       	  
      	 if(rs.next()){
      		 aa=rs.getString("student").trim();
      	 }
      	
      	}
        catch(Exception e){
      		e.printStackTrace(); 
      	}
      	finally{
      	      try{
      	        if(rs!=null)
      	          rs.close();
      	        if(stmt!=null)
      	          stmt.close();
      	        if(conn!=null)
      	          conn.close();
      	      }catch(Exception e){
      	    	  e.printStackTrace(); 
      	      }
      	    }
         return aa;    	 
    }     
    
    //学生选课信息更改2
    public Boolean DelStudents() throws Exception{
  	  Connection conn=null;
  	  PreparedStatement stmt =null;
  	  ResultSet rs =null;
  	  String sql ="update t_subject set student=? where id=?";
        boolean result=false;
        
        try{
		   
      	 conn=DBConn.getConn();
      	 stmt= conn.prepareStatement(sql); 
      	 stmt.setString(1,this.student);
      	 stmt.setInt(2,this.id);
      	 
      	 stmt.executeUpdate(); 
      	  
      		 result=true; 
      	
      	}
        catch(Exception e){
      		e.printStackTrace(); 
      	}
      	finally{
      	      try{
      	        if(rs!=null)
      	          rs.close();
      	        if(stmt!=null)
      	          stmt.close();
      	        if(conn!=null)
      	          conn.close();
      	      }catch(Exception e){
      	    	  e.printStackTrace(); 
      	      }
      	    }
         return result;    	 
    }     

//  查看某学生已选课程
    public ArrayList ViewCourse() throws Exception{
  	  Connection conn=null;
  	  PreparedStatement stmt =null;
  	  ResultSet rs =null;
  	  String sql ="select * from t_subject where student like ?";
        ArrayList<Course> result=new ArrayList<Course>();
        
        try{
      	 conn=DBConn.getConn();
      	 stmt= conn.prepareStatement(sql); 
      	 stmt.setString(1,"%"+student+"%");     	
      	 rs = stmt.executeQuery();    	 
      	     	  
      	  while (rs.next()){
      		  Course aa=new Course();
              aa.id=rs.getInt("id");
              aa.subjectname=rs.getString("subjectname");
     		  result.add(aa);
      	  }
      	
      	}
        catch(Exception e){
      		e.printStackTrace(); 
      	}
      	finally{
      	      try{
      	        if(rs!=null)
      	          rs.close();
      	        if(stmt!=null)
      	          stmt.close();
      	        if(conn!=null)
      	          conn.close();
      	      }catch(Exception e){
      	    	  e.printStackTrace(); 
      	      }
      	    }
         return result;    	 
    }       
}