/*
 * @author  Feroz
 * @version 1.0
 *
 * Development Environment        :  JDeveloper 2.0
 * Name of the Application        :  DynamicSqlSample.java
 * Creation/Modification History  :
 *
 *    Feroz       08-Mar-1999      Created
 *    Sujatha     22-May-2002      Certified on Oracle9i JDeveloper   
 *    Stephen     12-Feb-2003      Certified on Linux
 *
 */
 
package oracle.otnsamples.jdbc.dynamicsql;

// Package for JDBC classes
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.Connection;
import oracle.jdbc.pool.OracleDataSource;

// Java Utility Classes
import java.util.Properties;
import java.util.ResourceBundle;
import java.util.Enumeration;

// Package for using Streams
import java.io.IOException;

/**
 * This sample illustrates how dynamic sql operations can be performed using
 * JDBC. The user selects a table from his schema, then the columns and
 * provides the where clause. The dynamic SQL query is then formed and
 * executed.
 * The GUI for this sample is handled in DynamicSqlFrame.java
 */

public class DynamicSqlSample {

  /** Database Connection Object */
  Connection  connection = null;

  /** GUI handler for this sample */
  DynamicSqlFrame gui;

  /** 
   * The constructor for DynamicSql class which instantiates the GUI class 
   */

  public DynamicSqlSample() {
    gui = new DynamicSqlFrame(this);
    gui.setVisible(true);                      
  }

  /**
   *  Main entry point for the class. Instantiates the DMLSample class
   *  and sets up the database connection.
   */
  public static void main(String[] args) {
    DynamicSqlSample root = new DynamicSqlSample();
    root.dbConnection();   // Make JDBC Connection
    if( root.connection != null )
      root.displayTableList(); // Display the name of all the tables
  }

  /**
   *  Dispatches the GUI events to the appropriate method, which performs
   * the required JDBC operations. This method is invoked when event occurs
   * in the GUI (like table Selection, Button clicks etc.). This method
   * is invoked from the setupListeners section of DynamicSqlFrame.java
   */
  public void dispatchEvent( String eventName ) {
    //  For Dispatching the select event
    if( eventName.equals( "DISPLAY_COLUMNS") )
      displayColumns();

    // For Dispatching the select event
    if( eventName.equals("SELECT") )
      selectRecords();

    // For Dispatching the exit event
    if( eventName.equals("EXIT") )
      exitApplication();
  }

  /**
   * This method reads a properties file which is passed as
   * the parameter to it and load it into a java Properties
   * object and returns it.
   */
  private static Properties loadParams( String file ) throws IOException {
    // Loads a ResourceBundle and creates Properties from it
    Properties prop = new Properties();
    ResourceBundle bundle = ResourceBundle.getBundle( file );
    Enumeration enum = bundle.getKeys();
    String key = null;
    while( enum.hasMoreElements() ) {
      key = (String)enum.nextElement();
      prop.put( key, bundle.getObject( key ) );
    }
    return prop;
  }

  /**
   * Creates a database connection object using DataSource object. Please
   * substitute the database connection parameters with appropriate values in
   * Connection.properties file
   */
  private void dbConnection() {
    try {
      gui.putStatus("Trying to connect to the Database");


      // Load the properties file to get the connection information
      Properties prop = this.loadParams("Connection");

      // Create a OracleDataSource instance
      OracleDataSource ods = new OracleDataSource();

      // Sets the driver type
      ods.setDriverType("thin");

      // Sets the database server name
      ods.setServerName((String)prop.get("HostName"));

      // Sets the database name
      ods.setDatabaseName((String)prop.get("SID"));

      // Sets the port number
      ods.setPortNumber(new Integer((String)prop.get("Port")).intValue());

      // Sets the user name
      ods.setUser((String)prop.get("UserName"));

      // Sets the password
      ods.setPassword((String)prop.get("Password"));

      // Create a connection  object
      connection = ods.getConnection();

      // Sets the auto-commit property for the connection to be false.
      connection.setAutoCommit(false);

      gui.putStatus(" Connected to " + prop.get("SID") +
                    " Database as " + prop.get("UserName"));


    } catch(SQLException ex) { // Trap SQL errors
        gui.putStatus(
                     "Error in Connecting to the Database "+'\n'+ex.toString());
    } catch(IOException ex) { // Trap I/O errors
        gui.putStatus(
                     "Error in reading the properties file "+'\n'+ex.toString());
    }
  }

