xuegankaohebean.java

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

package xueganKaoHe;
import conn.DataBaseConnection;
import java.sql.Date;
import java.sql.*;
import java.util.*;
import java.io.*;
public class XueganKaoHeBean
{
	private Connection con=null;
	public XueganKaoHeBean()
	{
		this.con=DataBaseConnection.getConnection();
	}
	
	//是否存在学生
	public boolean existXuesheng(XueganKaoHe xueganKaoHe) {
        boolean bool=false;
        try {
            String studentId = xueganKaoHe.getStudentId();
            PreparedStatement pstmt = con.prepareStatement(
                    "select studentId from xuesheng where studentId='" +
                    studentId + "' and flag='"+1+"'");
            ResultSet rst = pstmt.executeQuery();
            if (rst.next()) {
                bool=true;
            }
            if(rst!=null){
        	rst.close();
        	}
        	if(pstmt!=null){
        		pstmt.close();
        	}
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        
        return bool;
    }
    
	//是否存在记录
	public boolean existXueganKaoHe(XueganKaoHe xueganKaoHe) {
        boolean bool=false;
        try {
            String studentId = xueganKaoHe.getStudentId();
            String xuenian = xueganKaoHe.getXuenian();
            PreparedStatement pstmt = con.prepareStatement(
                    "select * from xueganKaoHe where studentId='" +
                    studentId + "' and xuenian='"+xuenian+"'");
            ResultSet rst = pstmt.executeQuery();
            if (rst.next()) {
                bool=true;
            }
            if(rst!=null){
        	rst.close();
        	}
        	if(pstmt!=null){
        		pstmt.close();
        	}
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        
        return bool;
    }
    
    
	//插入
	public String addXueganKaoHe(XueganKaoHe xueganKaoHe)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
            if (!existXuesheng(xueganKaoHe)) {
                return "notExist"; //不存在此学生
            }
            if (existXueganKaoHe(xueganKaoHe)) {
                return "exist"; //此学生学干考核记录已存在
            }
			pstmt=con.prepareStatement("insert into xueganKaoHe values(?,?,?,?,?,?,?)");
			pstmt.setString(1,xueganKaoHe.getStudentId());
			pstmt.setString(2,xueganKaoHe.getXuenian());
			pstmt.setString(3,xueganKaoHe.getZhiwu());
			pstmt.setString(4,xueganKaoHe.getYewuKaohefen());
			pstmt.setString(5,xueganKaoHe.getGongzKaohefen());
			pstmt.setString(6,xueganKaoHe.getMinzhuHupingfen());
			pstmt.setString(7,xueganKaoHe.getKaoQinfen());
			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 updateXueganKaoHe(XueganKaoHe xueganKaoHe)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			pstmt=con.prepareStatement("update xueganKaoHe set zhiwu=?,yewuKaohefen=?,gongzKaohefen=?,minzhuHupingfen=?,kaoQinfen=? where studentId=? and xuenian=?");			
			pstmt.setString(1,xueganKaoHe.getZhiwu());
			pstmt.setString(2,xueganKaoHe.getYewuKaohefen());
			pstmt.setString(3,xueganKaoHe.getGongzKaohefen());
			pstmt.setString(4,xueganKaoHe.getMinzhuHupingfen());
			pstmt.setString(5,xueganKaoHe.getKaoQinfen());
			pstmt.setString(6,xueganKaoHe.getStudentId());
			pstmt.setString(7,xueganKaoHe.getXuenian());
			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 deleteXueganKaoHe(String studentId,String xuenian)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			pstmt=con.prepareStatement("delete from xueganKaoHe where studentId=? and xuenian=?"); 
			pstmt.setString(1,studentId);
			pstmt.setString(2,xuenian);
			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 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 ResultSet getXueganKaoHe(String xueNian)
	  {
	  	ResultSet rst=null;
	  	CallableStatement calStmt=null;
	  	String strSQL="{call pro_xueganKaoHe(?)}";
	  	try{
	  		calStmt=con.prepareCall(strSQL);
			calStmt.setString(1,xueNian);
			
			rst=calStmt.executeQuery();
	  	}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    	}
  	    return rst;
	  }
	  
  	 //关闭
  	 public void close() 
        {
        	try{
  	    		 if(con!=null){
  	    		 	con.close();
  	    		 }
        	}
        	catch(SQLException e)
        	{
        	   e.printStackTrace();	
        	}
        	
        }  
}