count.java

本系统采用Servlet+Jsp+JavaBean+SQL2000 设计方式

package bean;   
import java.sql.*;
import sun.jdbc.odbc.JdbcOdbcDriver;
  
public class Count{   
      Connection  Con=null;
      Statement   Stmt=null;
	  String sqlCmd="SELECT pass.num,pass.name,score.Escore,score.Mscore,score.Cscore,score.Total FROM pass INNER JOIN score ON pass.num=score.num ";   
      public Count(){   
          try{
		       Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			   Con =DriverManager.getConnection("jdbc:odbc:pass","","");
			   Stmt=Con.createStatement();
          }   
          catch(Exception e){   
               System.out.println("Error"+e.getMessage());   
          }
      }
	      
	  public int getEscore(){
	       int sum=0,i=0;
	       try{
			   ResultSet result=Stmt.executeQuery(sqlCmd+"ORDER BY pass.num");
			   while(result!=null&&result.next()){
			       sum=sum+Integer.valueOf(result.getString("Escore")).intValue();
				   i++;
			   }
			   return (sum/i);
		   }
		   catch(Exception e){}   
            return 0;
	  }
	  
	  public int getMscore(){
	       int sum=0,i=0;
	       try{
			   ResultSet result=Stmt.executeQuery(sqlCmd+"ORDER BY pass.num");
			   while(result!=null&&result.next()){
			       sum=sum+Integer.valueOf(result.getString("Mscore")).intValue();
				   i++;
			   }
			   return (sum/i);
		   }
		   catch(Exception e){}   
            return 0;
	  }
	  
	  public int getCscore(){
	       int sum=0,i=0;
	       try{
			   ResultSet result=Stmt.executeQuery(sqlCmd+"ORDER BY pass.num");
			   while(result!=null&&result.next()){
			       sum=sum+Integer.valueOf(result.getString("Cscore")).intValue();
				   i++;
			   }
			   return (sum/i);
		   }
		   catch(Exception e){}   
            return 0;
	  }
	  
	  public ResultSet getHighE(){
		  int hScore=0,tmp=0;
          try{
		       ResultSet result=Stmt.executeQuery(sqlCmd+"ORDER BY pass.num");
			   while(result!=null&&result.next()){
			       tmp=Integer.valueOf(result.getString("Escore")).intValue();
			       if(tmp>hScore)hScore=tmp;
			   }
			   result=Stmt.executeQuery(sqlCmd+"WHERE Escore = '"+hScore+"'");
			   return result;      
           }   
           catch(Exception e){}  
           return null;         
      }
	  
	  public ResultSet getHighM(){
		  int hScore=0,tmp=0;
          try{
		       ResultSet result=Stmt.executeQuery(sqlCmd+"ORDER BY pass.num");
			   while(result!=null&&result.next()){
			       tmp=Integer.valueOf(result.getString("Mscore")).intValue();
			       if(tmp>hScore)hScore=tmp;
			   }
			   result=Stmt.executeQuery(sqlCmd+"WHERE Mscore = '"+hScore+"'");
			   return result;      
           }   
           catch(Exception e){}  
           return null;         
      }
	  
	  public ResultSet getHighC(){
		  int hScore=0,tmp=0;
          try{
		       ResultSet result=Stmt.executeQuery(sqlCmd+"ORDER BY pass.num");
			   while(result!=null&&result.next()){
			       tmp=Integer.valueOf(result.getString("Cscore")).intValue();
			       if(tmp>hScore)hScore=tmp;
			   }
			   result=Stmt.executeQuery(sqlCmd+"WHERE Cscore = '"+hScore+"'");
			   return result;      
           }   
           catch(Exception e){}  
           return null;         
      }
	  
	   public ResultSet getHighT(){
		  int hScore=0,tmp=0;
          try{
		       ResultSet result=Stmt.executeQuery(sqlCmd+"ORDER BY pass.num");
			   while(result!=null&&result.next()){
			       tmp=Integer.valueOf(result.getString("Total")).intValue();
			       if(tmp>hScore)hScore=tmp;
			   }
			   result=Stmt.executeQuery(sqlCmd+"WHERE Total = '"+hScore+"'");
			   return result;      
           }   
           catch(Exception e){}  
           return null;         
      }
	  
	  public ResultSet getLowE(){
		  String lScore;
          try{
		       ResultSet result=Stmt.executeQuery(sqlCmd+"ORDER BY score.Escore");
			   if(result!=null){
			       result.next();
			       lScore=result.getString("Escore");
				   result=Stmt.executeQuery(sqlCmd+"WHERE Escore = '"+lScore+"'");
			   }
			   return result;      
           }   
           catch(Exception e){}  
           return null;         
      }
	  
	  public ResultSet getLowM(){
		  String lScore;
          try{
		       ResultSet result=Stmt.executeQuery(sqlCmd+"ORDER BY score.Mscore");
			   if(result!=null){
			       result.next();
			       lScore=result.getString("Mscore");
				   result=Stmt.executeQuery(sqlCmd+"WHERE Mscore = '"+lScore+"'");
			   }
			   return result;      
           }   
           catch(Exception e){}  
           return null;         
      }
	  
	  public ResultSet getLowC(){
		  String lScore;
          try{
		       ResultSet result=Stmt.executeQuery(sqlCmd+"ORDER BY score.Cscore");
			   if(result!=null){
			       result.next();
			       lScore=result.getString("Cscore");
				   result=Stmt.executeQuery(sqlCmd+"WHERE Cscore = '"+lScore+"'");
			   }  
			   return result;      
           }   
           catch(Exception e){}  
           return null;         
      }
	  
	  public ResultSet getLowT(){
		  String lScore;
          try{
		       ResultSet result=Stmt.executeQuery(sqlCmd+"ORDER BY score.Total");
			   if(result!=null){
			       result.next();
			       lScore=result.getString("Total");
				   result=Stmt.executeQuery(sqlCmd+"WHERE Total = '"+lScore+"'");
			   }  
			   return result;      
           }   
           catch(Exception e){}  
           return null;         
      }
}