  /**
   * This method retrieves all the tables present in the user's schema
   */
  private void displayTableList() {
    ResultSet resultSet = null;
    Statement stmt      = null;
    try {
      // Create a SQL statement and execute the query to fetch all tables in the
      // database
      stmt = connection.createStatement();
      resultSet = stmt.executeQuery(" SELECT table_name FROM user_tables");

      gui.tableNameModel.clearTable(); // Clear JTable in GUI

      // Populate the JTable with all the table names
      while( resultSet.next() ) { // Point result set to next row

        // Retrieve table name
        String tableName = resultSet.getString(1);

        gui.addToJTable(tableName); // Update GUI
      }
    } catch( SQLException ex ) {   // Trap SQL errors
      gui.statusField.setText(
          "Error in querying the database " + '\n' + ex.toString());
      gui.statusField.setScrollOffset(0);
    } finally {
      try {
        stmt.close(); // Close statement which also closes open result sets
      } catch(SQLException ex) {
        
      }
    }
  }

  /**
   * This method creates the SQL query to be executed based on the selections
   * by the user, and displays the results in the Results JTable
   */
  private void selectRecords() {


    // Get the concatenated string of column names for the columns
    // to be retrieved
    String columnNames = gui.makeStringOfAllColumns();

    // Get the Where clause for the query
    String whereClause = gui.restrictionTextfield.getText();

    // Form the dynamic Query
    StringBuffer query = new StringBuffer( "SELECT " ); // Start the SQL statement
    query.append( columnNames + " FROM ");            // Add columns to be retrieved
    query.append( gui.tableName );                    // Add table to retrieve

    // Add WHERE clause if any
    if( !whereClause.equals("") )
      query.append(" WHERE " + whereClause);

    Statement stmt = null;
    try {
      gui.putStatus("Selecting Records ...");

      // Create a SQL statement context to execute the Query
      stmt = connection.createStatement();

      // Execute the formed query and obtain the ResultSet
      ResultSet resultSet = stmt.executeQuery( new String(query) );

      String[] columnValues = new String[gui.selectedColumns.length];

      // Populate form the resultset
      while( resultSet.next() ) { // Point result set to next row
        // Retrieve column values for this row
        for( int i = 1; i < gui.selectedColumns.length + 1; i++ ) {
          // Check for null values
          columnValues[i -1] =
           ( resultSet.getString(i) == null )? "" :(String )resultSet.getString(i);
        }
        gui.addToResultsTable(columnValues); // Update GUI
      }
      gui.putStatus("Selection Complete");
      gui.restrictionTextfield.setText("");

    } catch (SQLException ex) {   // Trap SQL errors
      gui.statusField.setText(
          "Error in querying the database " + '\n' + ex.toString());
      gui.statusField.setScrollOffset(0);
    } finally {
      try {
        stmt.close(); // Close statement which also closes open result sets
      } catch(SQLException ex) {
        
      }
    }
  }

  /**
   * This method retrieves all the column names for the selected table
   * which can then be selected from the displayed JTable in GUI
   */
  private void displayColumns() {
    Statement stmt = null;
    try {
      // Create and execute a SQL statement to retrieve all the columns in the
      // selected Table
      stmt = connection.createStatement();
      ResultSet resultSet = stmt.executeQuery(
            "SELECT column_name FROM user_col_comments "+
            "WHERE table_name = '" + gui.tableName + "'");

      // Populate the Columns JTable with the rows contained in the result-set.
      while( resultSet.next() ) { // Point result set to next row

        // Retrieve Column
        String columnName = resultSet.getString(1);


        gui.addToJTable1( columnName ); // Add to Columns JTable
      }
    } catch( SQLException ex ) {   // Trap SQL errors
      gui.statusField.setText(
          "Error in querying the database " + '\n' + ex.toString());
      gui.statusField.setScrollOffset(0);
    } finally {
      try {
        stmt.close(); // Close statement which also closes open result sets
      } catch(SQLException ex) {
        
      }
    }
  }

  /**
   *  Closes the connection and exits from the program when exit button is
   *  pressed
   */
  private void exitApplication(){
    if( connection != null ) {
      try {
        connection.close();              // Close the connection while exiting
      } catch( Exception ex ) {
        gui.putStatus(" Error in closing the connection: ");
        gui.appendStatus(" " + ex.toString());
      }
    }
    System.exit(0);
  }
}