print Printer View e-mail E-mail this page Bookmark Bookmark
oracle.otnsamples.streams.StreamsMessagingBean (Java2HTML) 
/*
 * @author  Anupama Majety
 * @version 1.0
 *
 * Name of the Application        :  StreamsMessagingBean.java
 * Development Environment        :  Oracle JDeveloper 10g
 * Creation/Modification History  :
 *
 *    Anupama Majety      10-Sep-2003      Created
 *
 */

// Package name
package oracle.otnsamples.streams;

// JDBC imports
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;

// IO imports
import java.io.IOException;

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

// Servlet imports
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

// Oracle JDBC imports
import oracle.jdbc.pool.OracleDataSource;

// Other imports
import oracle.otnsamples.streams.Product;

/**
 * This class contains methods to retrieve information for the
 * manufacturer from his local database and connecting to it for
 * Adding a product information, update a product details or delete
 * a product.
 */
public class StreamsMessagingBean {

 /**
  *  Initialize the Connection object
  */
  private Connection conn     = null;

  /**
   * Holds session object.
   */
  private HttpSession session = null;

  /**
   * 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.
   *
   * @param     file             Name of the file that has to be loaded.
   * @exception IOException      In case of unexpected Exceptions
   */
  public 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 Oracle DataSource object. Please
   * substitute the database connection parameters with appropriate values in
   * ConnForFirstDB.properties file
   */
  public void dbConnection() {
    try {

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

      // Load the properties file to get the connection information
      Properties firstDbConnProp =
                    StreamsMessagingBean.loadParams("ConnForFirstDB");

      StreamsMessagingBean.configureDataSource(ods, firstDbConnProp);

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

    } catch (SQLException sqlEx){ // Handle SQL Errors
      System.out.println("SQL Errors = " + sqlEx.toString());
    } catch(Exception excep) { // Handle other errors
      System.out.println(" Couldn't retrieve connection... "
                                          + excep.toString());
    }
  }

