JDBC RowSet DML Sample - Readme

Date: 01-Mar-2004

Table of Contents

Introduction 

Prerequisite 

To understand more about the working of the RowSet DML sample, we expect you to have a working knowledge of Oracle database, SQL, PL/SQL concepts. Expertise in developing applications using Java and Swing is also required. Look at the Sun site for extensive information on Java and OTN for Oracle related technologies.

Technical Overview 

This sample, JDBC RowSet DML application, demonstrates the usage of the JDBC RowSets in the DML operations. The data manipulation language(DML) operations include SELECT, INSERT, UPDATE, DELETE on a database table. RowSet is a JDBC 2.0 feature and is available only in JDK 1.2 or later.

A RowSet is an object which encapsulates a set of rows. These rows are accessible though the javax.sql.RowSet interface. This interface supports component models of development, like JavaBeans, and is part of JDBC optional package by JavaSoft. JavaSoft supports three kinds of RowSet:

• Cached RowSet
• JDBC RowSet
• Web RowSet

Oracle RowSet implementation is in the oracle.jdbc.rowset package, and the classes are found in a separate archive called ocrs12.zip/jar. It is a separately downloadable library class. To use the RowSet, you need to include this archive in the CLASSPATH.

The RowSet interface provides a set of properties which can be altered to access the data in the database through a single interface. It supports properties and events which forms the core of JavaBeans. It has various properties like connect string, user name, password, type of connection, the query string itself, and also the parameters passed to the query. Actually, the JDBC RowSet serves mainly as a thin wrapper around a ResultSet object to make a JDBC driver look like a JavaBeans component.

Application Overview 

In this sample application, the usage of JDBC RowSet in a Java application for simple DML operations is shown on a simple database table called, 'otn_airlines'. The RowSet instance is configured to connect to a JDBC data source i.e. an Oracle Database; the data is then read using the RowSet from the database table - otn_airlines. The application retrieves and displays otn_airlines records and, allows inserts, updates and deletes on it as well.

Application Design 

The sample application is swing based. There is a main frame class to handle all the GUI of the application. Another sample class instantiates the GUI; it also calls the method from the business class to check if the application has the required database table and the startup records.

A business class called the AirlinesDML, has all the methods for the DML operations - SELECT, INSERT, UPDATE and DELETE. A singleton class called the RowSetCache, is created to return a rowset instance. The rowset is configured only once and the same is used across all the operations. The application code does not make use of the normal connection, statement or the resultset object but does all the operations using the available RowSet methods.

Following is the Class Diagram generated for the application from Oracle JDeveloper.

Code Support

Following is the code snippet of loadRowSet() method in RowSetCache.java class. This method will configure the RowSet instance to connect to the database. For more information about the code, refer to the RowSetCache.java file under the src/oracle/otnsamples/jdbc folder.

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

..... private RowSet rowset = null; ..... private OracleJDBCRowSet loadRowSet() throws IOException, SQLException { username = (String)prop.get("UserName"); password = (String)prop.get("Password"); ........... ........... try { // Loads a ResourceBundle and populates the rowset with the values. Properties prop = this.loadParams("config/Connection"); username = (String)prop.get("UserName"); password = (String)prop.get("Password"); port = (String)prop.get("Port"); sid = (String)prop.get("SID"); host = (String)prop.get("HostName"); // Create the database url string url = "jdbc:oracle:thin:@"+host+":"+port+":"+sid; //Set the username to the rowset rowset.setUsername(username); //Set the password to the rowset rowset.setPassword(password); //Set the database url to the rowset rowset.setUrl(url); } finally { // Close the rowset object rowset.close(); } // Return the rowset containing database connection details return (OracleJDBCRowSet)rowset; }

After configuring the RowSet, it can be used to execute a query, say for selecting records from the table. The following code snippet shows how to do this. For more details, please look into the code of AirlinesDML.java file under the src/oracle/otnsamples/jdbc folder.

public List selectRecords(String code,String name, String partner) throws SQLException, IOException{ ..... // Initialize the ArrayList to be returned. ArrayList data = new ArrayList( ); .... // The SQL query for selecting the rows from the otn_airlines table query = "SELECT * FROM otn_airlines " + "WHERE UPPER(code) LIKE UPPER('"+code+"') AND " + "UPPER(name) LIKE UPPER('"+name+"') AND " + "UPPER(partner) LIKE UPPER('"+partner+"') ORDER BY code"; // Get the rowset instance. rowset = RowSetCache.getInstance().getRowSet(); // Set the rowset to execute the query rowset.setCommand (query); // Execute the rowset rowset.execute (); // Retrieve the column values for all rows while (rowset.next ()) { // Get rows and add it to a String Array String[] row = new String[3]; for ( int i = 0; i < 3; i++ ) { row[ i ] = rowset.getString( i + 1 ); } // Add each row to the ArrayList data.add(row); } } finally { // Close the rowset object rowset.close(); } //Return the arraylist containing all the selected records. return data; }

The following code snippet shows how to perform an insert to the database table - otn_airlines. Code for update and delete will be similar to the insert. For more details, please look into the code of AirlinesDML.java file under the src/oracle/otnsamples/jdbc folder.

public void insertRecord( String code,String name,String partner ) throws SQLException, IOException{ String query = null; try { // Create the Insert SQL string query = "INSERT INTO otn_airlines VALUES ('"+code+"','"+name+"','"+ partner+"')"; // Get the rowset instance. rowset = RowSetCache.getInstance().getRowSet(); // Set the rowset to execute the query rowset.setCommand (query); // Execute the rowset rowset.execute(); } finally { // Close the rowset object rowset.close(); } }

Sample Application Files 

This section provides a tabular listing of all the important files used in the application. The directory structure of the deliverable RowSetDMLSample.jar will be as shown below. RowSetDMLSample is the top level directory.

Setting up the Sample Application 

Additional References 

• Oracle RowSet Documentation
• Sun Java RowSet Tutorial
• JDBC WhitePaper
• JDBC Technology Page

We hope you find this README document helpful. For any additional help/information please contact savitha.rajeev@oracle.com.