/**********************************
 * FileName:QueryFrame.java
 * Function:讀者資料查詢
 * Time:2004 1.8
 *********************************/

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

class UserQueryFrame extends Frame implements ActionListener 
{   
	Panel topPanel = new Panel ();
	Label lbUserName = new Label ("讀者姓名");
	Label lbResult = new Label ("結果顯示");
	TextField tfUserName = new TextField (30);
	TextField tfResult = new TextField (20);
	Button butQuery = new Button("查詢");
	Button butClear = new Button ("清空");
	Button butExit = new Button ("退出");
	JTable tbUQResult;
	JScrollPane tbPane1;
	private Vector vect = new Vector ();
	String labels[] = {"讀者ID","讀者姓名","工作單位"};
    ConDB conUserQuery= new ConDB();
	private int userY =0;

	UserQueryFrame()
	{
		setTitle("讀者資料查詢窗口");
		BorderLayout gbLayout = new BorderLayout();
		setLayout(gbLayout);
		tbUQResult = new JTable(tm);
	    tbUQResult.setToolTipText("讀者資料記錄");
	    tbUQResult.setCellSelectionEnabled(false);
	    tbUQResult.setShowVerticalLines(true);
	    tbUQResult.setShowHorizontalLines(true);
	    tbPane1 = new  JScrollPane(tbUQResult);
		
		
		//面板topPanel設置
		topPanel.setLayout(new GridLayout(1,3));
	    topPanel.add(lbUserName);
	    topPanel.add(tfUserName);
	    topPanel.add(butQuery);
		topPanel.add(butClear);
	    topPanel.add(butExit); 
		topPanel.add(lbResult);
		topPanel.add(tfResult);
		tfResult.setEditable (false);
	   
	    add("North",topPanel);
	    add("Center",tbPane1);
		butQuery.addActionListener (this);
		butClear.addActionListener (this);
	    butExit.addActionListener (this);
	    this.setVisible (true);
	    this.setSize(900,600);
	    this.setLocation (100,100);	
		
	    addWindowListener(new WindowAdapter() 
	   {
		  public void windowClosing(WindowEvent e) 
        	{
               System.exit (0);
        	}
      	 });
		openDB();
	 
	}//end of new UserQueryFrame()
	
	
	//設定數據
        //聲明一個類AbstractTableModel對象
        //實現AbstractTableModel對象tm中的方法
      AbstractTableModel tm= new AbstractTableModel() 
		{
			//取得表格列數
			public int getColumnCount() { return labels.length; }
            //取得表格行數
			public int getRowCount() { return vect.size();}
            //取得單元格中的屬性值
			public Object getValueAt(int arow, int acol) 
			{ 
				Vector row = (Vector)vect.elementAt(arow);
	            return row.elementAt(acol);
			 }
			//設置表格列名
		   public  String getColumnName(int column)
			{
			   if (labels[column] != null) 
			   {
            	  return labels[column];
        		}
			
			   else 
			  {
				   return "";
        		
			   }
		    }
			
			//數據模型不可編輯,該方法設置為空
			public void setValueAt(Object value,int arow,int acol){  }
			
		    //取得列所屬對象類
			public  Class getColumnClass(int column)
			{
      	        int type;
      	        try {
      	            type = conUserQuery.rs.getMetaData().getColumnType(column+1);
      	        }
      	        catch (SQLException e) {
      	            return super.getColumnClass(column);
      	        }
      
      	        switch(type) {
      	        case Types.CHAR:
      	        case Types.VARCHAR:
      	        case Types.LONGVARCHAR:
      	            return String.class;
      
      	        case Types.BIT:
      	            return Boolean.class;
      
      	        case Types.TINYINT:
      	        case Types.SMALLINT:
      	        case Types.INTEGER:
      	            return Integer.class;
      
      	        case Types.BIGINT:
      	            return Long.class;
      
      	        case Types.FLOAT:
      	        case Types.DOUBLE:
      	            return Double.class;
      
      	        case Types.DATE:
      	            return java.sql.Date.class;
      
      	        default:
      	            return Object.class;
      	        }
      	    }
		  //設置單元格不可編輯,為缺省實現
		  public  boolean isCellEditable(int row,int column)
		  {
			return false;
		  }

			
		};//end of new AbstractTableModel()  
	
	private void dataQuery()
	{
 	    String userName = tfUserName.getText ();
		try{
		    
			ResultSetMetaData rsmd = conUserQuery.rs.getMetaData();
	        conUserQuery.stm = conUserQuery.con.createStatement();
 
	 	   if(userName.compareTo ("") ==0)
			  conUserQuery.rs = conUserQuery.stm.executeQuery("select * from bookuser");
		   else
			conUserQuery.rs = conUserQuery.stm.executeQuery("select * from bookuser where username ='"+userName+"'");
		  
		   //初始化向量對象
		   vect.removeAllElements();
		  //更新表格內容
		  tm.fireTableStructureChanged();
         
		  while(conUserQuery.rs.next())
		  {
			Vector rec_vector = new Vector();
			for(int i =1; i<=rsmd.getColumnCount(); i++)
			{
				//從結果集中取數據放入向量rec_vector中
				rec_vector.addElement(conUserQuery.rs.getObject(i));
			}
			
			//向量rec_vector加入向量vect中
			vect.addElement(rec_vector);
			userY =1;
		 }
		
		//更新表格,顯示向量vect的內容		
		tm.fireTableStructureChanged();
		//查詢完成后,關閉該SQL
		conUserQuery.stm.close();
		//重新執行SQL
		openDB();

   	} catch(Exception e)
		{
		   System.out.print("Error in Query Data." + e);
   		   System.exit(1); 
   	   }
  }// end of private void dataQuery()


   //打開表
   private void openDB() 
   {
	   try
	   {
		   conUserQuery.stm = conUserQuery.con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
		   conUserQuery.rs = conUserQuery.stm.executeQuery("select * from bookuser");
		   conUserQuery.rs.first();
 
	   }
	   catch(Exception e)
	   {
		   System.out.print("Error in open table." + e);
		   System.exit(1); 
   	
	   }
   } // end of private void openDB() 
	
	public void actionPerformed(ActionEvent e)
	{
		if(e.getActionCommand () == "退出")
			dispose ();
		if(e.getActionCommand () == "查詢")
		{
			dataQuery();
			if(userY ==0)
			   tfResult.setText("輸入的讀者姓名不存在");
			userY=0;
		}
		if(e.getActionCommand ()=="清空")
		{
			tfUserName.setText ("");
			tfResult.setText ("");
		}
	   
	}
}