Oracle9i JDBC Developer's Guide and Reference Release 1 (9.0.1) Part Number A90211-01 |
|
This appendix describes the following topics:
A row set 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.
Three kinds of row set are supported by JavaSoft:
All the row set implementation is in the oracle.jdbc.rowset
package. Web row set is a semi-connected row set. It has a servlet which has a connection open and the WebRowSet
interface translates the user calls to appropriate request to the servlet over HTTP. This is targeted at Thin clients which have only HTTP implementation in them.
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. The following code executes a simple query:
... rowset.setUrl ("jdbc:oracle:oci8:@"); rowset.setUsername ("SCOTT"); rowset.setPassword ("TIGER"); rowset.setCommand ( "SELECT empno, ename, sal FROM emp WHERE empno = ?"); // empno of employee name "KING" rowset.setInt (1, 7839); ...
In the above example, the URL, user name, password, SQL query, and bind parameter required for the query are set as the command properties to retrieve the employee name and salary. Also, the row set would contain empno
, ename
, and sal
for the employee with the empno
as 7839
and whose name is KING
.
The classes for the row set feature are found in a separate archive, ocrs12.zip.
This file is located in the $ORACLE_HOME/jdbc
directory. To use row set, you need to include this archive in your CLASSPATH
.
For Unix (sh), the command is:
CLASSPATH=$CLASSPATH:$ORACLE_HOME/jdbc/lib/ocrs12.zip export CLASSPATH
For Windows 2000/NT/98/95, the command is:
set CLASSPATH=%CLASSPATH%;%ORACLE_HOME%\jdbc\lib\ocrs12.zip
This might also be set in the project properties in case you are using an IDE like Jdeveloper.
Oracle row set interfaces are implemented in the oracle.jdbc.rowset
package. Import this package to use any of the Oracle row set implementations.
The OracleCachedRowSet
and OracleJDBCRowSet
classes implement the javax.sql.RowSet
interface, which extends java.sql.ResultSet
. Row set not only provides the interfaces of result set, but also some of the properties of the java.sql.Connection
and java.sql.PreparedStatement
interfaces. Connections and prepared statements are totally abstracted by this interface. CachedRowSet
is serializable. This class implements the java.io.Serializable
interface. This enables the OracleCachedRowSet
class to be moved over the network or other JVM sessions.
Also available is the oracle.jdbc.rowset.OracleRowSetListenerAdapter
class.
Typically, static properties for the applications can be set for a row set at the development time and the rest of the properties which are dynamic (are dependent on runtime) can be set at the runtime. The static properties may include the connection URL, username, password, connection type, concurrency type of the row set, or the query itself. The runtime properties, like the bind parameters for the query, could be bound at runtime. Scenarios where the query itself is a dynamic property is also common.
The row set feature supports multiple listeners to be registered with the RowSet
object. Listeners can be registered using the addRowSetListener()
method and unregistered through the removeRowSetListener()
method. A listener should implement the javax.sql.RowSetListener
interface to register itself as the row set listener. Three types of events are supported by the RowSet
interface:
cursorMoved
event : Generated whenever there is a cursor movement, which occurs when the next()
or previous()
methods are called
rowChanged
event : Generated when a new row is inserted, updated, or deleted from the row set
rowsetChanged
event : Generated when the whole row set is created or changed
The following code shows the registration of a row set listener:
MyRowSetListener rowsetListener = new MyRowSetListener (); // adding a rowset listener. rowset.addRowSetListener (rowsetListener); // implementation of a rowset listener public class MyRowSetListener implements RowSetListener { public void cursorMoved(RowSetEvent event) { // action on cursor movement } public void rowChanged(RowSetEvent event) { // action on change of row } public void rowSetChanged(RowSetEvent event) { // action on changing of rowset } }// end of class MyRowSetListener
Applications which handle only a few events can implement only the required events by using the OracleRowSetAdapter
class, which is an abstract class with empty implementation for all the event handling methods.
In the following code, only the rowSetChanged
event is handled. The remaining events are not handled by the application.
rowset.addRowSetListener (new OracleRowSetAdapter () { public void rowSetChanged(RowSetEvent event) { // your action for rowsetChanged } } );
Various methods to traverse through the rows are provided by the RowSet
interface. These properties are inherited directly from the java.sql.ResultSet
interface. The RowSet
interface could be used as a ResultSet
interface for retrieval and updating of data. The RowSet
interface provides an optional way to implement a scrolling and updatable result set if they are not provided by the result set implementation.
A cached row set is a row set implementation where the rows are cached and the row set does not have a live connection to the database (disconnected) and it is a serializable object, which provides the standard interface as of the javax.sql.RowSet
interface. OracleCachedRowSet
is the implementation of CachedRowSet
by Oracle, and the OracleCachedRowSet
is used interchangeably with CachedRowSet
.
In the following code, an OracleCachedRowSet
object is created and the connection URL, username, password, and the SQL query for the row set is set as properties. The RowSet
object is populated through the execute
method. After the execute
call, the RowSet
object can be used as a java.sql.ResultSet
object to retrieve, scroll, insert, delete, or update data.
... RowSet rowset = new OracleCachedRowSet (); rowset.setUrl ("jdbc:oracle:oci8:@"); 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)); } ...
To populate a CachedRowSet
object with a query, complete the following steps:
OracleCachedRowSet
.
Url
, Username
, Password
, connection type (optional), and the query string as properties for the RowSet
object.
execute()
method to populate the RowSet
object.
CachedRowSet
can be populated with the existing ResultSet
object, using the populate()
method, as shown in the following code:
// Executing a query to get the ResultSet object. ResultSet rset = pstmt.executeQuery (); OracleCachedRowSet rowset = new OracleCachedRowSet (); // the obtained ResultSet object is passed to the // populate method to populate the data in the // rowset object. rowset.populate (rset);
In the above example, a ResultSet
object is obtained by executing a query and the retrieved ResultSet
object is passed to the populate()
method of the cached row set to populate the contents of the result set into cached row set.
To populate a CachedRowSet
object with an already available result set, complete the following steps:
OracleCachedRowSet
.
ResultSet
object to the populate()
method to populate the RowSet
object.
All the interfaces provided by the ResultSet
interface are implemented in RowSet
. The following code shows how to scroll through a row set:
/** * Scrolling forward, and printing the empno in * the order in which it was fetched. */ // going to the first row of the rowset rowset.beforeFirst (); while (rowset.next ()) System.out.println ("empno: " +rowset.getInt (1));
In the example above, the cursor position is initialized to the position before the first row of the row set by the beforeFirst()
method. The rows are retrieved in forward direction using the next()
method.
/** * Scrolling backward, and printing the empno in * the reverse order as it was fetched. */ //going to the last row of the rowset rowset.afterLast (); while (rowset.previous ()) System.out.println ("empno: " +rowset.getInt (1));
In the above example, the cursor position is initialized to the position after the last row of the RowSet
. The rows are retrieved in reverse direction using the previous()
method of RowSet
.
Inserting, updating, and deleting rows are supported by the row set feature as they are in the result set feature. The following code illustrates the insertion of a row at the fifth position of a row set:
/** * Inserting a row in the 5th position of the rowset. */ // moving the cursor to the 5th position in the rowset if (rowset.absolute(5)) { rowset.moveToInsertRow (); rowset.updateInt (1, 193); rowset.updateString (2, "Ashok"); rowset.updateInt (3, 7200); // inserting a row in the rowset rowset.insertRow (); // Synchronizing the data in RowSet with that in the // database. rowset.acceptChanges (); }
In the above example, a call to the absolute()
method with a parameter 5
takes the cursor to the fifth position of the row set
and a call to the moveToInsertRow()
method creates a place for the insertion of a new row into the row set. The updateXXX()
methods are used to update the newly created row. When all the columns of the row are updated, the insertRow()
is called to update the row set. The changes are committed through acceptChanges()
method.
The following code shows how an OracleCachedRowSet
object is serialized to a file and then retrieved:
// writing the serialized OracleCachedRowSet object { FileOutputStream fileOutputStream = new FileOutputStream ("emp_tab.dmp"); ObjectOutputStream ostream = new ObjectOutputStream (fileOutputStream); ostream.writeObject (rowset); ostream.close (); fileOutputStream.close (); } // reading the serialized OracleCachedRowSet object { FileInputStream fileInputStream = new FileInputStream ("emp_tab.dmp"); ObjectInputStream istream = new ObjectInputStream (fileInputStream); RowSet rowset1 = (RowSet) istream.readObject (); istream.close (); fileInputStream.close (); }
In the above example, a FileOutputStream
object is opened for a emp_tab.dmp
file, and the populated OracleCachedRowSet
object is written to the file using ObjectOutputStream
. This is retrieved using FileInputStream
and the ObjectInputStream
objects.
OracleCachedRowSet
takes care of the serialization of non-serializable form of data like InputStream
, OutputStream
, BLOBS and CLOBS. OracleCachedRowSets
also implements meta data of its own, which could be obtained without any extra server roundtrip. The following code shows how you can obtain meta data for the row set:
ResultSetMetaData metaData = rowset.getMetaData (); int maxCol = metaData.getColumnCount (); for (int i = 1; i <= maxCol; ++i) System.out.println ("Column (" + i +") " +metaData.getColumnName (i));
The above example illustrates how to retrieve a ResultSetMetaData
object and print the column names in the RowSet
.
Since the OracleCachedRowSet
class is serializable, it can be passed across a network or between JVMs, as done in Remote Method Invocation (RMI). Once the OracleCachedRowSet
class is populated, it can move around any JVM, or any environment which does not have JDBC drivers. Committing the data in the row set (through the acceptChanges()
method) requires the presence of JDBC drivers.
The complete process of retrieving the data and populating it in the OracleCachedRowSet
class is performed on the server and the populated row set is passed on to the client using suitable architectures like RMI or Enterprise Java Beans (EJB). The client would be able to perform all the operations like retrieving, scrolling, inserting, updating, and deleting on the row set without any connection to the database. Whenever data is committed to the database, the acceptChanges()
method is called which synchronizes the data in the row set to that in the database. This method makes use of JDBC drivers which require the JVM environment to contain JDBC implementation. This architecture would be suitable for systems involving a Thin client like a Personal Digital Assistant (PDA) or a Network Computer (NC).
After populating the CachedRowSet
object, it can be used as a ResultSet
object or any other object which can be passed over the network using RMI or any other suitable architecture.
Some of the other key-features of cached row set are the following:
All the constraints which apply to updatable result set are applicable here, except serialization, since OracleCachedRowSet
is serializable. The SQL query has the following constraints:
In addition, a SQL query should also satisfy the conditions below if inserts are to be performed:
Properties which apply to the connection cannot be set after populating the row set since the properties cannot be applied to the connection after retrieving the data from the same like, transaction isolation and concurrency mode of the result set.
A JDBC row set is another row set implementation. It is a simple, non-serializable connected row set which provides JDBC interfaces in the form of a Bean interface. Any call to JDBCRowSet
percolates directly to the JDBC interface. The usage of the JDBC interface is the same as any other row set implementation.
Table A-1 shows how the JDBCRowSet
interface differs from CachedRowSet
interface.
RowSet Type | Serializable | Connected to Database | Movable Across JVMs | Synchronization of data to database | Presence of JDBC Drivers |
---|---|---|---|---|---|
JDBC |
No |
Yes |
No |
No |
Yes |
Cached |
Yes |
No |
Yes |
Yes |
No |
The JDBC row set is a connected row set which has a live connection to the database and all the calls on the JDBC row set are percolated to the mapping call in JDBC connection, statement, or result set. A cached row set does not have any connection to the database open.
JDBC row set requires the presence of JDBC drivers where a cached row set does not require JDBC drivers during manipulation, but during population of the row set and the committing the changes of the row set.
The following code shows how a JDBC row set is used:
RowSet rowset = new OracleJDBCRowSet (); rowset.setUrl ("java:oracle:oci8:@"); 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)); }
In the above example, the connection URL, username, password, and the SQL query is set as the connection properties to the row set and the query is executed through the execute()
method and the rows are retrieved and printed.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|