chengjibean.java

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

package chengji;
import conn.DataBaseConnection;
import java.sql.*;
import java.util.*;
import java.io.*;
public class ChengjiBean
{
	private Connection con=null;
	public ChengjiBean()
	{
		this.con=DataBaseConnection.getConnection();
	}


	//是否存在学生
	public boolean existXuesheng(Chengji chengji) {
        boolean bool=false;
        try {
            String studentId = chengji.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 existChengji(Chengji chengji) {
        try {
            String studentId = chengji.getStudentId();
            String courseId = chengji.getCourseId();
            PreparedStatement pstmt = con.prepareStatement(
                    "select * from chengji where studentId='" +
                    studentId + "' and courseId='"+courseId+"'");
            ResultSet rst = pstmt.executeQuery();
            if (rst.next()) {
                return true;
            }
        } catch (Exception ex) {
            return false;
        }
        return false;
    }

	//插入
	public String addChengji(Chengji chengji)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			if (!existXuesheng(chengji)) {
                return "notExist"; //不存在此学生
            }
            if (existChengji(chengji)) {
                return "exist"; //此学生成绩已存在
            }
			pstmt=con.prepareStatement("insert into chengji(studentId,xueqi,score,courseId) values(?,?,?,?)");
			pstmt.setString(1,chengji.getStudentId());
			pstmt.setString(2,chengji.getXueqi());
			pstmt.setString(3,chengji.getScore());
			pstmt.setString(4,chengji.getCourseId());
			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 void executeUpdate(String sql)
  	    {
  	    	Statement stmt=null;

  	    	try{
  	    		stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
  	    		stmt.executeUpdate(sql);

  	    	}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    	}
         }
	//更新
	public String updateChengji(Chengji chengji)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			pstmt=con.prepareStatement("update chengji set xueqi=?,score=? where studentId=? and courseId=?");
			pstmt.setString(1,chengji.getXueqi());
			pstmt.setString(2,chengji.getScore());
			pstmt.setString(3,chengji.getStudentId());
			pstmt.setString(4,chengji.getCourseId());
			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 addRemedyChengji(String studentId,String courseId,String remedyScore)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			pstmt=con.prepareStatement("update chengji set remedyScore=? where studentId=? and courseId=?");
			pstmt.setString(1,remedyScore);
			pstmt.setString(2,studentId);
			pstmt.setString(3,courseId);
			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 deleteChengji(String studentId,String courseId)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			pstmt=con.prepareStatement("delete from chengji where studentId=? and courseId=?");
			pstmt.setString(1,studentId);
			pstmt.setString(2,courseId);
			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 getChengji(String grade,String stuClass,String xueNian)
	  {
	  	ResultSet rst=null;
	  	CallableStatement calStmt=null;
	  	String strSQL="{call pro_chengji(?,?,?)}";
	  	try{
	  		calStmt=con.prepareCall(strSQL);
			
			calStmt.setString(1,grade);
			calStmt.setString(2,stuClass);
			calStmt.setString(3,xueNian);
			
			rst=calStmt.executeQuery();
	  	}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    	}
  	    return rst;
	  }
  //(总分排名)
  public ResultSet getChengji1(String grade,String stuClass,String xueNian)
	  {
	  	ResultSet rst=null;
	  	CallableStatement calStmt=null;
	  	String strSQL="{call pro_chengji1(?,?,?)}";
	  	try{
	  		calStmt=con.prepareCall(strSQL);
			
			calStmt.setString(1,grade);
			calStmt.setString(2,stuClass);
			calStmt.setString(3,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();
        	}

        }

    //所有班级
     public String getAllClass()
  {
  	Statement stmt = null;
    ResultSet rst = null;
    StringBuffer sBuf=new StringBuffer();
    try {
      stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	  rst=stmt.executeQuery("select distinct classId,className from banji");
      while(rst.next()) {
        String classId= rst.getString("classId");
        String className= rst.getString("className");
        sBuf.append("<option value='"+classId+"'>"+className+"</option>\n");
        }
    }
    catch (SQLException ex) {
      return "";
    }
    return sBuf.toString();
  }

public String getAllClassName()
  {
  	Statement stmt = null;
    ResultSet rst = null;
    StringBuffer sBuf=new StringBuffer();
    try {
      stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	  rst=stmt.executeQuery("select distinct className from banji");
      while(rst.next()) {
        String className= rst.getString("className");
        sBuf.append("<option value='"+className+"'>"+className+"</option>\n");
        }
    }
    catch (SQLException ex) {
      return "";
    }
    return sBuf.toString();
  }
  
  
  //所有课程
     public String getAllCourse()
  {
  	Statement stmt = null;
    ResultSet rst = null;
    StringBuffer sBuf=new StringBuffer();
    try {
      stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	  rst=stmt.executeQuery("select courseId,courseName from kecheng");
      while(rst.next()) {
      	String courseId= rst.getString("courseId");
        String courseName= rst.getString("courseName");
        sBuf.append("<option value='"+courseId+"'>"+courseName+"</option>\n");
        }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
      return "";
    }
    return sBuf.toString();
  }

  //所有课程名
       public String getAllCourseName()
    {
    	Statement stmt = null;
      ResultSet rst = null;
      StringBuffer sBuf=new StringBuffer();
      try {
        stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
  	  rst=stmt.executeQuery("select courseName from kecheng");
        while(rst.next()) {
          String courseName= rst.getString("courseName");
          sBuf.append("<option value='"+courseName+"'>"+courseName+"</option>\n");
          }
      }
      catch (SQLException ex) {
        ex.printStackTrace();
        return "";
      }
      return sBuf.toString();
  }
  
  //
  public String getNianji(String className)
	 {
  	Statement stmt = null;
    ResultSet rst = null;
    StringBuffer sBuf=new StringBuffer();
    try {
      stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	  rst=stmt.executeQuery("select distinct grade from banji where className='"+className+"'");
      if(rst.next()) { 
        String grade= rst.getString("grade").trim();
        sBuf.append("<option value='"+grade+"'>"+grade+"</option>\n");
        }
    }
    catch (SQLException ex) {
      return "";
    }
    return sBuf.toString();
  }
  
  //
  public String getClassId(String className)
	 {
  	Statement stmt = null;
    ResultSet rst = null;
    StringBuffer sBuf=new StringBuffer();
    try {
      stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	  rst=stmt.executeQuery("select classId from banji where className='"+className+"'");
      if(rst.next()) { 
        String classId= rst.getString("classId");
        sBuf.append("<option value='"+classId+"'>"+className+"</option>\n");
        }
    }
    catch (SQLException ex) {
      return "";
    }
    return sBuf.toString();
  }
  
  //获得一个班的学生学号
	  public String getStudentID(String className)
	  {
	  	PreparedStatement pstmt = null;
	    ResultSet rst = null;
	    StringBuffer sBuf=new StringBuffer();
	    try {
	      pstmt = con.prepareStatement("select studentId from xuesheng where stuClass=(select classId from banji where className='"+className+"')");
	      rst = pstmt.executeQuery();
	      while(rst.next()) {
	      	String studentId= rst.getString("studentId").trim();
	        sBuf.append("<option value='"+studentId+"'>"+studentId+"</option>\n");
	      }
	    }
	    catch (SQLException ex) {
	      ex.printStackTrace();
	      return "";
	    }
	    return sBuf.toString();
	  }
  /*
  public static void main(String[] args){
  	ChengjiBean chengjiBean=new ChengjiBean();
  	//System.out.println(chengjiBean.getAllCourse());
  	String str=chengjiBean.addRemedyChengji("0368110051","123","67");
  	System.out.println(str);
  }*/
}