JDBC 3.0 feature: Web RowSet Product Listing Sample Application

Table of Contents

• Introduction
• Application Overview
• Application Design
• Sample Application Files
• Setting up the Sample Application

Pre-requisites:

Familiarity with the following Java and J2EE related technologies:

• JDBC(Java Database Connectivity)
• JSP(Java Server Pages) and tag libraries
• OC4J (Oracle9iAS Containers for J2EE)

Technical Overview:

JDBC Web RowSets :

The 'Product Listing sample application' illustrates the new JDBC 3.0 feature: Web RowSet, a new feature in Oracle Database 10g JDBC driver. Web RowSet represents a set of fetched rows that can be passed between tiers and components. The data can be obtained in the XML format that can be managed either by storing it in a String or writing it to a file in the local file system.

Benefits of the feature:

• Active connections with the datasource need not be maintained to pass the tabular data between tiers and components.
• Oracle Web RowSet extends OracleCachedRowSet. The rows (tabular data) referred to as “rowsets” can be read and written in XML format. This has enabled sending the rowsets over the Internet using the HTTP\XML protocol.

Different APIs are available to create and manage the Web RowSet object. The write operation is done using the writeXml() API and the read operation is done using the readXml() API. Web RowSet is implemented as a Java Bean with setter and getter methods. They can be directly used in JSPs just like any other Java Bean.

The sample application, 'Product Listing' makes use of a table: 'product_information' in the OE schema of the Oracle database. The application makes use of JSPs with Oracle tag library. Web RowSet APIs are used in a Java Class called: GetWebRowSet() that has methods for selecting/persisting table data. These methods are called from JSPs. The application provides a user interface with options for editing the product information, viewing the XML document created by Web Rowset and persisting the updated product information in the database.

The XML data obtained using Web RowSet APIs is transformed into HTML using XSLT. The xml.tld Oracle JSP Tag library is used to transform the XML data.

Design Notes:

Web Rowsets provide disconnected data i.e. it does not require continuous database connection to hold the data from the database. The ability of the Web Rowset to create and read the XML document effectively reduces the burden of creating frequent connections to the database. In fact, for the product listing, it takes one connection to get the data and create the XML document, and another one while persisting changes in the database. Retrieving individual product information in between does not require database connections; the Web RowSet data is used instead. Thus, the Web RowSet connects to the database only while persisting the changes in the database.

The application stores the XML document created as a JSP session attribute. JSPs access the session attribute to get the product information and save the updated information for a particular product ID. readXML() API is capable of creating the Web RowSet object using a Reader object without connecting to the database. Using this feature, information for different products can be obtained without connecting to the database. A connection is required only when updates need to be saved. At all other times, the disconnected data provided by the WebRowset can be used.

The sample has harnessed the power of JSPs to provide a UI and Oracle JSP tag library xml.tld to read the XML and apply XSL stylesheet. Using the xml.tld tags simplifies the process of applying the XSL on the generated XML document and presenting it to the user.

Application Architecture Diagram

Code Support:

Following is the code snippet of createXMLProductFile() method, which creates a Web RowSet and uses the XML document created by it in the application. For more information about the code, refer to the GetWebRowSet.java file under the src/oracle/otnsamples/jdbc folder.

Note: See the Sample Application Files section for folder and file details.

................. .................. // 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(); // 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(); .......... ...........

Following is the updateXMLProductInfo() method that reads the Web RowSet object and acquires the database connection only while saving the changes.

// 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); ........ // 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(conn); } } ...........

Following is a listing of the directory structure of the deliverable WebRowset.jar file. Note that WebRowset is the top-level directory.

4.1 Application Documentation Files

4.2.1 Application Java Files

4.2.2 Application JSP Files

Refer the Install.html document for step-by-step instructions on extracting files, installing, and configuring to get the application up and running.

Please enter your comments about this sample in the OTN Sample Code Discussion Forum.