stat.java

本系统的设计模拟一般学校特别是高校的学生选课内容

//package myprojects.main;
import java.sql.*;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.util.*;
import java.io.*;

public class Stat extends JFrame implements ActionListener
{
   int select=0;
   private JTable output; //显示表的内容
   private String url; //定位数据库的数据库URL
   private Connection connection;//Connection对象管理java程序和数据库的连接
   private JScrollPane textpane;//滚动区域对象
   private ResultSet resultSet;//保存结果
   Statement statement ;//将向数据库递交查询
   private Container c = getContentPane();


   JPanel panel;
   JLabel label;
   JButton button,cancle;
   JRadioButton  totalButton,aveButton;
   
   
   public Stat(int select)
   {
   	  
      super( "学生选课管理系统－－统计窗口" );
      this.select=select;
      panel =new JPanel(); 
      button = new JButton("确定");
      button.addActionListener(this);
      cancle = new JButton("返回");
      cancle.addActionListener(this);
      ButtonGroup group = new ButtonGroup();
      totalButton = new JRadioButton("按总分排序",true);
      group.add(totalButton);
      //textinputButton.addItemListener(this);
      
      aveButton= new JRadioButton("按平均分排序",false);
      group.add(aveButton);
      //graphicButton.addItemListener(this); 

      panel.setBorder(BorderFactory.createTitledBorder("排序方式"));
      panel.add(totalButton);
      panel.add(aveButton);
      panel.add(button);
      panel.add(cancle);
      
      c.setLayout( new BorderLayout() );//布局管理器
      c.add( new JScrollPane( panel ),BorderLayout.NORTH );
      
      textpane = new JScrollPane( output );
      c.add( new JScrollPane( output ),BorderLayout.CENTER );
      
      setVisible(true);
      setBounds(280,180,420,300);
      validate();
  }
   
  public void actionPerformed(ActionEvent e) 
  {
    if(e.getSource()==button)
    {  

        String query=null;
        if(select==0)
        {
           if(totalButton.isSelected())
           {
              query = "SELECT '学号'=成绩表.学号,'姓名'=姓名,'总分'=SUM(分数),'平均分'=AVG(分数) "
                +"FROM 学生基本信息表,成绩表 WHERE 学生基本信息表.学号=成绩表.学号"
                +" GROUP BY 成绩表.学号,姓名 ORDER BY SUM(分数) DESC";
           }
           else
           {
              query = "SELECT '学号'=成绩表.学号,'姓名'=姓名,'总分'=SUM(分数),'平均分'=AVG(分数) "
                +"FROM 学生基本信息表,成绩表 WHERE 学生基本信息表.学号=成绩表.学号"
                +" GROUP BY 成绩表.学号,姓名 ORDER BY AVG(分数) DESC";
           }
   	    }
   	    else if(select==1)
   	    {
              query = "SELECT '学号'=成绩表.学号,'姓名'=姓名,'总分'=SUM(分数),'平均分'=AVG(分数) "
                +" FROM 学生基本信息表,成绩表,课程信息表,教师表 "
                +" WHERE 学生基本信息表.学号=成绩表.学号 "
                +" AND 课程信息表.课程号=成绩表.课程号 "
                +" AND 课程信息表.教师=教师表.教师 "
                +" AND 教师表.登陆帐号='"
                +LoginFrame.userNameTextField.getText()
                +"' GROUP BY 成绩表.学号,姓名 ORDER BY SUM(分数) DESC";
           }
           else
           {
              query = "SELECT '学号'=成绩表.学号,'姓名'=姓名,'总分'=SUM(分数),'平均分'=AVG(分数) "
                +" FROM 学生基本信息表,成绩表,课程信息表,教师表 "
                +" WHERE 学生基本信息表.学号=成绩表.学号 "
                +" AND 课程信息表.课程号=成绩表.课程号 "
                +" AND 课程信息表.教师=教师表.教师 "
                +" AND 教师表.登陆帐号='"
                +LoginFrame.userNameTextField.getText()
                +"' GROUP BY 成绩表.学号,姓名 ORDER BY AVG(分数) DESC";
           }
   	    getTable(query);
    }
    else if(e.getSource()==cancle)
    {
    	setVisible(false);
        dispose();
    }
  }     



