import javax.swing.*;
import java.awt.*;
import java.sql.*;
import java.awt.event.*;
public class PrepareQuery extends JFrame
implements ActionListener {
  JTextField tfDepartment=new JTextField();
  JLabel lbDepartment=new JLabel("系別");
  JButton btnQuery=new JButton("查詢");
  JPanel panel=new JPanel();
  JTextArea taInfo=new JTextArea();
  public PrepareQuery()   {
    super("預編譯語句");
    setSize(350,260);
    this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE );
    panel.setLayout(new BorderLayout());
    panel.add(lbDepartment,BorderLayout.WEST);
    panel.add(tfDepartment,BorderLayout.CENTER);
    panel.add(btnQuery,BorderLayout.EAST);
    btnQuery.addActionListener(this);
    getContentPane().setLayout(new BorderLayout());
    getContentPane().add(panel,BorderLayout.NORTH);
    getContentPane().add(taInfo);
  }
  public void getRecord(String strDepartment)throws SQLException {
    Connection con=null;
    try {
    //加載驅動程序
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   }
   catch(ClassNotFoundException ex){
    taInfo.setText(ex.getMessage());
    System.exit(-1);
   }
   try {
     //試圖通過ODBC建立一個與學生管理數據庫的連接
     String  URL = "jdbc:odbc:學生信息管理";
      con = DriverManager.getConnection(URL);
      //定義帶參數的預編譯語句
      String SQL="SELECT 學號,姓名,年齡,系別 FROM 學生信息表"
          +" WHERE 系別=?";
      PreparedStatement preSt = con.prepareStatement(SQL);
      //為參數指定值
      preSt.setString(1,strDepartment);
      ResultSet rs=preSt.executeQuery();
      taInfo.setText("");//清空文本框
     while (rs.next()) {
         //將結果集中的數據添加到文本框中
         taInfo.append(rs.getString("學號") + "\t");
         taInfo.append(rs.getString("姓名") + "\t");
         taInfo.append(rs.getString("年齡") + "\t");
         taInfo.append(rs.getString("系別") + "\n");
       }
       rs.close();
       preSt.close();
      }
      catch(SQLException ex){
       taInfo.setText(ex.getMessage());
      }
      finally{
        con.close();
      }
  }
  public static void main(String[] args)   {
    PrepareQuery frame = new PrepareQuery();
    frame.show();
  }
  public void actionPerformed(ActionEvent e)  {
    if (e.getSource() ==btnQuery){
      String str=tfDepartment.getText();
      try{
        getRecord(str);
      }
      catch(SQLException ex){
        taInfo.setText(ex.getMessage());
      }
    }
  }
}