/**
 * @author  Umesh
 * @version 1.0
 *

 * Development Environment        :  Oracle9i JDeveloper
 * Name of the Application        :  ConnCacheBean.java
 * Creation/Modification History  :
 *
 *    Umesh      25-Nov-2001      Created
 *    Venky      04-Feb-2003      Certified on OC4J903 on Linux

 *
 * Overview of Application        : This Bean Class is used by all the JSPs
 * to perform database interaction. This class uses JDBC to perform any DML/DDL
 * operations. The key concept illustarted here is Connection Caching.
 *
 * As JSPs execute in middle tier, getting an individual database connection
 * everytime for every user is an expensive operation. This is true especially

 * when the number of users involved are large in number.
 *
 * With the help of Connection Caching, the overhead of instantiating a new physical
 * database connection can be easily overcome.
 *
 * This bean is implemented as a Singleton Class meaning that there can be only
 * one instance of this bean per JVM. In the constructor of the bean Connection
 * Cache is initialized.

 *
**/
package oracle.otnsamples.oracle9ijdbc.conncachesample;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.PreparedStatement;

import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.pool.OracleConnectionPoolDataSource;
import oracle.jdbc.pool.OracleConnectionCacheImpl;

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


// Java IO Classes
import java.io.IOException;

public class ConnCacheBean  {

  // Connection Cache Variable
  private OracleConnectionCacheImpl ocacheimpl = null;

  // Data Source Variable
  private OracleConnectionPoolDataSource cpds = null;


  // Variable pointing to this instance
  private static ConnCacheBean thisInstance = null;

 /**
  * Private Constructor : This approach makes it easy to implement this class as
  * Singleton Class.
  *
  * This method initializes Cache if not already initialized.
  **/
  private ConnCacheBean() throws Exception {
   if (ocacheimpl == null)
    initializeConnectionCache();

  }

 /**
  * This method returns a single instance of this bean.
  **/
  public static ConnCacheBean getInstance() throws Exception {
    if ( thisInstance == null ) {
       thisInstance = new ConnCacheBean();
    }
    return thisInstance;
  }

 /**

  * This Method initializes Connection Cache.
  **/
  private void initializeConnectionCache() throws Exception {
    if (ocacheimpl == null) {
      try {
        this.initializeConnectionCacheDataSrc();

        // Initialize the Connection Cache
        ocacheimpl = new OracleConnectionCacheImpl(cpds);

        // Set Max Limit for the Cache
        ocacheimpl.setMaxLimit(5);

        // Set Min Limit for the Cache

        ocacheimpl.setMinLimit(1);

        // Set Caching Scheme as DYNAMIC_SCHEME
        // Caching Schema means that once the connection active size becomes 5,
        // the next request for Connection will be served by creating a new pooled
        // connection instance and close the connection automatically when it is
        // no longer in use.
        ocacheimpl.setCacheScheme(OracleConnectionCacheImpl.DYNAMIC_SCHEME);

      } catch (java.sql.SQLException ex) { // Trap SQL Errors
         throw new Exception("SQL Error while Instantiating Connection Cache : \n" +
                                                   ex.toString());
      } catch (javax.naming.NamingException ex) { // Trap Naming Errors
         throw new Exception("Naming Exception : \n" + ex.toString());
      } catch (java.lang.Exception ex) { // Trap other errors

         throw new Exception("Exception : \n" + ex.toString());
      }
    }
  }

 /**
  * This method returns Product Information for all products in a ArrayList.
  **/
  public ArrayList getProductInformation() throws Exception {

    // Declare ArrayList to hold Product Information
    ArrayList prodList = new ArrayList();

    // Connection from the Connection Cache
    Connection conn = null;


    // Statement and ResultSet Object for fetching the Product Information
    Statement stmt = null;
    ResultSet rset = null;

    // If Cache is not initialized properly then throw an error
    if (ocacheimpl == null) {
      throw new Exception("Connection Cache Not Properly Initialized");
    }

    try {
      // Get Connection from the Cache
      conn = ocacheimpl.getConnection();

      stmt = conn.createStatement();

      // Execute Statement to get product Information from the Database
      rset = stmt.executeQuery(" SELECT P.PRODUCT_ID, P.PRODUCT_NAME, " +

        "P.PRODUCT_DESCRIPTION, P.LIST_PRICE, " + " C.CATEGORY_NAME FROM " +
        "PRODUCT_INFORMATION P, CATEGORIES_TAB C WHERE " +
        "P.CATEGORY_ID = C.CATEGORY_ID AND ROWNUM <= 10 AND P.PRODUCT_ID >= 1781 " +
        " ORDER BY C.CATEGORY_NAME ");

      // Loop through the Result Set
      while (rset.next()) {

        // Concatenate the information and add the String to the ArrayList
        String prodString = rset.getInt(1) + "****" + rset.getString(2) + "****" +
          rset.getString(3) + "****" + new Float(rset.getFloat(4)).toString() +
           "****" + rset.getString(5);

        prodList.add(prodString);
      }
   } catch (SQLException ex) { // Trap SQL Errors
     throw new Exception("SQL Error : \n" + ex.toString());
   } finally {

     try {
      // Close Result Set and Statement
      rset.close();
      stmt.close();
      // Closing connection returns the Connection to the Cache.
      conn.close();
     } catch (Exception ex) { // Trap Errors
       throw new Exception("SQL Error while closing objects = " + ex.toString());
     }
   }
    return prodList; // Return Product List
   }


