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

public class CommodityQueryFrame extends JDialog 
{
	private Connection queryconnection; 
   	private Statement querystatement; 
   	private ResultSet queryresultSet; 
   	private ResultSetMetaData rsMetaData;
  
    //GUI變量定義
	private JFrame parent; 
	private JPanel labelPanel, tablePanel;//定義兩塊面板對象
	//labelPanel用來放標簽，fieldsPanel用來放文本區
	private String labels = "請輸入商品標號：";
	private JTable commoditytable; 
	private JButton submitQuery;     
	private JButton cancelQuery;     
            JTextField Commodity_ID;
	
    public CommodityQueryFrame(JFrame parent, boolean modal) 
    {
        super(parent, modal);
        this.parent = parent;
        pack();
        //GridBagLayout layout = new GridBagLayout();
        //Container contents = getContentPane();
        //GridBagConstraints constraints = new GridBagConstraints();
        
        //super( "輸入SQL語句，按提交按鈕查看結果。" ); 
    	//url中指定ODBC中設置的DSN名稱
    	String url = "jdbc:odbc:VipQuery"; 
    	String username = ""; 
    	String password = ""; 
    	//加載驅動程序以連接數據庫
      	try 
      	{ 
        	Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ); 
        	queryconnection = 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 
      	} 
     
        submitQuery = new JButton( "查  詢" );
        submitQuery.setPreferredSize(new Dimension(120, 25));
    	submitQuery.addActionListener( 
         new ActionListener() 
         { 
            public void actionPerformed( ActionEvent e ) 
            { 
               	getTable();
            } 
         }   
        ); 
        
        cancelQuery = new JButton( "返  回" );
        cancelQuery.setPreferredSize(new Dimension(120, 25));
    	cancelQuery.addActionListener( 
         new ActionListener() 
         { 
            public void actionPerformed( ActionEvent e ) 
            { 
               	setVisible(false);
               	dispose();
            } 
         }   
        ); 
       
        GridBagConstraints constraints = new GridBagConstraints();
        
        Commodity_ID = new JTextField();
        Commodity_ID.setPreferredSize(new Dimension(120, 25));
              
        
    	//VIP_NAME = new JTextField();
    	//VIP_NAME.setPreferredSize(new Dimension(120, 25));
    	
    	labelPanel = new JPanel();
		labelPanel.setLayout( new GridBagLayout());
		
		constraints.weightx = 100;
        constraints.weighty = 100;
        constraints.gridx = 0;
        constraints.gridy = 0;
        constraints.gridwidth = 1;
        constraints.gridheight = 1;
        labelPanel.add(new JLabel( labels), constraints);
        
        
        constraints.gridx =1;
        constraints.gridy = 0;
        constraints.gridwidth = 1;
        constraints.gridheight = 1;
        labelPanel.add(Commodity_ID, constraints);
        
      
        constraints.gridx =2;
        constraints.gridy = 0;
        constraints.gridwidth = 1;
        constraints.gridheight = 1;
        labelPanel.add(submitQuery, constraints);
        
    
        constraints.gridx =3;
        constraints.gridy = 0;
        constraints.gridwidth = 1;
        constraints.gridheight = 1;
        labelPanel.add(cancelQuery, constraints);
      
		commoditytable = new JTable();
		
        JScrollPane commodityscroller = new JScrollPane( commoditytable ); 
        commodityscroller.setPreferredSize(new Dimension(100, 50));
		
		tablePanel = new JPanel();
		tablePanel.setLayout( new BorderLayout() );
		tablePanel.setPreferredSize(new Dimension(100, 20));
		tablePanel.setBorder(BorderFactory.createEtchedBorder());
 		tablePanel.add( commodityscroller ,BorderLayout.CENTER);
 		
 		Container c = getContentPane(); 
      	setTitle("商品信息查詢窗口");
      	c.setLayout( new BorderLayout()); 
      	//將"topPanel"編輯框布置到 "NORTH"
      	c.add( labelPanel ,BorderLayout.NORTH);
      	c.add( tablePanel ,BorderLayout.CENTER);
      	//c.add( commoditytable ,BorderLayout.SOUTH);
      	getTable(); 
      	setSize( 500, 330 );
      	setResizable(false);
      	setLocation(200,100);
      	//show(); 
    }
    
    private void getTable() 
   { 
      try 
      { 
      	 //執行SQL語句
      	 String commodityquery;
 
 
    	 if (Commodity_ID.getText().equals( "" ) )
      	 {
         	commodityquery = "SELECT * FROM COMMODITY_ID";
         }
         else 
         {   
         	 commodityquery = "SELECT * FROM COMMODITY_ID WHERE 商品編號="+
         	 				  Commodity_ID.getText()+"";
         }
         
         querystatement = queryconnection.createStatement(); 
          
         queryresultSet = querystatement.executeQuery( commodityquery );       
         //在表格中顯示查詢結果
         displayResultSet( queryresultSet);
      } 
      catch ( SQLException sqlex ) { 
         sqlex.printStackTrace(); 
      } 
   }  	
   
   private void displayResultSet( ResultSet commodityrs ) 
      throws SQLException 
   { 
      //定位到達第一條記錄
     
      boolean commoditymoreRecords = commodityrs.next();
      //如果沒有記錄，則提示一條消息
      
      if ( ! commoditymoreRecords ) 
      { 
         JOptionPane.showMessageDialog( this,"無此商品記錄" ); 
         //setTitle( "無記錄顯示" ); 
      	 return ;
      } 
     
      Vector commoditycolumnHeads = new Vector(); 
      Vector commodityrows = new Vector(); 
      try
      {
      	 
         //獲取字段的名稱
         ResultSetMetaData rsmd = commodityrs.getMetaData(); 
         for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
         { 
            commoditycolumnHeads.addElement( rsmd.getColumnName( i ) ); 
         }
          //獲取記錄集
         do 
         { 
            commodityrows.addElement( getNextRow( commodityrs, rsmd ) ); 
         } while ( commodityrs.next() ); 
         
         ResultSetMetaData commodityrsmd = commodityrs.getMetaData(); 
        
         
         //在表格中顯示查詢結果
         commoditytable = new JTable( commodityrows, commoditycolumnHeads ); 
         
         JScrollPane scroller = new JScrollPane( commoditytable ); 
         scroller.setSize(800, 20);
          
         Container c = getContentPane(); 
         c.remove(1); 
     	 tablePanel = new JPanel();
     	 tablePanel.setLayout( new BorderLayout() );
         tablePanel.add( scroller, BorderLayout.CENTER ); 
       
         c.add(tablePanel,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; 
   } 
}