 /*getTable*/    
    private void getTable(String commodityquery) 
    { 
        String url = "jdbc:odbc:java"; 
    	String username = "sa"; 
    	String password = ""; 
    	//加载驱动程序以连接数据库
      	try 
      	{ 
        	Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ); 
        	connection = DriverManager.getConnection( url, username, password ); 
      	} 
      	//捕获加载驱动程序异常
      	catch ( ClassNotFoundException cnfex ) 
      	{ 
        	System.err.println("装载 JDBC/ODBC 驱动程序失败。" ); 
        	cnfex.printStackTrace(); 
        	JOptionPane.showMessageDialog ( this ,cnfex ,
          "学生选课管理系统",JOptionPane.WARNING_MESSAGE ); 
         	System.exit( 1 );  // terminate program 
      	} 
      	//捕获连接数据库异常
      	catch ( SQLException sqlex ) 
      	{ 
         	System.err.println( "无法连接数据库" ); 
         	sqlex.printStackTrace(); 
         	JOptionPane.showMessageDialog ( this ,sqlex ,
          "学生选课管理系统",JOptionPane.WARNING_MESSAGE ); 
         	System.exit( 1 );  // terminate program 
      	}
      try 
      { 
      	 //执行SQL语句
         statement = connection.createStatement(); 
         resultSet = statement.executeQuery( commodityquery );       
         //在表格中显示查询结果
         displayResultSet( resultSet);
      } 
      catch ( SQLException sqlex ) { 
         sqlex.printStackTrace(); 
         JOptionPane.showMessageDialog ( this ,sqlex ,
          "学生选课管理系统",JOptionPane.WARNING_MESSAGE ); 
      } 
   }  	
  /*displayResultSet*/   
   private void displayResultSet( ResultSet commodityrs ) 
      throws SQLException 
   { 
      //定位到达第一条记录
     
      boolean commoditymoreRecords = commodityrs.next();
      //如果没有记录，则提示一条消息
      
      if ( ! commoditymoreRecords ) 
      { 
         JOptionPane.showMessageDialog( this,"无此记录" ); 
         //setTitle( "无记录显示" ); 
      	 return ;
      } 
     
      Vector commoditycolumnHeads = new Vector(); 
      Vector commodityrows = new Vector(); 
      try
      {
         //获取字段的名称
         ResultSetMetaData rsmd = commodityrs.getMetaData(); 
         for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
         { 
            commoditycolumnHeads.addElement( rsmd.getColumnName( i ) ); 
         }
          //获取记录集
         do 
         { 
            commodityrows.addElement( getNextRow( commodityrs, rsmd ) ); 
         } while ( commodityrs.next() ); 
         //在表格中显示查询结果
         output = new JTable ( commodityrows, commoditycolumnHeads );//将显示数据的output实例化
         textpane = new JScrollPane( output );
         textpane.setSize(800, 20);
         c.remove( 1 );
         c.add( textpane , BorderLayout.CENTER );
         c.validate();
      } 
      catch ( SQLException sqlex )
	  { 
         JOptionPane.showMessageDialog ( this ,sqlex ,
          "学生选课管理系统",JOptionPane.WARNING_MESSAGE ); 
      } 
   } 
/*getNextRow*/
   private Vector getNextRow( ResultSet rs, ResultSetMetaData rsmd )throws SQLException
   {
      Vector currentRow = new Vector();
      for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
      switch( rsmd.getColumnType( i ) ) //判断数据类型
      {
         case Types.CHAR:
         case Types.VARCHAR:
         case Types.LONGVARCHAR: currentRow.addElement( rs.getString( i ) );
                                 break;
         case Types.NUMERIC:
         case Types.DECIMAL:     currentRow.addElement( rs.getBigDecimal( i ) );
                                 break;  
         case Types.DATE:        currentRow.addElement( rs.getDate( i ) );
                                 break;                                                 
         case Types.SMALLINT:    
         case Types.INTEGER:     currentRow.addElement(new Integer( rs.getInt( i ) ) );
                                 break;
         default:                //System.out.println( "Type was: " +rsmd.getColumnTypeName( i ) );
                                 currentRow.addElement(rs.getString( i ));
                                 break;
      }//end_Switch
      return currentRow;//返回当前行
   }
}