banjibean.java

JSP开发的学生信息管理系统

package banji;
import conn.DataBaseConnection;
import java.sql.*;
import java.util.*;
import java.io.*;
public class BanjiBean
{
	private Connection con=null;
	public BanjiBean()
	{
		this.con=DataBaseConnection.getConnection();
	}
	
	//班级编号是否存在
	public boolean existBanji(Banji banji) {
        try {
            String classId = banji.getClassId();
            String className = banji.getClassName();
            PreparedStatement pstmt = con.prepareStatement(
                    "select classId from banji where classId='" +
                    classId + "' or className='"+className+"'");
            ResultSet rst = pstmt.executeQuery();
            if (rst.next()) {
                return true;
            }
        } catch (Exception ex) {
            return false;
        }
        return false;
    }
    
	//插入
	public String addBanji(Banji banji)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			if (existBanji(banji)) {
                return "exist"; //存在此班级编号
            }
			pstmt=con.prepareStatement("insert into Banji values(?,?,?,?,?,?,?,?)");
			pstmt.setString(1,banji.getClassId());
			pstmt.setString(2,banji.getGrade());
			pstmt.setString(3,banji.getSpeciality());
			pstmt.setString(4,banji.getClassName());
			pstmt.setInt(5,banji.getClassNumber());
			pstmt.setString(6,banji.getClassHead());
			pstmt.setString(7,banji.getClassDiscript());
			pstmt.setString(8,"1");
			pstmt.execute();
			str="sucess";
		}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    		str="failer";
  	    	}
  	    	finally
  	    	{
  	    		try{	
  	    		 if(pstmt!=null){
  	    		 	pstmt.close();
  	    		 }
  	    		 if(con!=null){
  	    		 	con.close();
  	    		 }
  	    		 
  	    		}catch(Exception e)
  	    		{
  	    			e.printStackTrace();
  	    		}
  	    	
  	    	}
  	    return str;	
	}
	//更新
	public String updateBanji(Banji banji)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			pstmt=con.prepareStatement("update banji set grade=?,speciality=?,className=?,classNumber=?,classHead=?,classDiscript=? where classId=?");
			pstmt.setString(1,banji.getGrade());
			pstmt.setString(2,banji.getSpeciality());
			pstmt.setString(3,banji.getClassName());
			pstmt.setInt(4,banji.getClassNumber());
			pstmt.setString(5,banji.getClassHead());
			pstmt.setString(6,banji.getClassDiscript());
			pstmt.setString(7,banji.getClassId());
			pstmt.execute();
			str="sucess";
		}
		catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    		str="failer";
  	    	}
  	    	finally
  	    	{
  	    		try{	
  	    		 if(pstmt!=null){
  	    		 	pstmt.close();
  	    		 }
  	    		 if(con!=null){
  	    		 	con.close();
  	    		 }
  	    		}catch(Exception e)
  	    		{
  	    			e.printStackTrace();
  	    		}
  	    	
  	    	}
  	    return str;	
	}
	
	//把班级设置为毕业班级
	public String deleteBanji(String classId)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			pstmt=con.prepareStatement("update banji set flag=? where classId=?"); 
			pstmt.setString(1,"2");
			pstmt.setString(2,classId);
			pstmt.execute();
			deleteXuesheng(classId);
			str="sucess";
		}
		catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    		str="failer";
  	    	}
  	    	finally
  	    	{
  	    		try{	
  	    		 if(pstmt!=null){
  	    		 	pstmt.close();
  	    		 }
  	    		 if(con!=null){
  	    		 	con.close();
  	    		 }
  	    		}catch(Exception e)
  	    		{
  	    			e.printStackTrace();
  	    		}
  	    	
  	    	}
  	    return str;	
	}
	//把毕业班级恢复为在校班级
	public String recoverBanji(String classId)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			pstmt=con.prepareStatement("update banji set flag=? where classId=?"); 
			pstmt.setString(1,"1");
			pstmt.setString(2,classId);
			pstmt.execute();
			recoverXuesheng(classId);
			str="sucess";
			
		}
		catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    		str="failer";
  	    	}
  	    	finally
  	    	{
  	    		try{	
  	    		 if(pstmt!=null){
  	    		 	pstmt.close();
  	    		 }
  	    		 if(con!=null){
  	    		 	con.close();
  	    		 }
  	    		}catch(Exception e)
  	    		{
  	    			e.printStackTrace();
  	    		}
  	    	
  	    	}
  	    return str;	
	}
	//把学生设置为毕业生
	public void deleteXuesheng(String classId)
	{
		PreparedStatement pstmt=null;
		try
		{
			pstmt=con.prepareStatement("update xuesheng set flag=? where stuClass=? and flag=?"); 
			pstmt.setString(1,"4");
			pstmt.setString(2,classId);
			pstmt.setString(3,"1");
			pstmt.execute();
		}
		catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    	}
	}
	//把毕业学生设置为未毕业
	public void recoverXuesheng(String classId)
	{
		PreparedStatement pstmt=null;
		try
		{
			pstmt=con.prepareStatement("update xuesheng set flag=? where stuClass=?"); 
			pstmt.setString(1,"1");
			pstmt.setString(2,classId);
			pstmt.execute();
		}
		catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    	}
	}
	//查询
	public ResultSet executeQuery(String sql)
  	{
  	     Statement stmt=null;
  	     ResultSet rst=null;
  	     try{
	  	      stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	  	      rst=stmt.executeQuery(sql);
  	    	}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    	}
  	    	return rst;
  	 }
  	 //关闭
  	 public void close() 
        {
        	try{
        		con.close();
        	}
        	catch(SQLException e)
        	{
        	   e.printStackTrace();	
        	}
        	
        } 
     //班级信息
     public  ResultSet banjiMessage(String classId)throws Exception
     {
     	Statement stmt=null;
  	     ResultSet rst=null;
  	     Banji banji=null;
  	     try{
	  	      stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	  	      rst=stmt.executeQuery("select * from banji where classId='"+classId+"'");
  	    	}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    	}
  	    	return rst;
     }
     
     //所有班级
     public String getAllClass()
  {
  	PreparedStatement pstmt = null;
    ResultSet rst = null;
    StringBuffer sBuf=new StringBuffer();
    try {
      pstmt = con.prepareStatement("select distinct classId,className from banji");
      rst = pstmt.executeQuery();
      while(rst.next()) {
      	String classId= rst.getString("classId");
      	String className= rst.getString("className");
        sBuf.append("<option value='"+classId+"'>"+className+"</option>\n");
      }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
      return "";
    }
    finally
    {
    	try{
    		if(rst!=null){
    			rst.close();
    			}		
  	    	if(pstmt!=null){
  	    		pstmt.close();
  	    		}
  	    	if(con!=null){
  	    		con.close();
  	    	}	
  	    	
  	    	}catch(Exception e)
    		{
    			e.printStackTrace();
    		}
    }
    return sBuf.toString();
  }
  
  /*
  public static void main(String[] args)throws Exception{
  	BanjiBean banjiBean=new BanjiBean();
  	Banji banji=new Banji();
  	banji.setClassId("123");
  	banji.setGrade("2003");
  	banji.setSpeciality("软件工程");
  	banji.setClassName("03软件1班");
  	banji.setClassNumber(58);
  	banji.setClassHead("d");
  	banji.setClassDiscript("dsfsdf");
  	String i=banjiBean.addBanji(banji);
  	System.out.print(i);
  	//System.out.println(banjiBean.getAllClass());
  }*/
}