print Printer View e-mail E-mail this page Bookmark Bookmark 
/*
 * @author  Savitha
 * @version 1.0
 *
 * Development Environment        :  Oracle JDeveloper 10g
 * Name of the Application        :  GetWebRowSet.java
 * Creation/Modification History  :
 *
 *    Savitha     23-Jul-2003      Created.
 */
package oracle.otnsamples.jdbc;

  // Java IO imports used in the class.
  import java.io.IOException;
  import java.io.StringReader;
  import java.io.StringWriter;

  // Java SQLException class
  import java.sql.SQLException;

  // Oracle WebRowset class whose features are demonstrated in the class.
  import oracle.jdbc.rowset.OracleWebRowSet;

/**
 * This class will demonstrate the usage of the newly introduced Web Rowset
 * feature of Oracle10g JDBC driver class. WebRowset is a JDBC 3.0 feature.
 */
public class GetWebRowSet {

  /** Create the SQL Query string to get product_information records. */
  private final String sqlQuery =
                        "SELECT product_id, product_name, catalog_url, "
                        +"product_status, list_price, min_price "
                        +"FROM product_information WHERE "
                        +" product_id BETWEEN 3210 AND 3247 AND"
                        +" list_price IS NOT NULL AND min_price IS NOT NULL AND"
                        +" catalog_url IS NOT NULL AND product_status"
                        +" IS NOT NULL ORDER BY product_id ";

  /**
   * Default class constructor to instantiate the class and get the handle to
   * the underlying methods. The constructor class sets the System property
   * org.xml.sax.driver to oracle.xml.parser.v2.SAXParser.
   * Takes no arguments.
   */
  public GetWebRowSet() {
    System.setProperty("org.xml.sax.driver", "oracle.xml.parser.v2.SAXParser");
  }

  /**
   * This  method will create an xml document for the product_information
   * table data using webrowset writeXml() method. The XML document created by
   * the Web RowSet is converted to a string and is finally returned to the
   * calling JSP where XSL is applied to display the products.
   *
   * WebRowSet APIs are used to connect to the database as 'oe' user and execute
   * the SQL query using setter methods. WebRowSet object is populated when the
   * SQL query is executed.
   * @return<b>String</b> Returns the XML data of all product information
   * as a String.
   * @throws<b>SQLException</b> if any error in getting the product
   * information from the database.
   * @throws<b>Exception</b> any unknown exception.
   */
  public String createXMLProductInfo() throws SQLException, Exception {

    // Variables to hold and manipulate XML data.
    StringWriter writer = new StringWriter();

   // Variable to hold XML data.
   String xmlString = null;

    // Create an OracleWebRowSet.
    OracleWebRowSet wset = new OracleWebRowSet();

    try {
      // Set the Datasource i.e the database connection.
      wset.setDataSourceName("jdbc/OracleCoreWRS");

      // Set the query to be used to get the WebRowSet object.
      wset.setCommand(sqlQuery);

      // Set authenticating parameter values: username and password.
      wset.setUsername("oe");
      wset.setPassword("oe");

      // Execute the SQL query.
      wset.execute();

      // Now generate the XML document of all the product information and
      // hold the data to a java.io.Writer object.
      wset.writeXml(writer);

      // Convert the java.io.Writer object data to a string.
      xmlString = writer.toString();

      // Close the Writer object.
      writer.close();

      } catch (SQLException sqlex) {
          throw new Exception("Problem while connecting and querying the " +
                              "database table: " + sqlex.toString());
      } catch (IOException ioex) {
          throw new Exception("Could not construct a FileWriter: "+
                             ioex.toString());

      } finally  {
          try {
            // Close the open Web RowSet and connection objects.
            if (wset != null) wset.close();
          } catch (Exception ex)          {
              throw new Exception("UNKNOWN EX in finally block of"+
                              " createXMLProductInfo() method of given status: "
                              +ex.toString());
          }
      }

      //Return the xml string.
      return xmlString;
  }


