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

public class DisplayQueryResults extends JFrame { 

   //數據庫變量定義
   private Connection connection; 
   private Statement statement; 
   private ResultSet resultSet; 
   private ResultSetMetaData rsMetaData; 

   //GUI變量定義
   private JTable table; 
   private JTextArea inputQuery; 
   private JButton submitQuery; 
    
   public DisplayQueryResults() 
   {  
      //Form的標題	 
      super( "輸入SQL語句，按提交按鈕查看結果。" ); 
      //url中指定ODBC中設置的DSN名稱
      String url = "jdbc:odbc:AddressBook"; 
      String username = ""; 
      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(); 
         System.exit( 1 );  // terminate program 
      } 
      //捕獲連接數據庫異常
      catch ( SQLException sqlex ) { 
         System.err.println( "無法連接數據庫" ); 
         sqlex.printStackTrace(); 
         System.exit( 1 );  // terminate program 
      } 
      //如果數據庫連接成功，則建立GUI
      //SQL語句
      String test="SELECT * FROM TblAddressBook"; 
      inputQuery = new JTextArea( test, 4, 30 ); 
      submitQuery = new JButton( "查詢" ); 
      //Button事件
      submitQuery.addActionListener( 
         new ActionListener() { 
            public void actionPerformed( ActionEvent e ) 
            { 
               getTable(); 
            } 
         }   
      ); 

      JPanel topPanel = new JPanel(); 
      topPanel.setLayout( new BorderLayout() ); 
      //將"輸入查詢"編輯框布置到 "CENTER"
      topPanel.add( new JScrollPane( inputQuery), BorderLayout.CENTER ); 
      //將"提交查詢"按鈕布置到 "SOUTH"
      topPanel.add( submitQuery, BorderLayout.SOUTH ); 
      table = new JTable(); 
      Container c = getContentPane(); 
      c.setLayout( new BorderLayout() ); 
      //將"topPanel"編輯框布置到 "NORTH"
      c.add( topPanel, BorderLayout.NORTH ); 
      //將"table"編輯框布置到 "CENTER"
      c.add( table, BorderLayout.CENTER );      
      getTable(); 
      setSize( 500, 300 ); 
      //顯示Form
      show(); 
   } 

   private void getTable() 
   { 
      try { 
      	 //執行SQL語句
         String query = inputQuery.getText(); 
         statement = connection.createStatement(); 
         resultSet = statement.executeQuery( query );         
         //在表格中顯示查詢結果
         displayResultSet( resultSet ); 
      } 
      catch ( SQLException sqlex ) { 
         sqlex.printStackTrace(); 
      } 
   } 

   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 ); 
         Container c = getContentPane(); 
         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; 
   } 

   public void shutDown() 
   { 
      try { 
      	 //斷開數據庫連接
         connection.close(); 
      } 
      catch ( SQLException sqlex ) { 
         System.err.println( "Unable to disconnect" ); 
         sqlex.printStackTrace(); 
      } 
   } 

   public static void main( String args[] ) 
   { 
      final DisplayQueryResults app = 
         new DisplayQueryResults(); 
      app.addWindowListener( 
         new WindowAdapter() { 
            public void windowClosing( WindowEvent e ) 
            {  
               app.shutDown(); 
               System.exit( 0 ); 
            } 
         } 
      ); 
   } 
}