Oracle Web RowSet - Part1

Exclusive offer: get 50% off this eBook here
JDBC 4.0 and Oracle JDeveloper for J2EE Development

JDBC 4.0 and Oracle JDeveloper for J2EE Development — Save 50%

A J2EE developer's guide to using Oracle JDeveloper's integrated database features to build data-driven applications with this book and eBook

$26.99    $13.50
by Deepak Vohra | April 2009 | Java Oracle

This article is a two-part series. In this article by Deepak Vohra, we will use the XML document representation of a result set generated with an SQL query to modify a relational database table. You will learn to set the environment, create a Web RowSet, modify a database table with Web RowSet, create a new row, read a row, update a row, delete a row, update the database table and also get acquainted with JDBC 4.0 Version.

The ResultSet interface requires a persistent connection with a database to invoke the insert, update, and delete row operations on the database table data. The RowSet interface extends the ResultSet interface and is a container for tabular data that may operate without being connected to the data source. Thus, the RowSet interface reduces the overhead of a persistent connection with the database.

In J2SE 5.0, five new implementations of RowSet—JdbcRowSet, CachedRowSet, WebRowSet, FilteredRowSet, and JoinRowSet—were introduced. The WebRowSet interface extends the RowSet interface and is the XML document representation of a RowSet object. A WebRowSet object represents a set of fetched database table rows, which may be modified without being connected to the database.

Support for Oracle Web RowSet is a new feature in Oracle Database 10g driver. Oracle Web RowSet precludes the requirement for a persistent connection with the database. A connection is required only for retrieving data from the database with a SELECT query and for updating data in the database after all the required row operations on the retrieved data has been performed. Oracle Web RowSet is used for queries and modifications on the data retrieved from the database. Oracle Web RowSet, as an XML document representation of a RowSet facilitates the transfer of data.

In Oracle Database 10g and 11g JDBC drivers, Oracle Web RowSet is implemented in the oracle.jdbc.rowset package. The OracleWebRowSet class represents a Oracle Web RowSet. The data in the Web RowSet may be modified without connecting to the database. The database table may be updated with the OracleWebRowSet class after the modifications to the Web RowSet have been made. A database JDBC connection is required only for retrieving data from the database and for updating the database. An XML document representation of the data in a Web RowSet may be obtained for data exchange. In this article, the Web RowSet feature in Oracle 10g database JDBC driver is implemented in JDeveloper 10g. An example Web RowSet will be created from a database. The Web RowSet will be modified and stored in the database table.

In this article, we will learn the following:

  • Creating a Oracle Web RowSet object
  • Adding a row to Oracle Web RowSet
  • Modifying the database table with Web RowSet

In the second half of the article, we will cover the following :

  • Reading a row from Oracle Web RowSet
  • Updating a row in Oracle Web RowSet
  • Deleting a row from Oracle Web RowSet
  • Updating Database Table with modified Oracle Web RowSet

Setting the Environment

We will use Oracle database to generate an updatable OracleWebRowSet object. Therefore, install Oracle database 10g including the sample schemas. Connect to the database with the OE schema:

SQL> CONNECT OE/<password>

Create an example database table, Catalog, with the following SQL script:

