/*********** GUI THAT ENABLES QUERYING OF TABLES WITHIN CCDB DATABASE ON ACMEX ************/

import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
import java.io.*;
import javax.swing.table.*;

public class Query_Frame extends JFrame implements MouseListener{
    
    OracleConnection conn;
    JTextField userQuery;
    JTable table;
    JLabel msgline;
    String user, pass;


    /***************** CONSTRUCTOR ********************/
 
    public Query_Frame(String username,String pass) {
	
	super("Query Oracle on Acmex");  // Set window title
	
	// set the username and password to be used for connection
	this.user = username;
	this.pass = pass;
	
	try{
	    // Arrange to quit the program when the user closes the window
	    addWindowListener(new WindowAdapter() {
		    public void windowClosing(WindowEvent e) { System.exit(0); }
		});
	    
	    // Create the Swing components we'll be using
	    userQuery = new JTextField();     // Lets the user enter a query
	    table = new JTable();             // Displays the table
	    msgline = new JLabel();           // Displays message
	    
	    JLabel label = new JLabel();     
	    label.setText("Enter Query(without semi-colon) and click Go:");

            //Button is created, when clicked will display the query results
	    JButton button = new JButton();
	    button.setText("Go");
	    button.addMouseListener(this);

	    //create an innerPanel to hold the label and textfield where the user would
	    //enter the query
	    Panel innerPanel = new Panel();

	    //set the layout of the innerpanel	    
	    innerPanel.setLayout(new GridLayout(1,3));
	    innerPanel.add(label);
	    innerPanel.add(userQuery);	
	    innerPanel.add(button);

	    // Place the components within this window
	    Container contentPane = getContentPane();
	    contentPane.add(innerPanel, BorderLayout.NORTH);
	    contentPane.add(new JScrollPane(table), BorderLayout.CENTER);
	    contentPane.add(msgline, BorderLayout.SOUTH);	    
	}
	catch (Exception e){
		System.out.println(e);
	}
	
    }//end of constructor
    
    //Button is clicked.
    public void mouseClicked(MouseEvent e){

	// Get the user's query and pass to displayQueryResults()
	displayQueryResults(userQuery.getText());
    }
    
    //List all the methods in the mouseListener interface
    public void mousePressed(MouseEvent e) {}
    public void mouseExited(MouseEvent e) {}
    public void mouseEntered(MouseEvent e) {}
    public void mouseReleased(MouseEvent e) {}
    

    //connect to the database. Incase of any error it will throw an exception     
    void dbConnect() throws SQLException{
	
	// Connect using the jdbc driver.
	DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver ());
	
	//get the connection to the ccdb database.
	this.conn = (OracleConnection)
	    DriverManager.getConnection("jdbc:oracle:thin:@cactus.prism.gatech.edu:1525:ccdb",user,pass);
	
    }//end of dbConnect()
    
    
    void dbClose() throws SQLException{
	
	// Try to close the connection; could throw an SQL exception
	conn.close();
	this.conn = null;
	
    }//end of dbClose()
    
    //Execute the Query using the connection..
    ResultSet executeQuery(String query) throws SQLException{
	
	ResultSet rs = null;
	
	// Create a Statement object that will be used to excecute the query.
	// The arguments specify that the returned ResultSet will be
	// scrollable, read-only, and insensitive to changes in the db.
	Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE,
					       ResultSet.CONCUR_READ_ONLY);
	rs = stmt.executeQuery (query);
	return rs;
	
    }//end of executeQuery
        
    //Close the resultset and the corresponding statement
    public void closeResultSet(ResultSet rs) throws SQLException{ 
	
	rs.getStatement().close(); 
	rs = null;
	
    }//end of closeResultSet
    
    //execute the query and display the results of execution in the JTable
    void displayQueryResults(String query){
	
	try {	    
	    // It may take a while to get the results, so give the user some
	    // immediate feedback that their query was accepted.
	    msgline.setText("Contacting database...");	
	 
	    //connect to the database
	    dbConnect();
	    
	    //execute the query
	    ResultSet rs = executeQuery(query);	
	    
	    // get TableModel definition to which the resultset has to be displayed 
	    DefaultTableModel aModel = new DefaultTableModel();
	    	    
	    //get the metadata and column count from the resultset
	    ResultSetMetaData rsmd = rs.getMetaData();
	    int colCount = rsmd.getColumnCount();
	    
	    //Allocate an array to hold the column names
	    String[] tableColumnNames = new String[colCount];
	    
	    //get the column names from the resultset
	    for (int i=0; i< colCount; i++)
		tableColumnNames[i] = rsmd.getColumnLabel(i+1);
	    
	    //set the column headers
	    aModel.setColumnIdentifiers(tableColumnNames);		
	    
	    // Loop through the ResultSet and transfer in the Model
	    while(rs.next()){
		
		//Allocate a new row
		Object[] objects = new Object[colCount];
		
		//Add the column values to the row
		for(int i=0;i<colCount;i++){
		    objects[i]=rs.getObject(i+1);
		}
		
		//add the row to the table model
		aModel.addRow(objects);
	    }

	    //place the table model into the table for displaying
	    table.setModel(aModel);
	    
	    // We're done, so clear the feedback message
	    msgline.setText("Finished");
	    
	    //close the resultset
	    closeResultSet(rs);
	    
	    //close the database connection
	    dbClose();
	}
	catch (SQLException ex) {
	    
	    // If something goes wrong, clear the message line
	    msgline.setText("Error Happened");
	    
	    // Then display the error in a dialog box
	    JOptionPane.showMessageDialog(Query_Frame.this,
					  new String[] {  // Display a 2-line message
					      ex.getClass().getName() + ": ",
					      ex.getMessage()
					  });
	}//catch
	catch (Exception e){
		System.err.println(e);
	}//end of catch
	
    }//end of displayQueryResults
    
    /*
     * To Use the java code, make sure the driver is in the class path. Also, 
     * supply the username and password for connection
     *                   
     *              Usage: java Query_Frame <user> <pass>
     *
     *              user = gt#
     *              pass = gt#+2-digit-birth-day+2-digit-birthmonth
     */
    public static void main(String[] args) {
        
	try {
	    //Check the passed in arguments
	    if (args.length != 2)
                throw new IllegalArgumentException("Wrong number of arguments");
	    
	    //create the object by passing in the constructor the username and password
	    Query_Frame frame = new Query_Frame(args[0],args[1]);
	   
	    //set the frame size
	    frame.setSize(1000,600);
	    
	    //make the frame visible
	    frame.setVisible(true);
	    
        }//end of try
        catch (IllegalArgumentException iAE){  
	    System.err.println(iAE);        
	    System.err.println("Usage: java Query_Frame <user> <pass>");
	}//end of catch
	
    }//end of main
    
}//end of class Query_Frame