  /**
   * This method helps to configure the Data Source
   * @param ods Oracle DataSource object
   * @param prop Properties object
   */
  public static void configureDataSource(OracleDataSource ods,
                                    Properties prop) {
     // Driver type 'oci8'
    ods.setDriverType  ("oci8");

    // TNSEntry name
    ods.setTNSEntryName((String)prop.get("TnsEntryName"));

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

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

  /**
   * This method helps to retrieve the details all the products present with
   * the local database and return it to be displayed in the JSP.
   * @return Details of the product
   * @exception Exception In case of unreported exception
   */
  public Hashtable getProductDetails() throws Exception {

    // Declaration Statements
    Statement stmt           = null;
    ResultSet rset           = null;
    Product productObj       = null;

    // Holds the details of the product.
    Hashtable productDetails = new Hashtable();

    // Construct the query to retrieve the details of the product.
    String query = "SELECT productId, productName, "
                    + "NVL(productdesc,'NOT SPECIFIED'), price, quantityOnHand"
                    + " FROM prodstore.Product_Master";

    // Create the statement.
    stmt = conn.createStatement();

    // Execute the query and retrieve the product Details
    rset = stmt.executeQuery(query);

    // Store the details of the product in hash table.
    while(rset.next()) {
      productObj = new Product(rset.getInt(1), rset.getString(2),
                                rset.getString(3),
                                rset.getFloat(4), rset.getInt(5));
      productDetails.put(new Integer(rset.getInt(1)), productObj);
    }

    // Perform cleanup
    rset.close();
    stmt.close();

    // Return the product details
    return(productDetails);
  }

  /**
   * This method helps to add a product in the database.
   * @param request HttpServletRequest object
   * @throws Exception Incase of unexpected Errors
   */
  public void addProduct(HttpServletRequest request) throws Exception {

    PreparedStatement pstmt = null;

    // Prepare the query
    pstmt = conn.prepareStatement("INSERT INTO prodstore.Product_Master "
            + "(productId, productName, productDesc, price, quantityOnHand)"
            + " VALUES (prodstore.Product_Id_Seq.nextVal, ?, ?, ?, ?)");

    // Retrieve the shipping details from Request and set them.
    pstmt.setString(1, request.getParameter("ProductNameReq"));
    pstmt.setString(2, request.getParameter("ProductDesc"));
    pstmt.setFloat(3, Float.parseFloat(request.getParameter("PriceReq")));
    pstmt.setInt(4, Integer.parseInt(request.getParameter("QuantityReq")));

    // Execute the prepared Statement.
    pstmt.executeUpdate();

    // Close the prepared statement
    pstmt.close();

    // Retrieve the id of the product which is added to the database
    int productId          = this.retrieveProductId();

    // Get the details of the product in database
    Hashtable productsList = this.getProductDetails();

    // Retrieve the session being used.
    session                = request.getSession(false);

    // Store the details required in Session
    session.setAttribute("ProductIds", new Integer(productId));
    session.setAttribute("ProductsList", productsList);
  }

  /**
   * This method helps to retrieve the productId used currently from sequence
   * @return product Id
   * @throws Exception Incase of unexpected Errors
   */
  private int retrieveProductId() throws Exception {
    Statement stmt          = null;
    ResultSet rset          = null;

    int prdId = 0;

    // Construct the query to retrieve the id of the product added.
    String query = "SELECT prodstore.Product_Id_Seq.currval FROM dual";

    // Create the statement.
    stmt = conn.createStatement();

    // Execute the query
    rset = stmt.executeQuery(query);

    if(rset.next()) {
      prdId = rset.getInt(1);
    }

    // Perform cleanup
    rset.close();
    stmt.close();

    // Return the id of the product
    return prdId;

  }

  /**
   * This method helps to delete the details of the products selected.
   * @param request HttpServletRequest object
   * @exception Exception  Incase of unreported errors
   */
  public void deleteProducts(HttpServletRequest request) throws Exception {

    PreparedStatement pstmt  = null;

    // Retrieve the Id of the products selected for Deletion
    String[] deletePrds      = request.getParameterValues("PrdIDSelected");

    // Construct the query
    StringBuffer queryString = new StringBuffer("DELETE FROM "
              + "prodstore.Product_Master WHERE"
              + " productId IN (");

    int length = deletePrds.length;

    for(int i=0;i<length;i++) {
      queryString.append("?");
      if (i < (length-1)) {
          queryString.append(", ");

      }
    }

    queryString.append(")");

    // Prepare the query
    pstmt = conn.prepareStatement(queryString.toString());

    // Set the value of the parameters
    for(int i=0;i<length;i++) {
      pstmt.setInt(i+1, new Integer(deletePrds[i]).intValue());
    }

    // Execute the prepared Statement.
    pstmt.executeUpdate();

    // Close the prepared statement
    pstmt.close();

    // Retrieve the details of the products in database
    Hashtable productsList = this.getProductDetails();

    // Retrieve the session being used.
    session = request.getSession(false);

    session.setAttribute("ProductIds", deletePrds);
    session.setAttribute("ProductsList", productsList);
  }

  /**
   * This method helps to update the details of the products selected.
   * @param request HttpServletRequest object
   * @exception Exception  Incase of unreported errors
   */
  public void updateProduct(HttpServletRequest request) throws Exception {

    PreparedStatement pstmt = null;

    // Retrieve the id of the product selected for updation
    String productId        = request.getParameter("PrdId");

    // Prepare the query
    pstmt = conn.prepareStatement("UPDATE prodstore.Product_Master "
            + " SET productName = ?, productDesc = ?, price = ?, "
            + "quantityOnHand = ? where productID = ?");

    // Retrieve the shipping details from Request and set them.
    pstmt.setString(1, request.getParameter("ProductNameReq"));
    pstmt.setString(2, request.getParameter("ProductDesc"));
    pstmt.setFloat(3, Float.parseFloat(request.getParameter("PriceReq")));
    pstmt.setInt(4, Integer.parseInt(request.getParameter("QuantityReq")));
    pstmt.setInt(5, Integer.parseInt(productId));

    // Execute the prepared Statement.
    pstmt.executeUpdate();

    // Close the prepared statement
    pstmt.close();

    // Retrieve the details of the products present in database.
    Hashtable productsList = this.getProductDetails();

    // Retrieve the session being used.
    session = request.getSession(false);

    session.setAttribute("ProductIds", productId);
    session.setAttribute("ProductsList", productsList);
  }

 /**
  *  Closes the connections to database.
  *
  *  @exception SQLException   Raised while closing the connection
  *  @exception Exception   In case of an unreported exception
  */
  public void cleanUp()
    throws SQLException,Exception {
    if(session != null){
      // Invalidate session if it exists
      session.invalidate();
      session = null;
    }
    // Close the connection
    if(conn != null)  conn.close();

  }

}
E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy