basicdiath.java

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

import java.sql.*; 
import javax.swing.*; 
import java.awt.*; 
import java.awt.event.*; 
import java.util.*; 

public class BasicDiath extends JFrame{
	private Connection dbConn;
	private PreparedStatement ps;
	private ResultSet rs;
	private ResultSetMetaData rsMetaData;
	
	//GUI变量定义
	private JTable table;
	private JTextArea inputQuery; 
	private JButton submitQuery;
	Container c=getContentPane();
	
	public BasicDiath() 
	{
		//Form的标题 
		super("基本素质测试"); 
		
		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
		}
		
		//如果数据库连接成功，则建立GUI
		//Button事件
		
		JPanel topPanel = new JPanel();
		GridBagLayout lay=new GridBagLayout();
		GridBagConstraints constraints=new GridBagConstraints();
		topPanel.setLayout(lay);
		//Button ok_button=new Button("确定");
	  JButton modify_button=new JButton("修改");
	  JButton exit_button=new JButton("退出");
	  JButton add_button=new JButton("添加");
	  JButton delete_button=new JButton("删除");
	  
	  JLabel studentno_label=new JLabel("学号: ");
	  final JTextField studentno_textfield=new JTextField();
	  JLabel basicgrade_label=new JLabel("基础分: ");
	  final JTextField basicgrade_textfield=new JTextField();
	  JLabel addgrade_label=new JLabel("加分: ");
	  final JTextField addgrade_textfield=new JTextField();
	  JLabel reducegrade_label=new JLabel("减分: ");
	  final JTextField reducegrade_textfield=new JTextField();
	  String[] temp={"2006-2007","2007-2008","2008-2009","2009-2010"};
	  JLabel acadyear_label=new JLabel("学年: ");
	  final JComboBox acadyear_combobox=new JComboBox(temp);
	   
	  JLabel userinfo_label=new JLabel("基本素质得分:");
			
	  constraints.anchor=GridBagConstraints.CENTER;
	     
	  constraints.gridx=0;
	  constraints.gridy=0;
	  constraints.weightx=1;
	  constraints.weighty=1;
	  constraints.gridwidth=1;
	  constraints.gridheight=1;
	  constraints.fill=GridBagConstraints.NONE;
	  constraints.anchor=GridBagConstraints.EAST;
	  topPanel.add(studentno_label,constraints);
	      
	  constraints.gridx=1;
	  constraints.gridy=0;
	  constraints.weightx=1;
	  constraints.weighty=2;
	  constraints.gridwidth=1;
	  constraints.gridheight=1;
	  constraints.fill=GridBagConstraints.BOTH;
	  constraints.anchor=GridBagConstraints.CENTER;
	  topPanel.add(studentno_textfield,constraints);
	     
	  constraints.gridx=2;
	  constraints.gridy=0;
	  constraints.weightx=1;
	  constraints.weighty=1;
	  constraints.gridwidth=1;
	  constraints.gridheight=1;
	  constraints.fill=GridBagConstraints.NONE;
	  constraints.anchor=GridBagConstraints.CENTER;
	  topPanel.add(modify_button,constraints);
	           
	  constraints.gridx=0;
	  constraints.gridy=1;
	  constraints.weightx=1;
	  constraints.weighty=1;
	  constraints.gridwidth=1;
	  constraints.gridheight=1;
	  constraints.fill=GridBagConstraints.NONE;
	  constraints.anchor=GridBagConstraints.EAST;
	  topPanel.add(basicgrade_label,constraints);
	     
	  constraints.gridx=1;
	  constraints.gridy=1;
	  constraints.weightx=1;
	  constraints.weighty=2;
	  constraints.gridwidth=1;
	  constraints.gridheight=1;
	  constraints.fill=GridBagConstraints.BOTH;
	  constraints.anchor=GridBagConstraints.CENTER;
	  topPanel.add(basicgrade_textfield,constraints);
	  
	  constraints.gridx=2;
	  constraints.gridy=1;
	  constraints.weightx=1;
	  constraints.weighty=2;
	  constraints.gridwidth=1;
	  constraints.gridheight=1;
	  constraints.fill=GridBagConstraints.NONE;
	  constraints.anchor=GridBagConstraints.CENTER;
	  topPanel.add(exit_button,constraints);
	  
