/*
 *@author余芳，創建日期2008-4-24
*/
package text;

import java.awt.BorderLayout;
import java.awt.Container;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.JTextField;



public class QueryDB3 extends JFrame implements ActionListener {
	
	private Connection connection1;
	private Connection connection2;
	private Connection connection3;
	private Statement statement;
	private ResultSet resultSet;
	private JTable table;
	
	JTextField start; 
	JTextField destination; 
	
	JTextArea Result;
	JPanel jPanel1 = new JPanel(); 
	JPanel jPanel2 = new JPanel(); 
	JPanel jPanel3 = new JPanel(); 
	JButton Query1;
	JButton Query２;
	JButton Query3;


	
	public QueryDB3() {
	super("信息查詢");
	setDefaultCloseOperation(EXIT_ON_CLOSE);
	String url1="jdbc:odbc:RoadInfo";
	String url2="jdbc:odbc:Roadguide";
	String username="";
	String password="";
	try{
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		connection1=DriverManager.getConnection(url1, username, password);
		connection2=DriverManager.getConnection(url2, username, password);
		connection3=DriverManager.getConnection(url2, username, password);
	}
	catch(ClassNotFoundException e){
		System.err.println("裝載JDBC/ODBC驅動程序失敗。");
		e.printStackTrace();
		System.exit(1);
	}
	catch(SQLException e){
		System.err.println(" 無法連接數據庫");
		e.printStackTrace();
		System.exit(1);
}
	Container container=this.getContentPane();  //得到容器
	JPanel p1=new JPanel();  //初始化一個面板
	
	
	Query1=new JButton();   //初始化按鈕
	Query1.setText("　　　　　　    　            查 詢 數 據 庫 中 的 所 有 路 況 信 息　          　   　　　　　");  //設置按鈕文本	
	Query1.addActionListener(this);	
	
	
	
	p1.add(Query1);		
    container.add(p1,BorderLayout.NORTH);  //在容器上增加面板 
	
    JPanel p2=new JPanel(); 
    start=new JTextField(6);  //初始化出發地輸入域
	destination=new JTextField(6);
   
    Query２=new JButton();   //初始化按鈕
	Query２.setText("查詢路徑信息");  //設置按鈕文本	
	Query２.addActionListener(this);
	Query3=new JButton();   //初始化按鈕
	Query3.setText(" 計算最優路徑 ");  //設置按鈕文本	
	Query3.addActionListener(this);
	p2.add(new JLabel("出發地："));  //增加聊天題標簽
	p2.add(start);  //增加名字輸入域
	p2.add(new JLabel("目的地：")); 
	p2.add(destination);	
	p2.add(Query２);	
	p2.add(Query3);	
	
	
	container.add(p2,BorderLayout.SOUTH);
	
	
	
	
   	
 	setSize(520,300);//設置窗口尺寸
    setVisible(true);  //設置窗口可視
	}
	 public void actionPerformed(ActionEvent actionevent)
	 {
	     Object obj = actionevent.getSource();
	     if(obj==Query1){ 
	         String q1="SELECT * FROM RoadInfo";
	        
	        
	         try{
	 			
	 			statement=connection1.createStatement();
	 			resultSet=statement.executeQuery(q1);
	 			displayResultSet(resultSet);
	 		}
	 		catch(SQLException sqlex){
	 			sqlex.printStackTrace();
	 		}
	 		}else if(obj == Query２)
	     {   
	    	
	         
	         String q2="SELECT Start,Destination,Road,Status FROM Roadguide WHERE Start='"+start.getText()
	         + "' AND Destination='"+destination.getText()+"'";
	        
	        
	         try{
	 			
	 			statement=connection2.createStatement();
	 			resultSet=statement.executeQuery(q2);
	 			displayResultSet(resultSet);
	 			
	 		}
	 		catch(SQLException sqlex){
	 			sqlex.printStackTrace();
	 		}
	         
	     } else if(obj==Query3){
	    	 String q3="SELECT * FROM Roadguide WHERE Start='"+start.getText()
	         + "' AND Destination='"+destination.getText()+"'ORDER BY Distance";
	    	 try{
		 			
		 			statement=connection3.createStatement();
		 			resultSet=statement.executeQuery(q3);
		 			displayResultSet(resultSet);
		 			start.setText("");
		 			destination.setText("");
		 		}
		 		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.add(scroller,BorderLayout.CENTER);
			  
				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{
				connection1.close();
			}catch(SQLException e){
				System.err.println("無法斷開連接");
				e.printStackTrace();
			}
			}
		 	
				
	 
	 
	public static 	void main(String[] args) {
		 new QueryDB3();
		 
	}
	}