coursegrade.java

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

	  					JOptionPane.showMessageDialog(new JFrame(),"课程不存在,课程名或学年出错","警告",JOptionPane.WARNING_MESSAGE);
	  				}else{
	  					//判断记录是否存在
	  					selectStr="select * from student_course where cno=? and sno=?";
	  				  ps=dbConn.prepareStatement(selectStr);
	  				  ps.setString(1,rs.getString("cno"));
	  				  ps.setString(2,studentno_textfield.getText());
	  				  rs=ps.executeQuery();
	  				  
	  				  if(rs.next()){
	  				  	String updateStr="update student_course set grade=? WHERE cno=? and sno=?";
	  			      ps=dbConn.prepareStatement(updateStr);
	  			      ps.setFloat(1,Float.parseFloat(grade_textfield.getText()));
	  			      ps.setString(2,rs.getString("cno"));
	  			      ps.setString(3,studentno_textfield.getText());
	  			      ps.executeUpdate();
	  			      JOptionPane.showMessageDialog(new JFrame(),"修改成功","成功",JOptionPane.WARNING_MESSAGE);
	  			    
	  			      selectStr="select student_course.sno as '学号',cname as '课程名',grade as '成绩',academic_year as '学年' from student_course,course where course.cno=student_course.cno and course.cno=? and sno=?";
	  				    ps=dbConn.prepareStatement(selectStr);
	  				    ps.setString(1,rs.getString("cno"));
	  				    ps.setString(2,studentno_textfield.getText());
	  				    rs=ps.executeQuery();
	  			      displayResultSet(rs);
	  			    }else{
	  			  	  JOptionPane.showMessageDialog(new JFrame(),"记录不存在","警告",JOptionPane.WARNING_MESSAGE);
	  			  	}
	  			  }
	  			}catch(SQLException exc){
	  			  exc.printStackTrace();
	  			}finally{
	  			  if(ps!=null)try{ps.close();}catch(SQLException ignore){}
	  			  if(dbConn!=null)try{dbConn.close();}catch(SQLException ignore){}
	  			}
	  	  }
	  	  //更新数据库
	  	  new DatabaseUpdate();
	  	}
	  });
	  
	  exit_button.addActionListener(new ActionListener(){
	  	public void actionPerformed(ActionEvent e){
	  		dispose();	  	
	  	}
	  });
	  
	  add_button.addActionListener(new ActionListener(){
	  	public void actionPerformed(ActionEvent e){
	  		String[] temp={"","",""};
	  		temp[0]=(String)coursename_combobox.getSelectedItem();
	  		temp[1]=studentno_textfield.getText();
	  		temp[2]=grade_textfield.getText();
	  		if(temp[0].equals("")||temp[1].equals("")||temp[2].equals("")){
	  			JOptionPane.showMessageDialog(new JFrame(),"记录不能为空","警告",JOptionPane.WARNING_MESSAGE);
	  		}else{
	  		  try{
	  		  	//判断课程是否存在
	  		    dbConn=DriverManager.getConnection(dbURL,username,password);
	  				String selectStr="select * from course where cname=? and academic_year=?";
	  				ps=dbConn.prepareStatement(selectStr);
	  				ps.setString(1,(String)coursename_combobox.getSelectedItem());
	  				ps.setString(2,(String)acadyear_combobox.getSelectedItem());
	  				rs=ps.executeQuery();
	  				
	  				if(!rs.next()){
	  					JOptionPane.showMessageDialog(new JFrame(),"课程不存在,课程名或学年出错","警告",JOptionPane.WARNING_MESSAGE);
	  				}else{
	  					String cnoStr=rs.getString("cno");
	  					//判断记录是否存在
	  					selectStr="select * from student_course where cno=? and sno=?";
	  				  ps=dbConn.prepareStatement(selectStr);
	  				  ps.setString(1,cnoStr);
	  				  ps.setString(2,studentno_textfield.getText());
	  				  rs=ps.executeQuery();
	  				    				  
	  				  if(!rs.next()){
	  				  	//判断学号是否合法
	  				  	selectStr="select * from student where sno=?";
	  				    ps=dbConn.prepareStatement(selectStr);
	  				    ps.setString(1,studentno_textfield.getText());
	  				    rs=ps.executeQuery();
	  				    
	  				    if(rs.next()){
	  				  	  String updateStr="insert into student_course (sno,cno,grade) values(?,?,?)";
	  			        ps=dbConn.prepareStatement(updateStr);
	  			        ps.setString(1,studentno_textfield.getText());
	  			        ps.setString(2,cnoStr);
	  			        ps.setFloat(3,Float.parseFloat(grade_textfield.getText()));
	  			        ps.executeUpdate();
	  			        JOptionPane.showMessageDialog(new JFrame(),"添加成功","成功",JOptionPane.WARNING_MESSAGE);
	  			    
	  			        selectStr="select student_course.sno as '学号',cname as '课程名',grade as '成绩',academic_year as '学年' from student_course,course where course.cno=student_course.cno and course.cno=? and sno=?";
	  				      ps=dbConn.prepareStatement(selectStr);
	  				      ps.setString(1,cnoStr);
	  				      ps.setString(2,studentno_textfield.getText());
	  				      rs=ps.executeQuery();
	  			        displayResultSet(rs);
	  			      }else{
	  			      	JOptionPane.showMessageDialog(new JFrame(),"学号不合法","警告",JOptionPane.WARNING_MESSAGE);
	  			      }
	  			    }else{
	  			  	  JOptionPane.showMessageDialog(new JFrame(),"记录已存在","警告",JOptionPane.WARNING_MESSAGE);
	  			  	}
	  			  }
	  			}catch(SQLException exc){
	  			  exc.printStackTrace();
	  			}finally{
	  			  if(ps!=null)try{ps.close();}catch(SQLException ignore){}
	  			  if(dbConn!=null)try{dbConn.close();}catch(SQLException ignore){}
	  			}
	  	  }
	  	  //更新数据库
	  	  new DatabaseUpdate();
	  	}
	  });
	  
	  delete_button.addActionListener(new ActionListener(){
	  	public void actionPerformed(ActionEvent e){
	  		String[] temp={"","",""};
	  		temp[0]=(String)coursename_combobox.getSelectedItem();
	  		temp[1]=studentno_textfield.getText();
	  		if(temp[0].equals("")||temp[1].equals("")){
	  			JOptionPane.showMessageDialog(new JFrame(),"记录不能为空","警告",JOptionPane.WARNING_MESSAGE);
	  		}else{
	  		  try{
	  		  	//判断课程是否存在
	  		    dbConn=DriverManager.getConnection(dbURL,username,password);
	  				String selectStr="select * from course where cname=? and academic_year=?";
	  				ps=dbConn.prepareStatement(selectStr);
	  				ps.setString(1,(String)coursename_combobox.getSelectedItem());
	  				ps.setString(2,(String)acadyear_combobox.getSelectedItem());
	  				rs=ps.executeQuery();
	  				
	  				if(!rs.next()){
	  					JOptionPane.showMessageDialog(new JFrame(),"课程不存在,课程名或学年出错","警告",JOptionPane.WARNING_MESSAGE);
	  				}else{
	  					String cnoStr=rs.getString("cno");
	  					//判断记录是否存在
	  					selectStr="select * from student_course where cno=? and sno=?";
	  				  ps=dbConn.prepareStatement(selectStr);
	  				  ps.setString(1,cnoStr);
	  				  ps.setString(2,studentno_textfield.getText());
	  				  rs=ps.executeQuery();
	  				  
  				    if(rs.next()){
	  				  	String deleteStr="delete student_course where cno=? and sno=?";
	  			      ps=dbConn.prepareStatement(deleteStr);
	  			      ps.setString(1,cnoStr);
	  				    ps.setString(2,studentno_textfield.getText());
	  			      ps.executeUpdate();
	  			      JOptionPane.showMessageDialog(new JFrame(),"删除成功","成功",JOptionPane.WARNING_MESSAGE);
	  			    }else{
	  			  	  JOptionPane.showMessageDialog(new JFrame(),"记录不存在","警告",JOptionPane.WARNING_MESSAGE);
	  			  	}
	  			  }
	  			}catch(SQLException exc){
	  			  exc.printStackTrace();
	  			}finally{
	  			  if(ps!=null)try{ps.close();}catch(SQLException ignore){}
	  			  if(dbConn!=null)try{dbConn.close();}catch(SQLException ignore){}
	  			}
	  	  }
	  	  //更新数据库
	  	  new DatabaseUpdate();
	  	}
	  });
	  
	  table = new JTable(); 
	  Container c = getContentPane(); 
	  c.setLayout( new BorderLayout() ); 
	  //将"topPanel"编辑框布置到 "NORTH" 
	  c.add( topPanel, BorderLayout.NORTH ); 
	  //将"table"编辑框布置到 "CENTER" 
	  c.add( table, BorderLayout.CENTER );
	  setSize( 500, 300 );
	  //显示Frame 
	  show();
	  
	  Toolkit kit=Toolkit.getDefaultToolkit();
	  Dimension screenSize=kit.getScreenSize();
	  int width=screenSize.width;
	  int height=screenSize.height;
	  setLocation((width-getWidth())/2,(height-getHeight())/2);
	} 
	
	private void displayResultSet(ResultSet rs)throws SQLException{
		//定位到达第一条记录
		boolean moreRecords = rs.next();
		//如果没有记录，则提示一条消息
		if (! moreRecords){
			JOptionPane.showMessageDialog( this,"结果集中无记录" );
			setTitle( "无记录显示" );
			return;
		}
		
		Vector columnHeads = new Vector();
		Vector rows = new Vector();
		try{
			//获取字段的名称
			ResultSetMetaData rsmd = rs.getMetaData();
			for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
			  columnHeads.addElement( rsmd.getColumnName( i ) );
			//获取记录集
			do{
				rows.addElement( getNextRow( rs, rsmd ) );
			}while(rs.next());
			//在表格中显示查询结果
			table = new JTable( rows, columnHeads );
			JScrollPane scroller = new JScrollPane( table );
			
			c.remove(1);
			c.add( scroller, BorderLayout.CENTER );
			//刷新Table
			c.validate();
		}catch(SQLException sqlex){
			sqlex.printStackTrace();
		}
	} 
	
	private Vector getNextRow(ResultSet rs,ResultSetMetaData rsmd)throws SQLException{
		Vector currentRow = new Vector();
		for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
		  currentRow.addElement( rs.getString( i ) );
		  //返回一条记录
		  return currentRow;
	}
}