databaseupdate.java

综合测评管理系统——完成班级的综合测评工作

import java.sql.*;

public class DatabaseUpdate{
	private Connection dbConn;
	private PreparedStatement ps;
	private ResultSet rs;
	
	
	public DatabaseUpdate(){
		//和数据库建立连接
		final String dbURL="jdbc:sqlserver://localhost:1433; DatabaseName=sample"; 
		final String username="sa"; 
		final String password="2005"; 
		//加载驱动程序以连接数据库
		try{
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
			dbConn=DriverManager.getConnection(dbURL,username,password);
		}catch(ClassNotFoundException cnfex){
			System.err.println("装载 JDBC/ODBC 驱动程序失败。");
			cnfex.printStackTrace();
			System.exit(1); // terminate program
		}catch(SQLException sqlex){
			System.err.println( "无法连接数据库" );
			sqlex.printStackTrace();
			System.exit(1); // terminate program
		}
		
		//数据库更新
		try{
			String selectStr="select * from colligate,colligate_rate where colligate.Academic_year=colligate_rate.Academic_year";
		  ps=dbConn.prepareStatement(selectStr);
		  rs=ps.executeQuery();
		  float F1_grade=0;
		  float F3_grade=0;
		  float F2_grade=0;
		  while(rs.next()){
			  F1_grade=(rs.getFloat("F1_basic")+rs.getFloat("F1_add")+rs.getFloat("F1_reduce"))*rs.getFloat("field1_rate");
			  F3_grade=rs.getFloat("F3_total")*rs.getFloat("field3_rate");
			  String updateStr="update colligate set F1_grade=?,F3_grade=? where sno=? and Academic_year=?";
			  ps=dbConn.prepareStatement(updateStr);
			  ps.setFloat(1,F1_grade);
			  ps.setFloat(2,F3_grade);
			  ps.setString(3,rs.getString("sno"));
			  ps.setString(4,rs.getString("Academic_year"));
			  ps.executeUpdate();
			  
			  //更新F2
			  selectStr="select sum(grade) as cgrade,avg(grade) as agrade from student_course,course where student_course.cno=course.cno and sno=? and Academic_year=?";
			  ps=dbConn.prepareStatement(selectStr);
			  ps.setString(1,rs.getString("sno"));
			  ps.setString(2,rs.getString("Academic_year"));
			  ResultSet rstmp=ps.executeQuery();
			  
			  rstmp.next();
			  F2_grade=rs.getFloat("field2_rate")*rstmp.getFloat("agrade");
			  updateStr="update colligate set F2_total=?,F2_average=?,F2_grade=? where sno=? and Academic_year=?";
			  ps=dbConn.prepareStatement(updateStr);
			  ps.setFloat(1,rstmp.getFloat("cgrade"));
			  ps.setFloat(2,rstmp.getFloat("agrade"));
			  ps.setFloat(3,F2_grade);
			  ps.setString(4,rs.getString("sno"));
			  ps.setString(5,rs.getString("Academic_year"));
			  ps.executeUpdate();
		 }
		 //排名
		 selectStr="select * from colligate order by F2_grade DESC";
		 ps=dbConn.prepareStatement(selectStr);
		 rs=ps.executeQuery();
		 for(int i=1;rs.next();i++){
		 	String updateStr="update colligate set F2_ranking=? where sno=? and Academic_year=?";
		 	ps=dbConn.prepareStatement(updateStr);
		 	ps.setInt(1,i);
		 	ps.setString(2,rs.getString("sno"));
		 	ps.setString(3,rs.getString("Academic_year"));
		 	ps.executeUpdate();
		 }
		 
		 selectStr="select * from colligate order by Total_grade DESC";
		 ps=dbConn.prepareStatement(selectStr);
		 rs=ps.executeQuery();
		 for(int i=1;rs.next();i++){
		 	String updateStr="update colligate set Total_ranking=? where sno=? and Academic_year=?";
		 	ps=dbConn.prepareStatement(updateStr);
		 	ps.setInt(1,i);
		 	ps.setString(2,rs.getString("sno"));
		 	ps.setString(3,rs.getString("Academic_year"));
		 	ps.executeUpdate();
		 }
		 selectStr="select COUNT(sno) as cou,sno from student_course group by sno";
		 ps=dbConn.prepareStatement(selectStr);
		 rs=ps.executeQuery();
		 while(rs.next()){
		   String updateStr="update student set passcourse=? where sno=?";
		   ps=dbConn.prepareStatement(updateStr);
		   ps.setInt(1,rs.getInt("cou"));
		   ps.setString(2,rs.getString("sno"));
		   ps.executeUpdate();
		 }		 	
		}catch(SQLException e){}
	}
}