displayqueryresults.java

邮局订报系统.

//  DisplayQueryResults.java
//  Display the contents of the querying result in the  Study database.

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

public class DisplayQueryResults extends JFrame {
   
   // JDBC driver and database URL
   static final String JDBC_DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
   static final String DATABASE_URL = "jdbc:odbc:PostofficeDSN";
   String sex="男";
   // 定义SQL statement 客户代码,客户姓名,电话,邮编,地址
   
   String DEFAULT_QUERY = "select 客户代码,客户姓名,电话,邮编,地址 from customer";
   String DEFAULT_QUERY1 = "select * from newspaper";
   String DEFAULT_QUERY2 = "select 客户姓名,报纸名称 from customer,newspaper,subscription where customer.客户代码=subscription.客户代码 AND subscription.报纸编号=newspaper.报纸编号";
   String DEFAULT_QUERY3 = "select count(客户代码) as "+
  "客户人数 from subscription GROUP BY 报纸编号";
   String DEFAULT_QUERY4 = "select 报纸名称,SUM(ALL 报纸单价*份数) from subscription,newspaper where subscription.报纸编号=newspaper.报纸编号 GROUP BY subscription.报纸编号,报纸单价,份数,报纸名称";
   String DEFAULT_QUERY5= "select 客户姓名,SUM(ALL 报纸单价*份数) from customer,subscription,newspaper where subscription.客户代码=customer.客户代码 AND subscription.报纸编号=newspaper.报纸编号 GROUP BY subscription.客户代码,客户姓名,报纸单价,份数";
   
   int k=0;
   
   
   
   private ResultSetTableModel tableModel;
   private JTextArea queryArea;
   
   // create ResultSetTableModel and GUI
   public DisplayQueryResults(int k) 
   {   
      super( "Displaying Query Results" );
         this.k=k;
      // create ResultSetTableModel and display database table
      try {

          // create TableModel for results of query SELECT * FROM authors
         if(k==0)tableModel = new ResultSetTableModel( JDBC_DRIVER, DATABASE_URL, 
            DEFAULT_QUERY );
          if(k==1)tableModel = new ResultSetTableModel( JDBC_DRIVER, DATABASE_URL, 
            DEFAULT_QUERY1 );  
            
           if(k==2)tableModel = new ResultSetTableModel( JDBC_DRIVER, DATABASE_URL, 
            DEFAULT_QUERY2 ); 
           if(k==3)tableModel = new ResultSetTableModel( JDBC_DRIVER, DATABASE_URL, 
            DEFAULT_QUERY3 ); 
           if(k==4)tableModel = new ResultSetTableModel( JDBC_DRIVER, DATABASE_URL, 
            DEFAULT_QUERY4 ); 
           if(k==5)tableModel = new ResultSetTableModel( JDBC_DRIVER, DATABASE_URL, 
            DEFAULT_QUERY5 ); 
            
            

         // set up JTextArea in which user types queries
         queryArea = new JTextArea( DEFAULT_QUERY, 3, 100 );
         queryArea.setWrapStyleWord( true );
         queryArea.setLineWrap( true );
         
         JScrollPane scrollPane = new JScrollPane( queryArea,
            ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED, 
            ScrollPaneConstants.HORIZONTAL_SCROLLBAR_NEVER );
         
         // set up JButton for submitting queries
         JButton submitButton = new JButton( "Submit Query" );

         // create Box to manage placement of queryArea and 
         // submitButton in GUI
         Box box = Box.createHorizontalBox();
         box.add( scrollPane );
         box.add( submitButton );

         // create JTable delegate for tableModel 
         JTable resultTable = new JTable( tableModel );
         //将表格添加到可滚动的面板
         JScrollPane tableScrollPane=new JScrollPane(resultTable); 

         // place GUI components on content pane
         Container c = getContentPane();         
         c.add( box, BorderLayout.NORTH );
         c.add( new JScrollPane( tableScrollPane ), BorderLayout.CENTER );

         // create event listener for submitButton
         submitButton.addActionListener( 
         
            new ActionListener() {
         
               // pass query to table model
               public void actionPerformed( ActionEvent event )
               {
                  // perform a new query
                  try {
                     tableModel.setQuery( queryArea.getText() );
                  }
                  
                  // catch SQLExceptions when performing a new query
                  catch ( SQLException sqlException ) {
                     JOptionPane.showMessageDialog( null, 
                        sqlException.getMessage(), "Database error", 
                        JOptionPane.ERROR_MESSAGE );
                     
                     // try to recover from invalid user query 
                     // by executing default query
                     try {
                        tableModel.setQuery( DEFAULT_QUERY );
                        queryArea.setText( DEFAULT_QUERY );
                     }
                     
                     // catch SQLException when performing default query
                     catch ( SQLException sqlException2 ) {
                        JOptionPane.showMessageDialog( null, 
                           sqlException2.getMessage(), "Database error", 
                           JOptionPane.ERROR_MESSAGE );
         
                        // ensure database connection is closed
                        tableModel.disconnectFromDatabase();
         
                        System.exit( 1 );   // terminate application
                        
                     }  // end inner catch                   
             
                  } // end outer catch
                  
               }  // end actionPerformed
               
            }  // end ActionListener inner class          
            
         ); // end call to addActionListener

         // set window size and display window
         setSize( 500, 250 );
         setVisible( true );   
         
      }  // end try

      // catch ClassNotFoundException thrown by 
      // ResultSetTableModel if database driver not found
      catch ( ClassNotFoundException classNotFound ) {
         JOptionPane.showMessageDialog( null, 
            "Cloudscape driver not found", "Driver not found",
            JOptionPane.ERROR_MESSAGE );
         
         System.exit( 1 );   // terminate application
      }
      
      // catch SQLException thrown by ResultSetTableModel 
      // if problems occur while setting up database
      // connection and querying database
      catch ( SQLException sqlException ) {
         JOptionPane.showMessageDialog( null, sqlException.getMessage(), 
            "Database error", JOptionPane.ERROR_MESSAGE );
         
         // ensure database connection is closed
         tableModel.disconnectFromDatabase();
         
         System.exit( 1 );   // terminate application
      }
      
      // dispose of window when user quits application (this overrides
      // the default of HIDE_ON_CLOSE)
      setDefaultCloseOperation( DISPOSE_ON_CLOSE );
      
      // ensure database connection is closed when user quits application
      addWindowListener(
      
         new WindowAdapter() {
            
            // disconnect from database and exit when window has closed
            public void windowClosed( WindowEvent event )
            {
               tableModel.disconnectFromDatabase();
              // System.exit( 0 );
            }
         }
      );      
      
   }  // end DisplayQueryResults constructor
   
   // execute application
   /*public static void main( String args[] ) 
   {
      new DisplayQueryResults(4);     
   }*/
   
}  // end class DisplayQueryResults