Oracle Java Application Tutorial - PDF

							                                   Tutorial: JDBC RowSet
This tutorial describes the JDBC RowSet, an implementation type of the javax.sql.RowSet by peering
into the concepts, design, and implementation aspects that OTN developers applied to the JDBC RowSet
DML sample Application. OTN developers used OC4J (Oracle Containers for J2EE) with Oracle JDBC to
develop the sample application.

This tutorial assumes that you are familiar with the JDBC RowSet DML sample Application and have
installed and configured the required software as described in the \doc\Install.html and \doc\Readme.html
files of your sample code download.

Contents


    1.   Concepts



    2.   Design



    3.   Required Software



    4.   Setup



    5.   Application Usage



    6.   Resources



    7.   Feedback




                                              Concepts

In this section we will look at some of the key concepts behind JdbcRowSets. Specifically, we will look at
the following:
JdbcRowSet Defined
Using JdbcRowSet
Advantages of JdbcRowSet


JdbcRowSet Defined
The JdbcRowSet is a connected rowset. The purpose of the JdbcRowSet is to provide a JavaBean type
layer on top of java.sql.ResultSet to make a JDBC driver look like a JavaBeans component. This is
defined in the sun.jdbc.rowset.JdbcRowset class:
                                   public class JdbcRowSet extends BaseRowSet implements
javax.sql.RowSet
In this class specification, unlike the CachedRowSet, a JdbcRowSet has a live connection to the database
and requires the presence of JDBC drivers during manipulation of the row set. However, it is not
serializable, and neither is it cloneable. It is also not movable across JVMs and there is no synchronization
of data to the database. It provides whatever features the underlying java.sql.ResultSet interface
provides, with the added advantage of presenting itself like a JavaBean. Any call made to the JdbcRowSet
is directly hooked on to the JDBC interface whose usage is the same as any other row set implementation.


Using JdbcRowSet
To demonstrate the use of a JdbcRowSet , we will look at a code snippet that sets a connection URL
(java:oracle:oci:@), username (SCOTT), password (TIGER), and a SQL query (SELECT empno,
ename, sal FROM emp) on an emp table, as the connection properties to the row set:



RowSet rowset = new OracleJDBCRowSet ();
rowset.setUrl ("java:oracle:oci:@");
rowset.setUsername ("SCOTT");
rowset.setPassword ("TIGER");
rowset.setCommand (
  "SELECT empno, ename, sal FROM emp");
rowset.execute ();
while (rowset.next ())
{
  System.out.println ("empno: " + rowset.getInt (1));
  System.out.println ("ename: "
   + rowset.getString (2));
  System.out.println ("sal: " + rowset.getInt (3));
}



The SQL query in the above code snippet is executed through the execute() method, and the resultant
rows that are retrieved are subsequently printed.


Advantages of JdbcRowSet

        An instance of JdbcRowSet can simply take calls invoked on it and in turn call them on its
         ResultSet object, because it is always connected to its database. As a result, a resultset can, for
         example, be a component in a Swing application.



        A ResultSet object can be made scrollable and updatable. All RowSet objects are by default
         scrollable and updatable. If the driver and database being used do not support scrolling and/or
         updating of resultsets, an application can populate a JdbcRowSet object with the data of a
         ResultSet object and then operate on the JdbcRowSet object as if it were the ResultSet object.
                                                Design
The JDBC RowSet DML sample application uses a simple database called otn_airlines table. While OTN
developers have kept the application design simple, the main objective of illustrating the usage of JDBC
RowSets in DML operations has been successfully demonstrated.
The JDBC data source that is used in the sample application is an Oracle Database. The RowSet instance
is configured to connect to the database, following which the data from the table otn_airlines is read using
the RowSet. The application retrieves and displays the records from the otn_airlines table and allows for
inserts, updates, and deletes on it as well.
The SQL DML operations SELECT, INSERT, UPDATE and DELETE are implemented in a class called
AirlinesDML, that captures only the business implementation of the otn_airlines table. A singleton class
called RowSetCache (Refer UML diagram below) is used to create a connected RowSet. At any given point
in time, only one instance of the class is made available to the application in its 'run' mode. 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 of the operations using the
available RowSet methods.
The figure below (created with JDeveloper) shows the key class, in the JDBC RowSet DML sample
application:
                                       Required Software
