/*
 * @author  Rangan
 * @version 1.3
 *
 * Development Environment        :  JDeveloper 3.0
 * Name of the Application        :  LOBSample.java
 * Creation/Modification History  :
 *
 *    Rangan       10-JAN-1998      Created
 *    Feroz        12-DEC-1999      Modified
 *    Sujatha      15-May-2002      Certified on Oracle9i JDeveloper 
 *    Stephen      13-Feb-2003      Certified on Linux
 *                                  Added checkTables() Method
 */
package oracle.otnsamples.jdbc.lobtype;

// Package for JDBC classes
import java.sql.Connection;
import java.sql.Clob;
import java.sql.ResultSet;
import java.sql.Blob;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.PreparedStatement;
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;
import java.io.OutputStream;
import java.io.File;
import java.io.Reader;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.Writer;
import java.io.FileReader;
import java.io.FileInputStream;
/**
 * This sample illustrates access and manipulation of CLOB and BLOB columns
 * using JDBC2.0. Oracle's JDBC2.0 driver, provides API to perform selects,
 * updates, inserts and deletes from LOB columns. Here the methods specified
 * by the JDBC 2.0 API specifications are used for database operations.
 *
 * The sample illustrates the above operations on CLOB and BLOB columns in the
 * OTN_AIRPORT_LOB_DETAILS table. It inserts sample .gif and .txt file contents,
 * into the LOB columns for the chosen airport. If LOB data already exists for
 * the chosen airport, it retrieves and displays them. It also
 * illustrates manipulation of LOB columns, by allowing users to enter text to
 * be appended to the CLOB column.
 */
public class LOBSample {

  /** Database Connection Object */

  Connection connection;     
  
  /** The GUI handler for the sample */
  LOBFrame   gui;             


  /**
   *  Constructor. Initializes GUI
   */
  public LOBSample() {
    gui = new LOBFrame(this);
    gui.setVisible(true);       
  }

  /**
   *  Main entry point for the class. Instantiates the root frame,
   *  sets up the database connection and populates the JTable with 
   *  OTN_AIRPORTS rows
   */
  public static void main(String[] args) {
    LOBSample lobs = new LOBSample(); // Instantiate root frame
    lobs.dbConnection();              // Setup db connection
    if( lobs.connection != null ) {
      lobs.checkTables();   // Check if table exist, if not create it
      lobs.populateAirports();    // Populate the JTable with all airports rows
    }
  }

