/**
 * <p>Title: 以表格顯示數據庫查詢結果</p>
 * <p>Description: 第五章例子</p>
 * <p>Copyright: Copyright (c) 2006</p>
 * <p>Company: </p>
 * @author 蘇年樂
 * @version 1.0
 */

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

public class TableDisplay 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 TableDisplay() {
    //Form的標題
    super("輸入SQL語句，按提交按鈕查看結果。");
    //url中指定ODBC中設置的DSN名稱
    String url = "jdbc:odbc:Person";
    String username = "";
    String password = "";
    //加載驅動程序以連接數據庫
    try {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      connection = DriverManager.getConnection(
          url, username, password);
    }
    //捕獲加載驅動程序異常
    catch (ClassNotFoundException e) {
      System.err.println("裝載JDBC/ODBC驅動程序失敗。");
      e.printStackTrace();
      System.exit(1); // terminate program
    }
    //捕獲連接數據庫異常
    catch (SQLException e) {
      System.err.println("無法連接數據庫");
      e.printStackTrace();
      System.exit(1); // terminate program
    }
    //如果數據庫連接成功，則建立GUI
    //SQL語句
    String test = "SELECT * FROM Person";
    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 e) {
      e.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 e) {
      System.err.println("Unable to disconnect");
      e.printStackTrace();
    }
  }

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