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

 
public class storedprocedure extends JFrame implements ItemListener {
    
     
    Connection conn;
    CallableStatement cstm1;
    CallableStatement cstm2;
    ResultSet rs1;
    ResultSet rs2;
    JTable table;
    DefaultTableModel dtm;
    DefaultComboBoxModel dcm;
    JComboBox comb;
    JLabel label1 = new JLabel("请选择课程名称：");;
    JLabel label2 = new JLabel("考试人数：");
    JLabel label3 = new JLabel("平均分：");
    Vector<String> title = new Vector<String>();
    
     
    public storedprocedure (){
        
        try{
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        }catch(Exception e1){JOptionPane.showMessageDialog(null,"驱动获取失败！");}
        try{
            Connection conn =DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=练习","sa","");
        }catch(SQLException e2){JOptionPane.showMessageDialog(null,"数据库连接失败！");}
        try{
            cstm1 = conn.prepareCall("{call getcoursename()}");
            rs1 = cstm1.executeQuery();
            dcm = new DefaultComboBoxModel();
            comb = new JComboBox(dcm);
            initcombobox();
            cstm2 = conn.prepareCall("{?=call stat(?,?)}", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            cstm2.registerOutParameter(1, java.sql.Types.INTEGER);
            cstm2.setString(2, (String)comb.getSelectedItem()+"%");
            cstm2.registerOutParameter(3, java.sql.Types.FLOAT);
            rs2 = cstm2.executeQuery();
            
            ResultSetMetaData dbmd = rs2.getMetaData();
            
            for(int i=1 ;i<=dbmd.getColumnCount();i++){
                title.addElement(dbmd.getColumnName(i));
            }
            dtm = new DefaultTableModel(null,title);
            table = new JTable(dtm);
            inittable();
            int count = cstm2.getInt(1);
            Float avg = Math.round(cstm2.getFloat(3)*100f)/100.0f;
            table.setRowHeight(20);
            label2.setText("考试人数："+count);
            label3.setText("平均分："+avg);
            
            JPanel pane = new JPanel();
            pane.add(label1);pane.add(comb);pane.add(label2);pane.add(label3);
            JScrollPane jp = new JScrollPane(table);
            Container con = getContentPane();
            con.setLayout(new BorderLayout());
            con.add(pane,"North");
            con.add(pane,"Center");
            comb.addItemListener(this);
        }catch(Exception e4){e4.printStackTrace();}
        this.addWindowListener(new WindowAdapter(){
            public void WindowClosing(WindowEvent e){
                try{
                    rs1.close();
                    rs2.close();
                    cstm1.close();
                    cstm2.close();
                    conn.close();
                }catch(SQLException e6){e6.printStackTrace();};
            }
        });
        setTitle("学生成绩浏览");
        Dimension screen = Toolkit.getDefaultToolkit().getScreenSize();
        setLocation((screen.width-400)/2,(screen.height-400)/2);
        setSize(600,400);
        setVisible(true);
    }
     
    public void inittable(){
        
        dtm.setRowCount(0);
        try{
            Vector<String> v = new Vector<String>();
            rs2.beforeFirst();
            while(rs2.next()){
            for(int i=1;i<=title.size();i++){
                v.addElement(rs2.getString(i));
            }
            dtm.addRow(v);
            }
        }catch(Exception e5){e5.printStackTrace();};
    }
 
    public void initcombobox(){
        dcm.removeAllElements();
        try{
            while(rs1.next()){
                dcm.addElement(rs1.getString(1).trim());
            }
            
        }catch(Exception e3){e3.printStackTrace();}
    }
     
    public void itemStateChanged(ItemEvent e){
        
        if(e.getStateChange()==ItemEvent.SELECTED){
            try{
            cstm2.registerOutParameter(1, java.sql.Types.INTEGER);
            cstm2.setString(2, (String)comb.getSelectedItem()+"%");
            cstm2.registerOutParameter(3, java.sql.Types.FLOAT);
            rs2 = cstm2.executeQuery();
            inittable();
            int count = cstm2.getInt(1);
            Float avg = Math.round(cstm2.getFloat(3)*100f)/100.0f;
            table.setRowHeight(20);
            label2.setText("考试人数："+count);
            label3.setText("平均分："+avg);
            
            }catch(Exception e7){e7.printStackTrace();}
        }
    }
    
    public static void main (String[] args){
        
        new storedprocedure();
    }

}