This tutorial presents some code examples. If you want to study them in context, download and install the
JDBC RowSet DML source code. If you also want to build and run the JDBC RowSet DML sample
application, you will need the software listed in the Install file that came with the sample download.
JDeveloper is Oracle's visual Java development tool and can be dowloaded from here.




                                                 Setup

If you use Oracle9i JDeveloper as your IDE, add the J2EE library to your project. For coding details, see the
Application Usage section.
To configure your system to build and run the JDBC RowSet DML sample application, see the Setup
section in the ReadMe file of the JDBC RowSet DML sample application.




                                       Application Usage
The RowSetCache.java file located under src/oracle/otnsamples/jdbc is a singleton class used to
create a connected RowSet. The RowSetCache class contains the loadparams() method that loads the
database details by reading a properties file, called the Connection.properties. The setter methods of
the RowSet are used to set username, password, url, port, and sid of the datasource it should connect to.

When the application is running, only one instance of the RowSetCache singleton class is made available to
it, at any given point in time.
The following code snippet is an extract from the RowSetCache singleton class:




 public class RowSetCache {
 private static RowSetCache rowSetCache = new RowSetCache();
 private RowSet rowset = null;
 private RowSetCache() {
}
 public static RowSetCache getInstance() {
   return rowSetCache;
 }
 private 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;
 }
Within the RowSetCache singleton class, you will also find the loadRowSet() method that loads the
database details to a rowset and returns the same, thereby configuring the Rowset instance to connect to
the database:



        private OracleJDBCRowSet loadRowSet() throws IOException, SQLException {

        // Variables to hold database details
        String username = null;
        String password = null;
        String host = null;
        String sid    = null;
        String port = null;
        String url    = null;
        rowset = new OracleJDBCRowSet();

        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 rowset containing database connection details
        return (OracleJDBCRowSet)rowset;
    }
}


Once the RowSet instance has been configured to connect to the database, records can be selected from
the otn_airlines table using JDBC RowSet, based on the query conditions that have been entered. The
following code snippet extracted from the AirlinesDML.java class demonstrates this:
/** RowSet Object **/
 RowSet rowset = new OracleJDBCRowSet();

.......

// Create the Insert SQL string
query = "INSERT INTO otn_airlines VALUES ('"+code+"','"+name+"','"+
                            partner+"')";
// Set the rowset to execute the query
rowset.setCommand (query);

// Execute the rowset
rowset.execute();

.......
// Close the rowset object
rowset.close();



The following code snippet from AirlinesDML.java shows how to perform an insert to the database
table - otn_airlines:




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




It is worth noting that the code for update and delete operations on the otn_airlines table will be similar to
the insertRecord code snippet above.




                                               Resources
                 This tutorial is part of a series on the different types of RowSet implementations based on the JDBC
                 RowSet DML Sample application. Following are links to resources that can help you understand and apply
                 the concepts and techniques presented in the tutorials. See the Required Software section to obtain the
                 JDBC RowSet DML source code and related files.


Resource                                                                      URL


JDBC API
              http://java.sun.com/products/jdbc/
Website


Oracle
              http://otn.oracle.com/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm
JDBC FAQ


OTN JDBC
How-To
              http://otn.oracle.com/sample_code/tech/java/codesnippet/jdbc/index.html
Document
s


OTN
Sample        http://otn.oracle.com/sample_code/content.html
Code

Oracle
JDBC
              http://otn.oracle.com/software/tech/java/sqlj_jdbc/htdocs/winsoft.html
Drivers for
Download

Oracle
JDBC
              http://otn.oracle.com/tech/java/sqlj_jdbc/index.html
Technolog
y Page

JDBC
              http://otn.oracle.com/tech/java/sqlj_jdbc/pdf/twp_appdev_jdbc_best_of_breed_drivers_4_j2ee_webservices_grid__10gr1_1223.p
Whitepape
              df
r




                                                                 Feedback

                 If you have questions or comments about this tutorial, you can:

                          Post a message in the OTN Sample Code discussion forum. OTN developers and other experts
                           monitor the forum.



                          Send email to the author. mailto:Dilip.Thomas@oracle.com

                 If you have suggestions or ideas for future tutorials, you can:

                          Post a message in the OTN Member Feedback forum.
   Send email to mailto:Tom.Haunert@oracle.com