  /**
   * This method will reuse the xml document/String created by
   * CreateXMLProductInfo() method. The xml string is passed to the method as an
   * argument from the calling JSP: results.jsp. The xml document content is
   * read using readXml(). The readXml() API will actually give the handle the
   * data in XML string that stores the all the product information. The record
   * for the particular is matched first, and then using different setXXX()
   * methods, data updates are made. updateRow() is called before
   * acceptChanges(). acceptChanges() will persist and synchronize the data in
   * the database.
   *
   * The method takes in parameters for product id, name, catalog_url, status,
   * listprice, maxprice and the xml string. These values are used in the
   * respective updates using setXXX() methods.
   *
   * @param<b>prodId</b> the product id.
   * @param<b>catUrl</b> the category_url of the product.
   * @param<b>prodStatus</b> the product status value.
   * @param<b>prodListPr</b> the product List price value.
   * @param<b>prodMinPr</b> the product Min Price value.
   * @param<b>xmlString</b> the xml string that is passed from the JSP. This is
   * actually the XML document created by the Web RowSet.
   * @throws<b>SQLException</b> if any error in getting the product
   * information from the database.
   * @throws<b>Exception</b> any unknown exception.
   */
  public void updateXMLProductInfo(int prodId,        String catUrl,
                                   String prodStatus, int prodListPr,
                                   int prodMinPr,     String xmlString)
   throws SQLException, Exception {

    // Create OracleWebRowSet object to store and update product information.
    OracleWebRowSet wsetRdr = new OracleWebRowSet();

    // Create a StringReader object using the passed xml string.
    StringReader strRdr = new StringReader(xmlString);

    try {

      // Create the Web Rowset object using the StringReader object.
      // Note that the Web RowSet does not require the connection.
      wsetRdr.readXml(strRdr);

      // setCommand() needs to be explicitly set to read the SQL string.
      wsetRdr.setCommand(sqlQuery);

      // Set authenticating parameter values: username and password.
      wsetRdr.setUsername("oe");
      wsetRdr.setPassword("oe");

      int id = 0;

      // Move to the first row.
      wsetRdr.beforeFirst();

      // Loop through the Web RowSet to get the product information.
      while (wsetRdr.next()) {

        id = wsetRdr.getInt(1);

        // If the passed product id matches, update the values.
        if (id == prodId) {
          wsetRdr.updateString(3,catUrl);     // Update catalog_url.
          wsetRdr.updateString(4,prodStatus); // Update Product status.
          wsetRdr.updateInt(5,prodListPr);    // Update Product ListPrice.
          wsetRdr.updateInt(6,prodMinPr);     // Update Product MinPrice.

          // First Call update Row.
          wsetRdr.updateRow();

          // Persist the changes in the database.
          wsetRdr.acceptChanges();
        }
      }
    } catch (SQLException sqlex) {
        throw new Exception("Problem while connecting and querying the" +
                             " database table: " + sqlex.toString());
    } catch (Exception ex) {
        throw new Exception("Problem while connecting to the database in the "+
                            "method updateXMLProductInfo() of given status: "+
                            ex.toString());
    } finally  {
         // Close the open Web RowSet.
         if (wsetRdr != null) wsetRdr.close();
    }

  }

  /**
   * This method will return the product information of the passed
   * product id as a String.  The product information is separated by
   * ##.
   * @param<b>prodId</b> the product id.
   * @param<b>xmlString</b> the xml string that holds all the product details.
   * This string was created by Web RowSet that is passed as an argument from
   * the calling JSP: editProduct.jsp.
   * Note that the method does not use any connection to the database, instead
   * reads the data from the xml string. This demonstrates the 'disconnected'
   * feature of the Web RowSet.
   * @return<b>String</b> an string of the product information separated by ##.
   * @throws<b>SQLException</b> if any error in getting the product
   * information from the database.
   * @throws<b>Exception</b> any unknown exception.
   */
  public String getXMLProductInfo(int prodId, String xmlString)
    throws SQLException, Exception {

    // Create OracleWebRowSet objects to store and update product information.
    OracleWebRowSet wsetRdr = new OracleWebRowSet();

    // A String to hold the product information.
    String productString  = null;

    // Create a StringReader object using the passed xml string.
    StringReader strRdr = new StringReader(xmlString);

    try {

      // Create the Web Rowset object using the StringReader object.
      wsetRdr.readXml(strRdr);

      int id = 0;

      // Use a StringBuffer to append all the product details separated by ##.
      StringBuffer strBuf = new StringBuffer();

      // Move to the first record.
      wsetRdr.beforeFirst();

      // Loop through the Web RowSet to fetch product details.
      while (wsetRdr.next()) {
        id = wsetRdr.getInt(1);
        if (id == prodId) {

          // Read data one by one and append to the StringBuffer by adding ##
          // inbetween.
          strBuf.append(wsetRdr.getString(2)).append("##").
                 append(wsetRdr.getString(3)).append("##").
                 append(wsetRdr.getString(4)).append("##").
                 append(wsetRdr.getString(5)).append("##").
                 append(wsetRdr.getString(6));
          productString = strBuf.toString();
        }
      }
    } catch (SQLException sqlex) {
        throw new Exception("Problem while connecting and querying the" +
                             " database table: " + sqlex.toString());
    } catch (Exception ex) {
        throw new Exception("Problem while connecting to the database: "+
                            ex.toString());
    } finally  {
         // Close the open Web RowSet.
         if (wsetRdr != null) wsetRdr.close();
    }

     // Return the string that has product details for the passed id.
     return productString;
  }

}
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