 /**
  * This method returns the Order Information for a particular product.
  * Order Information is returned in a ArrayList.
  **/
  public ArrayList getOrderInformation(String productId) throws Exception {

    // ArrayList holding order Information
    ArrayList orderList = new ArrayList();
    String orderEntry = "";

    // Connection from the Connection Cache
    Connection conn = null;
    // Statement and ResultSet to fetch the Order Information
    PreparedStatement pstmt = null;
    ResultSet rset = null;

    // If Connection Cache is not properly initialized, then throw an error
    if (ocacheimpl == null) {
      throw new Exception("Connection Cache Not Properly Initialized");
    }

    // Fetch Order Information from the database
    try {

      // Get Connection from the Connection Cache.
      conn = ocacheimpl.getConnection();


      // Prepare Query to fetch the Order Information
      pstmt = conn.prepareStatement("SELECT O.ORDER_ID, C.CUST_FIRST_NAME, "+
        " C.CUST_LAST_NAME, TO_CHAR(O.ORDER_DATE, 'DD-MON-YYYY'), OI.UNIT_PRICE, " +
        " OI.QUANTITY, O.ORDER_STATUS FROM ORDERS O, ORDER_ITEMS OI, CUSTOMERS C " +
        " WHERE OI.PRODUCT_ID = ? AND OI.ORDER_ID = O.ORDER_ID AND " + " O.CUSTOMER_ID = C.CUSTOMER_ID ");
      int prdId = new Integer(productId).intValue();

      // Bind Product Dd
      pstmt.setInt(1, prdId);

      // Execute Query
      rset = pstmt.executeQuery();

      // Loop through the result set
      while (rset.next()) {
        orderEntry = rset.getInt(1) + "****" + rset.getString(2) + " " +
        rset.getString(3) + "****" + rset.getString(4) + "****" + rset.getFloat(5) +
         "****" + rset.getInt(6) + "****" + rset.getString(7);
        orderList.add(orderEntry);
      }

    }catch (java.sql.SQLException ex) { // Trap SQL Errors
       throw new Exception("SQL Error = " +ex.toString());
    } finally {
      try {
         // Close Result Set and Statement
         rset.close();
         pstmt.close();
         // Closing connection returns the Connection to the Cache.
         conn.close();
      } catch (Exception ex) { // Trap Errors
        throw new Exception("SQL Error while closing objects = " +ex.toString());
      }
    }
    return orderList; // Return Order Information
  }

 /**
  * This method returns active size of the Cache.
  **/
  public int getActiveSize() {
    return ocacheimpl.getActiveSize();
  }


 /**
  * This method returns connection cache size.
  **/
  public int getCacheSize() {
     return ocacheimpl.getCacheSize();
  }

 /**
  * This Method initializes the variable 'cpds' with value of valid Connection
  * Cache Data Source.
  **/
  private void initializeConnectionCacheDataSrc() throws Exception {
    try {
      // Initialize the Datasource
      cpds = new OracleConnectionPoolDataSource();

      // Configure the Datasource with proper values of Host Name, User Name etc
      this.configureDataSource(cpds);

    } catch (SQLException sqlEx){ // Trap SQL Errors
        throw new Exception("SQL Errors = " + sqlEx.toString());
    } catch (Exception ex) { // Trap Generic Errors

        throw new Exception("Generic Errors = " + ex.toString());
    }

  }

 /**
  * This method configures the Datasource with appropriate values of Host Name,
  * User Name, Password etc.
  *
  * Note that the configuration parameters are stored in connection.properties
  * file.
  **/
  private void configureDataSource(OracleConnectionPoolDataSource ods) {
    try  {
        // Load the properties file to get the connection information
        Properties prop = this.loadParams("Connection");

        // Set Driver type, either 'thin' or 'oci8'
        // When 'oci8' is used TNSEntryName has to be set.
        ods.setDriverType("thin");

        // Set Network protocol
        ods.setNetworkProtocol("tcp");


        // Set Host name
        ods.setServerName  ((String)prop.get("HostName"));

        // Set Database SID
        ods.setDatabaseName((String)prop.get("SID"));

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

        // Set User name
        ods.setUser        ((String)prop.get("UserName"));

        // Set Password
        ods.setPassword    ((String)prop.get("Password"));
    } catch (IOException ex)  {
      ex.printStackTrace();
    }
  }

  /**
   * This method reads a properties file, which is passed as
   * the parameter to it, and loads it into a java Properties
   * object and returns the object.
   */
  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;
  }

  /**
  *  This method closes the connection cache.
  **/
  public void closeConnCache() throws SQLException {
    if(ocacheimpl != null) {
      ocacheimpl.close();
    }
  }
}