queryscore.java

用JAVA+SQL编写的一个教学管理软件

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

public class Queryscore extends JFrame{
	private int tech_id;
	private String tech_name;
	private JTextArea txa_score;
	private JComboBox cmb_cno;
	private JLabel lbl_cno,lbl_cname;
	private JPanel panel,panel2;
	private JButton btn_ok,btn_back,btn_print;
	private boolean hasCourse=false;
	private	File file=new File("Backup.xls");
	private String path=file.getAbsolutePath();
	public Queryscore(String name,int id){
		super("成绩查询统计");
		tech_id=id;
		tech_name=name;
		Container container=getContentPane();
		container.setLayout(new BorderLayout());
		txa_score=new JTextArea(15,15);
		txa_score.setFont(new Font("Serif",Font.PLAIN,16));
		txa_score.disable();
		txa_score.setDisabledTextColor(Color.BLACK);
		cmb_cno=new JComboBox();
		lbl_cno=new JLabel("请选择课程号");
		lbl_cname=new JLabel();
		btn_ok=new JButton("统计");
		btn_print=new JButton("导出成绩");
		btn_back=new JButton("返回");
		try{
			Connection conn=null;
	        DateSource date=new DateSource();
    	    conn=date.getconnection();
    	    String sql="SELECT id FROM course WHERE tech_id=?";
    	    PreparedStatement stmt=conn.prepareStatement(sql);
    	    stmt.setInt(1,tech_id);
    	    ResultSet rs=stmt.executeQuery();
    	    if(rs.next()){
    	    	hasCourse=true;
    	    	cmb_cno.addItem("");
    	    	do{cmb_cno.addItem(rs.getString(1));}while(rs.next());
    	 	}
    	 	
    	 	else cmb_cno.addItem("你没有课程");
    	 	rs.close();
    	 	conn.close();
		}catch(Exception e){}
		panel=new JPanel();
		panel.setLayout(new GridLayout(1,4,0,20));
		panel.add(lbl_cno);
		panel.add(cmb_cno);
		panel.add(lbl_cname);
		panel.add(btn_ok);
		panel2=new JPanel();
		panel2.setLayout(new GridLayout(1,2,0,20));
		panel2.add(btn_print);
		panel2.add(btn_back);
		btn_print.hide();
		container.add(txa_score,BorderLayout.CENTER);
		container.add(panel,BorderLayout.NORTH);
		container.add(panel2,BorderLayout.SOUTH);
		addWindowListener(new WindowAdapter(){
			public void windowClosing(WindowEvent e)
			{
				dispose();
				System.exit(0);
			}
		});
		setLocation(400,250);
		pack();
		setVisible(true);
		
		
		btn_back.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e){
				dispose();
				returnMenu();
			}	
		});
		
		cmb_cno.addItemListener(new ItemListener(){
        	public void itemStateChanged(ItemEvent event){
        		int index=cmb_cno.getSelectedIndex();
        		if(hasCourse){
        			if(event.getStateChange()==ItemEvent.SELECTED){
        				if(index==0)lbl_cname.setText("");
        				else try{
        						Connection conn=null;
		        				DateSource date=new DateSource();
    		    				conn=date.getconnection();
        						String sql="SELECT name FROM course WHERE id=?";
      				  			PreparedStatement stmt=conn.prepareStatement(sql);
        						stmt.setInt(1,Integer.parseInt(cmb_cno.getItemAt(index).toString()));
        						ResultSet rs=stmt.executeQuery();
        						if(rs.next())lbl_cname.setText(rs.getString(1));
        						rs.close();
        						conn.close();
        					}catch(Exception exc){
        						JOptionPane.showMessageDialog(txa_score,exc.getMessage(),"错误",1);
        					}
        				
        			}
        		}
        	
        	}	
		});
		
		btn_ok.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e){
				txa_score.setText("");
				int index=cmb_cno.getSelectedIndex();
				if(hasCourse){
					if(index!=0){
						String score="学号"+"\t"+"姓名"+"\t"+"成绩"+"\t"+"绩点"+"\n"
						+"--------------------------------------------------"+"\n";
						int courseCount=0;
						double scoreSum=0;
						int tempId=0;
						String tempName="";
						double tempScore=0;
						double point=0;
						try{
   			     			Connection conn=null;
        					DateSource date=new DateSource();
		        			conn=date.getconnection();
        					String sql="SELECT id,name,score FROM students JOIN score ON students.id=score.Stu_id WHERE Cou_id=? and score is NOT NULL";
      		  				PreparedStatement stmt=conn.prepareStatement(sql);
							stmt.setInt(1,Integer.parseInt(cmb_cno.getItemAt(index).toString()));
							ResultSet rs=stmt.executeQuery();
							while(rs.next()){
								tempId=rs.getInt(1);
								tempName=rs.getString(2);
								tempScore=rs.getDouble(3);
								point=getPoint(tempScore);
								score=score+tempId+"\t"+tempName+"\t"+tempScore+"\t"+point+"\n";
								scoreSum+=tempScore;
								courseCount++;
							}
							score=score+"--------------------------------------------------"+"\n"
							+"总计共有"+courseCount+"名学生，平均成绩为："+(scoreSum/courseCount)+"。";
							rs.close();
							conn.close();
							if(courseCount>0){txa_score.setText(score);btn_print.show();}
							else txa_score.setText("没有查到相应的记录！");
						}catch(Exception exc){JOptionPane.showMessageDialog(txa_score,exc.getMessage(),"错误",1);}
					}
					else txa_score.setText("请选择课程号！");
				}
				else txa_score.setText("对不起，您没有课程！");
			}
		});
		
		
		btn_print.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e){
				
				int index=cmb_cno.getSelectedIndex();
				try{
   			     	Connection conn=null;
        			DateSource date=new DateSource();
		        	conn=date.getconnection();
        			String sql="insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE="+path+"',sheet3$)SELECT course.id,course.name,students.id,students.name,score FROM students JOIN score ON students.id=score.Stu_id JOIN course ON score.Cou_id=course.id WHERE Cou_id=? and score is NOT NULL";			
					PreparedStatement stmt=conn.prepareStatement(sql);
					stmt.setInt(1,Integer.parseInt(cmb_cno.getItemAt(index).toString()));
					stmt.execute();
					stmt.close();
					conn.close();
					JOptionPane.showMessageDialog(txa_score,"导出成功","提示",1);
        		}catch(Exception exc){JOptionPane.showMessageDialog(txa_score,exc.getMessage(),"错误",1);}
			}
		});		
	
	}	
	private void returnMenu(){
		Cmenu user=new Cmenu("老师",tech_name,tech_id);
	}
	
	private double getPoint(double score){
		if(score>=90)return 4;
		else if(score>=85)return 3.5;
			else if(score>=80)return 3;
				else if(score>=75)return 2.5;
					else if(score>=70)return 2;
						else if(score>=65)return 1.5;
							else if(score>=60)return 1;
								else return 0;
	}
	

	

	
		
		
}