CREATE TABLE OE.Catalog(Journal VARCHAR(25), Publisher Varchar(25),
Edition VARCHAR(25), Title Varchar(45), Author Varchar(25));
INSERT INTO OE.Catalog VALUES('Oracle Magazine', 'Oracle
Publishing', 'July-August 2005', 'Tuning Undo Tablespace',
'Kimberly Floss');
INSERT INTO OE.Catalog VALUES('Oracle Magazine', 'Oracle
Publishing', 'March-April 2005', 'Starting with Oracle ADF', 'Steve
Muench');

Configure JDeveloper 10g for Web RowSet implementation. Create a project in JDeveloper. Select File | New | General | Application. In the Create Application window specify an Application Name and click on Next. In the Create Project window, specify a Project Name and click on Next. A project is added in the Applications Navigator.

Oracle Web RowSet - Part1

Next, we will set the project libraries. Select Tools | ProjectProperties and in the Project Properties window, select Libraries | Add Library to add a library. Add the Oracle JDBC library to project libraries. If the Oracle JDBC drivers version prior to the Oracle database 10g (R2) JDBC drivers version is used, create a library from the Oracle Web RowSet implementation classes JAR file: C:JDeveloper10.1.3jdbclibocrs12.jar. The ocrs12.jar is required only for JDBC drivers prior to Oracle database 10g (R2) JDBC drivers. In Oracle database 10g (R2) JDBC drivers OracleRowSet implementation classes are packaged in the ojdbc14.jar. In Oracle database 11g JDBC drivers Oracle RowSet implementation classes are packaged in ojdbc5.jar and ojdbc6.jar.

In the Add Library window select the User node and click on New. In the Create Library window specify a Library Name, select the Class Path node and click on Add Entry. Add an entry for ocrs12.jar. As Web RowSet was introduced in J2SE 5.0, if J2SE 1.4 is being used we also need to add an entry for the RowSet implementations JAR file, rowset.jar. Download the JDBC RowSet Implementations 1.0.1 zip file, jdbc_rowset_tiger-1_0_1-mrel-ri.zip, from http://java.sun.com/products/jdbc/download.html#rowset1_0_1 and extract the JDBC RowSet zip file to a directory. Click on OK in the Create Library window. Click on OK in the Add Library window. A library for the Web RowSet application is added.

Oracle Web RowSet - Part1

Now configure an OC4J data source. Select Tools | Embedded OC4J Server Preferences. A data source may be configured globally or for the current workspace. If a global data source is created using Global | Data Sources, the data source is configured in the C:JDeveloper10.1.3jdevsystemoracle.j2ee.10.1.3.36.73embedded-oc4jconfig data-sources.xml file. If a data source is configured for the current workspace using Current Workspace | Data Sources, the data source is configured in the data-sources.xml file. For example, the data source file for the WebRowSetApp application is WebRowSetApp-data-sources.xml. In the Embedded OC4J Server Preferences window configure either a global data source or a data source in the current workspace. A global data source definition is available to all applications deployed in the OC4J server instance. A managed-data-source element is added to the data-sources.xml file.

<managed-data-source name='OracleDataSource' connection-pool-
name='Oracle Connection Pool' jndi-name='jdbc/OracleDataSource'/>
<connection-pool name='Oracle Connection Pool'>
<connection-factory factory-
class='oracle.jdbc.pool.OracleDataSource' user='OE' password='pw'
url="jdbc:oracle:thin:@localhost:1521:ORCL">
</connection-factory>
</connection-pool>

Add a JSP, GenerateWebRowSet.jsp, to the WebRowSet project. Select File | New | Web Tier | JSP | JSP. Click on OK. Select J2EE 1.3 or J2EE 1.4 in the Web Application window and click on Next. In the JSP File window specify a File Name and click on Next. Select the default settings in the Error Page Options page and click on Next. Select the default settings in the Tag Librarieswindow and click on Next. Select the default options in the HTML Options window and click on Next. Click on Finish in the Finish window. Next, configure the web.xml deployment descriptor to include a reference to the data source resource configured in the data-sources.xml file as shown in following listing:

<resource-ref>
<res-ref-name>jdbc/OracleDataSource</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

Oracle Web RowSet - Part1

JDBC 4.0 and Oracle JDeveloper for J2EE Development A J2EE developer's guide to using Oracle JDeveloper's integrated database features to build data-driven applications with this book and eBook
Published: April 2008
eBook Price: $26.99
Book Price: $44.99
See more
Select your format and quantity:

Creating a Web RowSet

In this section, we will create a Web RowSet from a database table and an XML document representation of the Web RowSet is generated. Create a Java class in JDeveloper with File | New | General | Java Class. In the Create Java Class window specify the class name,WebRowSetQuery, and package name and click on OK. A Java class, WebRowSetQuery.java gets added to the WebRowSet project. In the Java application first import the oracle.jdbc.rowset package classes. Create an OracleWebRowSet class object:

OracleWebRowSet webRowSet=new OracleWebRowSet();

Set the data source name to obtain a JDBC connection with the database. The data source name is configured in the data-sources.xml file:

webRowSet.setDataSourceName("jdbc/OracleDataSource");

Set the SQL query command for the OracleWebRowSet class object:

webRowSet.setCommand(selectQuery);

Variable selectQuery is the String value for the SQL statement that is to be used to query the database. SQL statement value is obtained from an input field in a JSP. Set the username and password to obtain a JDBC connection:

webRowSet.setUsername("OE");
webRowSet.setPassword("<password>");

Set the read only, fetch size, and max rows attributes of the OracleWebRowSetobject:

webRowSet.setReadOnly(false);
webRowSet.setFetchSize(5);
webRowSet.setMaxRows(3);

Run the SQL command specified in the setCommand() method with the execute() method:

webRowSet.execute();

A Web RowSet is created consisting of the data retrieved from the database table with the SQL query. Generate an XML document from the Web RowSet using the writeXml() method;

OutputStreamWriter output=new OutputStreamWriter( new
FileOutputStream(new File("c:/output/output.xml")));
webRowSet.writeXml(output);

Oracle Web RowSet also provides readXml() methods to read an Oracle Web RowSet object in XML format using a Reader object or an InputStream object. If the readXml() methods are to be used set one of the following JAXP system properties:

  • javax.xml.parsers.SAXParserFactory
  • javax.xml.parsers.DocumentBuilderFactory

For example, set the SAXParserFactory property as follows:

System.setProperty("javax.xml.parsers.SAXParserFactory",
"oracle.xml.jaxp.JXSAXParserFactory");

WebRowSetQuery.java also has methods to read, update, delete, and insert a row in the database table, which will be discussed in the subsequent sections. WebRowSetQuery.java application is listed below:

package webrowset;
import oracle.jdbc.rowset.*;
import java.io.*;
import java.sql.SQLException;
public class WebRowSetQuery
{
public OracleWebRowSet webRowSet;
public String selectQuery;
public WebRowSetQuery()
{
}
public WebRowSetQuery(OracleWebRowSet webRowSet)
{
this.webRowSet = webRowSet;
}
public void generateWebRowSet(String selectQuery)
{
try
{
webRowSet = new OracleWebRowSet();
webRowSet.setDataSourceName("jdbc/OracleDataSource");
webRowSet.setCommand(selectQuery);
webRowSet.setUsername("oe");
webRowSet.setPassword("pw");
webRowSet.setReadOnly(false);
webRowSet.setFetchSize(5);
webRowSet.setMaxRows(3);
webRowSet.execute();
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
}
public void generateXMLDocument()
{
try
{
OutputStreamWriter output = new OutputStreamWriter(
new FileOutputStream(new File("c:/output/output.xml")));
webRowSet.writeXml(output);
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
catch (IOException e)
{
}
}
public void deleteRow(int row)
{
try
{
webRowSet.absolute(row);
webRowSet.deleteRow();
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
}
public void insertRow(String journal, String publisher,
String edition, String title, String author)
{
try
{
webRowSet.moveToInsertRow();
webRowSet.updateString(1, journal);
webRowSet.updateString(2, publisher);
webRowSet.updateString(3, edition);
webRowSet.updateString(4, title);
webRowSet.updateString(5, author);
webRowSet.insertRow();
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
}
public void updateRow(int rowUpdate, String journal,
String publisher, String edition, String title, String author)
{
try
{
webRowSet.absolute(rowUpdate);
webRowSet.updateString(1, journal);
webRowSet.updateString(2, publisher);
webRowSet.updateString(3, edition);
webRowSet.updateString(4, title);
webRowSet.updateString(5, author);
webRowSet.updateRow();
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
}
public String[] readRow(int rowRead)
{
String[] resultSet = null;
try
{
resultSet = new String[5];
webRowSet.absolute(rowRead);
resultSet[0] = webRowSet.getString(1);
resultSet[1] = webRowSet.getString(2);
resultSet[2] = webRowSet.getString(3);
resultSet[3] = webRowSet.getString(4);
resultSet[4] = webRowSet.getString(5);
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
return resultSet;
}
public void updateDatabase()
{
try
{
webRowSet.acceptChanges();
}
catch (java.sql.SQLException e)
{
System.out.println(e.getMessage());
}
}
}

The SELECT query with which the Web RowSet is created is input from the GenerateWebRowSet.jsp JSP, which was added in the Setting the Environment section, and is listed below:

<%@ page contentType="text/html;charset=windows-1252"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<title>Generate WebRowSet</title>
</head>
<body>
<form>
</form>
<%
String selectQuery=request.getParameter("selectQuery");
webrowset.WebRowSetQuery query=new webrowset.WebRowSetQuery();
if(selectQuery!=null)
{
query.generateWebRowSet(selectQuery);
query.generateXMLDocument();
}
%>
<form name="query" action="GenerateWebRowSet.jsp" method="post">
<table>
<tr>
<td>Select Query:</td>
</tr><tr><td>
<textarea name="selectQuery" rows="5"
cols="50"></textarea>
</td>
</tr><tr><td>
<input class="Submit" type="submit" value="Apply"/>
</td>
</tr>
</table>
</form>
</body>
</html>

Right-click on the GenerateWebRowSet.jsp and select Run to run the JSP.

Oracle Web RowSet - Part1

In the JSP page displayed, specify the SQL query from which a Web RowSet is to be generated. For example, specify SQL Query:

SELECT JOURNAL, PUBLISHER, EDITION, TITLE, AUTHOR FROM OE.Catalog
Click on Apply.

Oracle Web RowSet - Part1

A Web RowSet is generated and an XML document is generated from the Web RowSet. The XML document output from the Web RowSet includes the metadata information for the JDBC data source, the database table, and the data in the table; the data element tag represents the data in the database table. An XML document generated from a Web RowSet is based on the DTD (http://java.sun.com/j2ee/dtds/RowSet.dtd). The XML document generated from the example database table Catalog as follows:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE RowSet PUBLIC '-//Sun Microsystems, Inc.//DTD RowSet//EN'
'http://java.sun.com/j2ee/dtds/RowSet.dtd'>
<RowSet>
<properties>
<command>SELECT JOURNAL, PUBLISHER, EDITION, TITLE, AUTHOR FROM
OE.Catalog</command>
<concurrency>1007</concurrency>
<datasource>jdbc/OracleDataSource</datasource>
<escape-processing>true</escape-processing>
<fetch-direction>1002</fetch-direction>
<fetch-size>10</fetch-size>
<isolation-level>2</isolation-level>
<key-columns>
</key-columns>
<map></map>
<max-field-size>0</max-field-size>
<max-rows>3</max-rows>
<query-timeout>0</query-timeout>

In this section, the procedure to generate a Web RowSet from a database table was explained. In the following section the Web RowSet is modified and the modified data stored in the database table.

JDBC 4.0 and Oracle JDeveloper for J2EE Development A J2EE developer's guide to using Oracle JDeveloper's integrated database features to build data-driven applications with this book and eBook
Published: April 2008
eBook Price: $26.99
Book Price: $44.99
See more
Select your format and quantity:

Modifying a Database Table with Web RowSet

With ResultSet interface, to modify the data in the database, a JDBC connection with the database is required to insert, delete, or update a database table row. With a Web RowSet, the data may be modified in the OracleWebRowSet object, and a connection is required only to update the database table with the data in the Web RowSet after all the modifications have been made to the Web RowSet. In this section, the data in the Web RowSet is modified and the database table is updated with the modified Web RowSet. A JDBC connection is not required to modify the data in the example Web RowSet. An OracleWebRowSet object is generated as in the previous section.

Create a JSP, ModifyWebRowSet.jsp, to create and modify a Web RowSet from an SQL query. Also add JSPs CreateRow.jsp, ReadRow.jsp, UpdateRow.jsp, DeleteRow.jsp, and UpdateDatabase.jsp, which are listed later in this article. ModifyWebRowSet.jsp, the JSP used to create and modify a Web RowSet is listed as follows:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ page session="true"%>
<html>
<head>
<title>Modify Database Table with Web RowSet</title>
</head>
<body>
<h3>Modify Database Table with Web RowSet</h3>
<%webrowset.WebRowSetQuery query=null;%>
<%String selectQuery=request.getParameter("selectQuery");
if(selectQuery!=null){
query=new webrowset.WebRowSetQuery();
query.generateWebRowSet(selectQuery);
session.setAttribute("query", query);
}
%>
<form name="query" action="ModifyWebRowSet.jsp" method="post">
<table>
<tr>
<td>Select Query:</td>
</tr><tr><td>
<textarea name="selectQuery" rows="5"
cols="50"></textarea>
</td></tr><tr><td>
<input class="Submit" type="submit" value="Apply Query"/>
</td></tr>
<tr><td><a href="CreateRow.jsp">Create Row</a></td></tr>
<tr><td><a href="ReadRow.jsp">Read Row</a></td></tr>
<tr><td><a href="UpdateRow.jsp">Update Row</a></td></tr>
<tr><td><a href="DeleteRow.jsp">Delete Row</a></td></tr>
<tr><td><a href="UpdateDatabase.jsp">Update
Database</a></td></tr>
</table>
</form>
</body>
</html>

The directory structure of the Web RowSet application is shown in the Applications Navigator. Run the ModifyWebRowSet.jsp JSP in JDeveloper. The JSP is displayed in a browser. Specify a SQL query to generate a Web RowSet. Click on Apply Query. Subsequently, we will modify the Web RowSet and update the database.

Oracle Web RowSet - Part1

A Web RowSet is generated. We will use the Web RowSet object to create, read, update, and delete the result set obtained with the SQL query. In the ModifyWebRowSet.jsp, set the WebRowSetQuery object as a session object attribute:

session.setAttribute("query", query);

The OracleWebRowSet object of the ModifyWebRowSet object will be used in the Create, Read, Update, and Delete JSPs.

Creating a New Row

Next, create a new row in the Web RowSet. Click on the Create Row link in the ModifyWebRowSet.jsp JSP.

Oracle Web RowSet - Part1

The CreateRow.jsp is displayed. Specify the row values to add and click on Apply.

Oracle Web RowSet - Part1

In the CreateRow.jsp, the input values are retrieved from the JSP and the insertRow() method of the WebRowSetQuery class is invoked. The WebRowSetQuery object is retrieved from the session object:

WebRowSetQuery query=( webrowset.WebRowSetQuery)
session.getAttribute("query");

In the insertRow() method OracleWebRowSet object cursor is moved to the insert row:

webRowSet.moveToInsertRow();

Set the row values with the updateString() method:

webRowSet.updateString(1, journal);
webRowSet.updateString(2, publisher);
webRowSet.updateString(3, edition);
webRowSet.updateString(4, title);
webRowSet.updateString(5, author);

Add the row to the OracleWebRowSet:

webRowSet.insertRow();

A new row is added in the OracleWebRowSet object. A new row is not yet added to the database. CreateRow.jsp is listed as follows:

<%@ page contentType="text/html;charset=windows-1252"%>
<%@ page session="true"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<title>Create Row with Web RowSet</title>
</head>
<body>
<form><h3>Create Row with Web RowSet</h3>
<table>
<tr><td><a href="ModifyWebRowSet.jsp">Modify Web RowSet
Page</a></td></tr>
</table>
</form>
<%
webrowset.WebRowSetQuery query=null;
query=(webrowset.WebRowSetQuery)
session.getAttribute("query");
String journal=request.getParameter("journal");
String publisher=request.getParameter("publisher");
String edition=request.getParameter("edition");
String title=request.getParameter("title");
String author=request.getParameter("author");
if(journal!=null||publisher!=null||edition!=null||title!=null
||author!=null){
query.insertRow(journal, publisher, edition, title, author);
}
%>
<form name="query" action="CreateRow.jsp" method="post">
<table>
<tr>
<td>
<h4>Insert Row</h4>
</td>
</tr>

Summary

Here we have  learned to set the environment, create a Web RowSet, modify a database table with Web RowSet, and create a new row. In the next part, we will learn to read a row, update a row, delete a row, update the database table and also get acquainted with JDBC 4.0 Version. To get a thorough understanding of the topic, reading the second part is recommended.

If you have read this article you may be interested to view :

About the Author :


Deepak Vohra

Deepak Vohra is a consultant and a principal member of the NuBean.com software company. Deepak is a Sun Certified Java Programmer and Web Component Developer, and has worked in the fields of XML and Java programming and J2EE for over five years. Deepak is the co-author of the Apress book Pro XML Development with Java Technology and was the technical reviewer for the O'Reilly book WebLogic: The Definitive Guide. Deepak was also the technical reviewer for the Course Technology PTR book Ruby Programming for the Absolute Beginner, and the technical editor for the Manning Publications book Prototype and Scriptaculous in Action. Deepak is also the author of the Packt Publishing books JDBC 4.0 and Oracle JDeveloper for J2EE Development; Processing XML documents with Oracle JDeveloper 11g; EJB 3.0 Database Persistence with Oracle Fusion Middleware 11g; and Java 7 JAX-WS Web Services.

Books From Packt

Pentaho Reporting 1.0 for Java Developers
Pentaho Reporting 1.0 for Java Developers

Drools JBoss Rules 5.0 Developer's Guide
Drools JBoss Rules 5.0 Developer's Guide

Liferay Portal 5.2 Systems Development
Liferay Portal 5.2 Systems Development

Grails 1.1 Web Application Development
Grails 1.1 Web Application Development

Oracle Essbase 9 Implementation Guide
Oracle Essbase 9 Implementation Guide

Oracle 10g/11g Data and Database Management Utilities
Oracle 10g/11g Data and Database Management Utilities

Mastering Oracle Scheduler in Oracle 11g Databases
Mastering Oracle Scheduler in Oracle 11g Databases

Oracle SOA Suite Developer's Guide
Oracle SOA Suite Developer's Guide

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software