	  constraints.gridx=0;
	  constraints.gridy=2;
	  constraints.weightx=1;
	  constraints.weighty=1;
	  constraints.gridwidth=1;
	  constraints.gridheight=1;
	  constraints.fill=GridBagConstraints.NONE;
	  constraints.anchor=GridBagConstraints.EAST;
	  topPanel.add(addgrade_label,constraints);
	     
	  constraints.gridx=1;
	  constraints.gridy=2;
	  constraints.weightx=1;
	  constraints.weighty=2;
	  constraints.gridwidth=1;
	  constraints.gridheight=1;
	  constraints.fill=GridBagConstraints.BOTH;
	  constraints.anchor=GridBagConstraints.CENTER;
	  topPanel.add(addgrade_textfield,constraints);
	  
	  constraints.gridx=0;
	  constraints.gridy=3;
	  constraints.weightx=1;
	  constraints.weighty=1;
	  constraints.gridwidth=1;
	  constraints.gridheight=1;
	  constraints.fill=GridBagConstraints.NONE;
	  constraints.anchor=GridBagConstraints.EAST;
	  topPanel.add(reducegrade_label,constraints);
	     
	  constraints.gridx=1;
	  constraints.gridy=3;
	  constraints.weightx=1;
	  constraints.weighty=2;
	  constraints.gridwidth=1;
	  constraints.gridheight=1;
	  constraints.fill=GridBagConstraints.BOTH;
	  constraints.anchor=GridBagConstraints.CENTER;
	  topPanel.add(reducegrade_textfield,constraints);
	  
	  constraints.gridx=0;
	  constraints.gridy=4;
	  constraints.weightx=1;
	  constraints.weighty=1;
	  constraints.gridwidth=1;
	  constraints.gridheight=1;
	  constraints.fill=GridBagConstraints.NONE;
	  constraints.anchor=GridBagConstraints.EAST;
	  topPanel.add(acadyear_label,constraints);
	     
	  constraints.gridx=1;
	  constraints.gridy=4;
	  constraints.weightx=1;
	  constraints.weighty=2;
	  constraints.gridwidth=1;
	  constraints.gridheight=1;
	  constraints.fill=GridBagConstraints.BOTH;
	  constraints.anchor=GridBagConstraints.CENTER;
	  topPanel.add(acadyear_combobox,constraints);
	      
	  constraints.gridx=0;
	  constraints.gridy=5;
	  constraints.weightx=1;
	  constraints.weighty=1;
	  constraints.gridwidth=2;
	  constraints.gridheight=1;
	  constraints.fill=GridBagConstraints.NONE;
	  constraints.anchor=GridBagConstraints.CENTER;
	  topPanel.add(add_button,constraints);
	      
	  constraints.gridx=1;
	  constraints.gridy=5;
	  constraints.weightx=1;
	  constraints.weighty=1;
	  constraints.gridwidth=2;
	  constraints.gridheight=1;
	  constraints.fill=GridBagConstraints.NONE;
	  constraints.anchor=GridBagConstraints.CENTER;
	  topPanel.add(delete_button,constraints);
	  