  /**
   *  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.).
   */
  public void dispatchEvent( String eventName ) {
    // Dispatch Event
    if( eventName.equals("Load Sample Files"))
      loadSamples(gui.getSelectedCode());
    else if( eventName.equals("Add Suggestions") ) {
      String suggestions = gui.getSuggestionText();
      if( !suggestions.equals("CANCEL")) {
        addSuggestions(gui.getSelectedCode(), suggestions);
        gui.sugArea.append(new String(suggestions));
       }
    } else if( eventName.equals("Airport Selected in Table") )
      airportSelected(gui.getSelectedCode());
    else 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());
    }
  }

  /**
   * Queries all rows from the OTN_AIRPORTS table and populates the JTable with
   * the returned rows
   */

  private void populateAirports() {
    Statement stmt = null;
    try {
      gui.appendStatus("\nPopulating Airports. Please wait...");

      // Statement object for issuing SQL statements
      stmt = connection.createStatement();

      // Execute the query that returns all airport rows
      ResultSet resultSet = stmt.executeQuery(
                         "SELECT airport_code, description, name"+
                         " FROM otn_airports");

      // Loop through the result set and populate JTable with all airports
      while( resultSet.next() ) {
        // Retrieve column values for this row
        String code = resultSet.getString(1);
        String desc = resultSet.getString(2);
        String city = resultSet.getString(3);

        gui.addToJTable(code, desc, city); // Insert into Jtable
      }
      gui.putStatus("Connected to database and retrieved all airport rows");
      gui.appendStatus("\nPlease Choose an airport ");
    } catch( SQLException ex ) {  // Trap SQL errors
      gui.putStatus("Error Querying OTN_AIRPORTS table: \n" + ex.toString());
    } finally {
      try {
        stmt.close(); // Close statement which also closes open result sets
      } catch(SQLException ex) {
        
      }
    }
  }

  /**
   * Creates a new row for the selected airport in OTN_AIRPORT_LOB_DETAILS.
   * It then loads the sample files into the LOB columns, using JDBC2.0.
   */
  private void loadSamples( String airportCode ) {
    PreparedStatement pstmt = null;
    Statement stmt = null;
    try {
      gui.putStatus("Creating row for airport in OTN_AIRPORT_LOB_DETAILS.. ");

      // Insert a row into OTN_AIRPORT_LOB_DETAILS with 
      // LOB column values are initialized to empty
      pstmt = connection.prepareStatement(
        "INSERT INTO OTN_AIRPORT_LOB_DETAILS( airport_code, airport_map,"+
        "airport_sug_book) VALUES(? , EMPTY_BLOB() , EMPTY_CLOB())");
      
      pstmt.setString(1, airportCode); // Bind AIRPORT code
      pstmt.execute();                 // Execute SQL statement

      gui.appendStatus("Created.\n Loading <map.gif> into Blob column for airport...");

      // Retrieve the row just inserted, and lock it for insertion of the
      // LOB columns.
      stmt = connection.createStatement();
      ResultSet lobDetails = stmt.executeQuery(
          "SELECT airport_map, airport_sug_book FROM OTN_AIRPORT_LOB_DETAILS "+
          "WHERE airport_code = '" + airportCode + "' FOR UPDATE");

      // Load the properties file to get the sample files information
      Properties prop = this.loadParams("Misc");
      String mapFileName     = (String)prop.get("map");
      String sugBookFileName = (String)prop.get("suggestions");

      // Retrieve Blob and Clob streams for AIRPORT_MAP and AIRPORT_SUG_BOOK
      // columns, and load the sample files
      if( lobDetails.next() ) {
        // Get the Blob locator and open output stream for the Blob
        Blob mapBlob = lobDetails.getBlob(1);
        OutputStream blobOutputStream = ((oracle.sql.BLOB)mapBlob).getBinaryOutputStream();


        // Open the sample file as a stream for insertion into the Blob column
        File mapFile = new File(mapFileName);
        InputStream sampleFileStream = new FileInputStream(mapFile);

        // Buffer to hold chunks of data to being written to the Blob.
        byte[] buffer = new byte[10* 1024];

        // Read a chunk of data from the sample file input stream, and write the
        // chunk to the Blob column output stream. Repeat till file has been
        // fully read.
        int nread = 0;   // Number of bytes read
        while( (nread= sampleFileStream.read(buffer)) != -1 ) // Read from file
          blobOutputStream.write(buffer, 0, nread);         // Write to Blob

        // Close both streams
        sampleFileStream.close();
        blobOutputStream.close();

        // Load the suggestion book sample file into the Clob column
        gui.appendStatus("Done\nLoading <suggestionbook.txt> into Clob column ..");


        // Get the Clob locator and open an output stream for the Clob
        Clob sugBookClob = lobDetails.getClob(2);
        Writer clobWriter = ((oracle.sql.CLOB)sugBookClob).getCharacterOutputStream();

        // Open the sample file as a stream for insertion into the Clob column
        File sugbookFile = new File(sugBookFileName);
        FileReader sugFileReader = new FileReader(sugbookFile);

        // Buffer to hold chunks of data to being written to the Clob.
        char[] cbuffer = new char[10* 1024];

        // Read a chunk of data from the sample file input stream, and write the
        // chunk into the Clob column output stream. Repeat till file has been
        // fully read.
        nread = 0;
        while( (nread= sugFileReader.read(cbuffer)) != -1 ) // Read from File
          clobWriter.write( cbuffer, 0, nread);          // Write to Clob

        // Close both streams
        sugFileReader.close();
        clobWriter.close();

        gui.putStatus("Done Loading sample files");

        gui.appendStatus("\nRetrieving and displaying sample files..");
        // Retrieve and display the LOB data just inserted
        drawBlob(mapBlob, airportCode);
        writeClob(sugBookClob, airportCode);
        gui.putStatus("Done loading and displaying LOB data");
      }
    } catch( Exception ex ) { // Trap SQL errors
      gui.putStatus("Error loading sample files for the selected airport");
      gui.appendStatus("\n" + ex.toString());
    } finally {
      try {
        pstmt.close(); // Close PreparedStatement
        stmt.close(); // Close statement which also closes open result sets
      } catch(SQLException ex) {
        
      }
    }
  }

  /**
   * Accepts suggestions from an JOptionPane and appends the entered
   * suggestions to the Clob column
   */
  private void addSuggestions(String airportCode, String suggestions) {
    Statement stmt = null;
    try {
      gui.putStatus(" Appending entered suggestions to Clob column. Please wait...");
      stmt = connection.createStatement(); // Prepare a statement

      // Retrieve the Clob locator and also lock the row, for the selected
      // Airport
      ResultSet lobDetails = stmt.executeQuery(
                          "SELECT airport_sug_book "+
                          "FROM OTN_AIRPORT_LOB_DETAILS "+
                          "WHERE airport_code ='" + airportCode + "' FOR UPDATE");

      // Obtain the Clob locator and append suggestions
      if( lobDetails.next() ) {
        // Get the Clob locator
        Clob clob = lobDetails.getClob(1);

        // Append the entered suggestions to the end of the CLOB data.
        // The first parameter to putString, is the offset from which to start
        // writing, and the second parameter is the data to be written.
        // clob.length(), returns the length of the data in the CLOB column
        ((oracle.sql.CLOB)clob).putString(((oracle.sql.CLOB)clob).length() + 1,
                                             suggestions);
        gui.appendStatus(" Done");
      }
    } catch( SQLException ex ) {
        gui.putStatus("Error appending suggestions to the Clob column");
      gui.appendStatus("\n" + ex.toString());
    } finally {
      try {
        stmt.close(); // Close statement which also closes open result sets
      } catch(SQLException ex) {
        
      }
    }
  }

  /**
   * This method is called when a row is selected from the airport JTable.
   * It checks if there exists data in OTN_AIRPORT_LOB_DETAILS for the selected
   * airport. If there exists data, it calls drawBlob and writeClob to
   * display the data
   */
  private void airportSelected(String airportCode) {
    Statement stmt = null;
    try {
      gui.putStatus("Retrieving LOB details for selected airport..");
      // Create a SQL statement
      stmt = connection.createStatement();

      // Query OTN_AIRPORT_LOB_DETAILS for the selected AIRPORT
      ResultSet lobDetails = stmt.executeQuery(
               "SELECT airport_map, airport_sug_book FROM OTN_AIRPORT_LOB_DETAILS "+
               "WHERE airport_code = '" + airportCode + "'");

      // Check if LOB columns exist
      if( lobDetails.next() ) {
        // LOB details exist

        // Display airport map and suggestion book (LOB details)
        drawBlob(lobDetails.getBlob(1), airportCode);
        writeClob(lobDetails.getClob(2), airportCode);
        gui.putStatus("Done retrieving and displaying LOB details");
      } else {
        // No LOB details
        gui.loadButton.setEnabled(true);
        gui.putStatus(" No airport map and suggestion book exist for selected airport");
        gui.appendStatus("\n Press <Load Sample Files> to load LOB details");
      }
    } catch( Exception ex ) { // Trap SQL errors
      gui.putStatus("Error retrieving LOB Details for the selected airport");
      gui.appendStatus(ex.toString());
    } finally {
      try {
        stmt.close(); // Close statement which also closes open result sets
      } catch(SQLException ex) {
        
      }
    }
  }

  /**
   * Retrieve the Blob data from input Blob column into a local file,
   * and draws the image
   */
  private void drawBlob(Blob blob, String airPCode) {
    try {
      // Open a stream to read the Blob data
      InputStream blobStream = blob.getBinaryStream();


      // get user home folder name 
      String userHome = System.getProperty("user.home");

      // append the file name with user home directory, file separator and
      // file extension GIF
      String fileName = userHome+File.separator+airPCode+".gif";   

      // Open a file stream to save the Blob data
      FileOutputStream fileOutStream = new FileOutputStream(fileName);

      // Read from the Blob data input stream, and write to the file output
      // stream
      byte[] buffer = new byte[10]; // buffer holding bytes to be transferred
      int nbytes = 0;  // Number of bytes read
      while( (nbytes = blobStream.read(buffer)) != -1 ) // Read from Blob stream
        fileOutStream.write(buffer, 0, nbytes);     // Write to file stream

      // Flush and close the streams
      fileOutStream.flush();
      fileOutStream.close();
      blobStream.close();

      gui.drawMap(fileName); // Draw retrieved image to GUI
    } catch( Exception ex ) { // Trap SQL and IO errors
      gui.putStatus(" Error in retrieving and drawing map for selected airport");
      gui.appendStatus("\n" + ex.toString());
    }
  }

  /**
   * Retrieve the character data from the input Clob, save in a
   * StringBuffer and display the StringBuffer contents in GUI
   */
  void writeClob(Clob clob, String airPCode) {
    try {
      // Open a stream to read Clob data
      Reader clobStream = clob.getCharacterStream();

      // Holds the Clob data when the Clob stream is being read
      StringBuffer suggestions = new StringBuffer();

      // Read from the Clob stream and write to the stringbuffer
      int    nchars = 0; // Number of characters read
      char[] buffer = new char[10];  //  Buffer holding characters being transferred

      while( (nchars = clobStream.read(buffer)) != -1 ) // Read from Clob
        suggestions.append(buffer, 0, nchars);        // Write to StringBuffer

      clobStream.close();  // Close the Clob input stream
      gui.sugArea.append(new String(suggestions)); // Display in GUI
    } catch( Exception ex ) { // Trap SQL and IO errors
      gui.putStatus("Error in getting and drawing Clob for the airport, "+ airPCode +":");
      gui.appendStatus(ex.toString());
    }
  }

  /**
   *  Close the database Connection and exit the application
   */
  public void exitApplication() {
    try {
      gui.putStatus("Closing the connection....please wait.....");
      if( connection != null )
        connection.close(); //Close the connection object.
    } catch( SQLException ex ) { //Trap SQL Errors
      gui.putStatus(ex.toString());
    }
    System.exit(0); //Exit the application
  }
  /**
   * Checks if the tables ('OTN_AIRPORTS' and 'OTN_AIRPORT_LOB_DETAILS') 
   * are present, else creates it. 
   * Look into PopulateTable.java for more details
   */
  private void checkTables()   {
    Statement stmt = null;
    ResultSet rset = null;
    PopulateTable popTable = null;
    try     {
      stmt = connection.createStatement();
      
      // check from User_tables data dictionary table if the table is existing.
      rset = stmt.executeQuery(" SELECT Table_Name FROM User_Tables "+
                               " WHERE Table_Name = 'OTN_AIRPORTS' ");

      // if the resultset of the above query does not have any record, it means
      // OTN_AIRPORTS table is not existing. So the table is created.
      if (!rset.next()) {
        // call the class to create the table
        if (popTable == null)
          popTable = new PopulateTable(connection,gui);
        popTable.createSchemaTable();
        gui.putStatus("OTN_AIRPORTS Table created.");                  
      } 
      // check from User_tables data dictionary table if the table is existing.
      rset = stmt.executeQuery(" SELECT Table_Name FROM User_Tables "+
                               " WHERE Table_Name = 'OTN_AIRPORT_LOB_DETAILS' ");

      // if the resultset of the above query does not have any record, it means
      // OTN_AIRPORT_LOB_DETAILS table is not existing. So the table is created.
      if (!rset.next()) {
        // call the class to create the table
        
        if (popTable == null)
          popTable = new PopulateTable(connection,gui);
        popTable.createLOBTable();
        gui.putStatus("OTN_AIRPORT_LOB_DETAILS Table created.");                  
      }
    } catch (SQLException sqlEx) {
       gui.putStatus("Could not create required tables : "+sqlEx.toString());
    } finally  {
        try { 
          if( rset != null ) rset.close( );
          if( stmt != null ) stmt.close( ); 
        } catch(SQLException ex) { }
    }
  }
}