	  modify_button.addActionListener(new ActionListener(){
	  	public void actionPerformed(ActionEvent e){
	  		String[] temp={"","","",""};
	  		temp[0]=studentno_textfield.getText();
	  		temp[1]=basicgrade_textfield.getText();
	  		temp[2]=addgrade_textfield.getText();
	  		temp[3]=reducegrade_textfield.getText();
	  		if(temp[0].equals("")||temp[1].equals("")||temp[2].equals("")||temp[3].equals("")){
	  			JOptionPane.showMessageDialog(new JFrame(),"记录不能为空","警告",JOptionPane.WARNING_MESSAGE);
	  		}else{
	  		  try{
	  		    dbConn=DriverManager.getConnection(dbURL,username,password);
	  				String selectStr="select * from colligate where sno=? and Academic_year=?";
	  				ps=dbConn.prepareStatement(selectStr);
	  				ps.setString(1,temp[0]);
	  				ps.setString(2,(String)acadyear_combobox.getSelectedItem());
	  				rs=ps.executeQuery();
	  				if(rs.next()){
	  					String updateStr="update colligate set F1_basic=?,F1_add=?,F1_reduce=? WHERE sno=?";
	  			    ps=dbConn.prepareStatement(updateStr);
	  			    ps.setFloat(1,Float.parseFloat(basicgrade_textfield.getText()));
	  			    ps.setFloat(2,Float.parseFloat(addgrade_textfield.getText()));
	  			    ps.setFloat(3,Float.parseFloat(reducegrade_textfield.getText()));
	  			    ps.setString(4,rs.getString("sno"));
	  			    ps.executeUpdate();
	  			    
	  			    selectStr="select sno as '学号',F1_basic as '基础分',F1_add as '加分',F1_reduce as '减分',Academic_year as '学年' from colligate where sno=? and Academic_year=?";
	  				  ps=dbConn.prepareStatement(selectStr);
	  				  ps.setString(1,temp[0]);
	  				  ps.setString(2,(String)acadyear_combobox.getSelectedItem());
	  				  rs=ps.executeQuery();
	  			    JOptionPane.showMessageDialog(new JFrame(),"修改成功","成功",JOptionPane.WARNING_MESSAGE);
	  			    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]=studentno_textfield.getText();
	  		temp[1]=basicgrade_textfield.getText();
	  		temp[2]=addgrade_textfield.getText();
	  		temp[3]=reducegrade_textfield.getText();
	  		if(temp[0].equals("")||temp[1].equals("")||temp[2].equals("")||temp[3].equals("")){
	  			JOptionPane.showMessageDialog(new JFrame(),"记录不能为空","警告",JOptionPane.WARNING_MESSAGE);
	  		}else{
	  		  try{
	  		    dbConn=DriverManager.getConnection(dbURL,username,password);
	  				String selectStr="select * from colligate where sno=? and Academic_year=?";
	  				ps=dbConn.prepareStatement(selectStr);
	  				ps.setString(1,temp[0]);
	  				ps.setString(2,(String)acadyear_combobox.getSelectedItem());
	  				rs=ps.executeQuery();
	  				if(!rs.next()){
	  					String insertStr="insert into colligate (sno,F1_basic,F1_add,F1_reduce,Academic_year) values(?,?,?,?,?)";
	  			    ps=dbConn.prepareStatement(insertStr);
	  			    ps.setString(1,studentno_textfield.getText());
	  			    ps.setFloat(2,Float.parseFloat(basicgrade_textfield.getText()));
	  			    ps.setFloat(3,Float.parseFloat(addgrade_textfield.getText()));
	  			    ps.setFloat(4,Float.parseFloat(reducegrade_textfield.getText()));
	  			    ps.setString(5,(String)acadyear_combobox.getSelectedItem());
	  			    ps.executeUpdate();
	  			    
	  			    selectStr="select sno as '学号',F1_basic as '基础分',F1_add as '加分',F1_reduce as '减分',Academic_year as '学年' from colligate where sno=? and Academic_year=?";
	  				  ps=dbConn.prepareStatement(selectStr);
	  				  ps.setString(1,temp[0]);
	  				  ps.setString(2,(String)acadyear_combobox.getSelectedItem());
	  				  rs=ps.executeQuery();
	  			    JOptionPane.showMessageDialog(new JFrame(),"添加成功","成功",JOptionPane.WARNING_MESSAGE);
	  			    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();
	  	}
	  });
	  
	  delete_button.addActionListener(new ActionListener(){
	  	public void actionPerformed(ActionEvent e){
	  		try{
	  		  dbConn=DriverManager.getConnection(dbURL,username,password);
	  		  String selectStr="select * from colligate where sno=? and Academic_year=?";
	  			ps=dbConn.prepareStatement(selectStr);
	  			ps.setString(1,studentno_textfield.getText());
	  			ps.setString(2,(String)acadyear_combobox.getSelectedItem());
	  			rs=ps.executeQuery();
	  			if(rs.next()){
	  				String deleteStr="delete colligate where sno=? and Academic_year=?";
	  			   ps=dbConn.prepareStatement(deleteStr);
	  			   ps.setString(1,studentno_textfield.getText());
	  			   ps.setString(2,(String)acadyear_combobox.getSelectedItem());
	  			   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;
	}
}