Overview of JDBC RowSets
A RowSet is an object that encapsulates a set of rows from either java Database Connectivity (JDBC) result sets or tabular data sources. RowSets support component-based development models like JavaBeans, with a standard set of properties and an event notification mechanism.
RowSets were introduced in JDBC 2.0 through the optional packages. However, the implementation of RowSets was standardized in the JDBC RowSet Implementations Specification (JSR-114), which is available as non-optional package since Java Platform, Standard Edition (Java SE) 5.0. Java SE 6.0 RowSets contain more APIs supporting features like RowId, National Language Charactersets, and so on. The Java SE Javadocs provide information about the standard interfaces and base classes for JDBC RowSet implementations.
five types of RowSet:
- CachedRowSet
- JdbcRowSet
- WebRowSet
- FilteredRowSet
- JoinRowSet
for example, ojdbc5.jar and ojdbc6.jar contain oracle.jdbc.rowsetpackage.
To use the Oracle RowSet implementations, you need to import either the entire oracle.jdbc.rowset package or specific classes and interfaces from the package for the required RowSet type. For client-side usage, you also need to include the standard Oracle JAR files like ojdbc5.jar or ojdbc6.jar in theCLASSPATH environment variable.
This section covers the following topics:
- RowSet Properties
- Events and Event Listeners
- Command Parameters and Command Execution
- Traversing RowSets
The javax.sql.RowSet interface provides a set of JavaBeans properties that can be altered to access the data in the data source through a single interface. Example of properties are connection string, user name, password, type of connection, and the query string.
The interface provides standard accessor methods for setting and retrieving the property values. The following code illustrates setting some of the RowSetproperties:
...
rowset.setUrl("jdbc:oracle:oci:@");
rowset.setUsername("SCOTT");
rowset.setPassword("TIGER");
rowset.setCommand("SELECT empno, ename, sal FROM emp");
...
In this example, the URL, user name, password, and SQL query are set as the RowSet properties to retrieve the employee number, employee name, and salary of all the employees into the RowSet object.
Events and Event Listeners
RowSets support JavaBeans events. The following types of events are supported by the RowSet interface:
- cursorMoved : This event is generated whenever there is a cursor movement. For example, when the next or previous method is called.
- rowChanged : This event is generated when a row is inserted, updated, or deleted from the RowSet.
- rowSetChanged : This event is generated when the whole RowSet is created or changed. For example, when the execute method is called.
Command Parameters and Command Execution
The command property of a RowSet object typically represents a SQL query string, which when processed would populate the RowSet object with actual data. Like in regular JDBC processing, this query string can take input or bind parameters. The javax.sql.RowSet interface also provides methods for setting input parameters to this SQL query. After the required input parameters are set, the SQL query can be processed to populate the RowSet object with data from the underlying data source.
The following code illustrates this simple sequence:
...
rowset.setCommand("SELECT ename, sal FROM emp WHERE empno = ?");
// setting the employee number input parameter for employee named "KING"
rowset.setInt(1, 7839);
rowset.execute();
...
In the preceding example, the employee number 7839 is set as the input or bind parameter for the SQL query specified in the command property of the RowSetobject. When the SQL query is processed, the RowSet object is filled with the employee name and salary information of the employee whose employee number is 7839.
Traversing RowSets
The javax.sql.RowSet interface extends the java.sql.ResultSet interface.
The RowSet interface, therefore, provides cursor movement and positioning methods, which are inherited from the ResultSet interface, for traversing through data in a RowSet object. Some of the inherited methods are absolute,beforeFirst, afterLast, next, and previous.
The RowSet interface can be used just like a ResultSet interface for retrieving and updating data. The RowSet interface provides an optional way to implement a scrollable and updatable result set. All the fields and methods provided by the ResultSet interface are implemented in RowSet.
CachedRowSet
A CachedRowSet is a RowSet in which the rows are cached and the RowSet is disconnected, that is, it does not maintain an active connection to the database.
The oracle.jdbc.rowset.OracleCachedRowSet class is the Oracle implementation of CachedRowSet.It can interoperate with the reference implementation of Sun Microsystems.
The OracleCachedRowSet class in the ojdbc5.jar and ojdbc6.jar files implements the standard JSR-114 interfacejavax.sql.rowset.CachedRowSet.
JdbcRowSet
A JdbcRowSet is a RowSet that wraps around a ResultSet object. It is a connected RowSet that provides JDBC interfaces in the form of a JavaBean interface.The Oracle implementation of JdbcRowSet is oracle.jdbc.rowset.OracleJDBCRowSet. The OracleJDBCRowSet class in ojdbc5.jar and ojdbc6.jarimplements the standard JSR-114 interface javax.sql.rowset.JdbcRowSet.#
Below Table shows how the JdbcRowSet interface differs from CachedRowSet interface.
Table 18-1 The JDBC and Cached Row Sets Compared
The command property of a RowSet object typically represents a SQL query string, which when processed would populate the RowSet object with actual data. Like in regular JDBC processing, this query string can take input or bind parameters. The javax.sql.RowSet interface also provides methods for setting input parameters to this SQL query. After the required input parameters are set, the SQL query can be processed to populate the RowSet object with data from the underlying data source.
The following code illustrates this simple sequence:
...
rowset.setCommand("SELECT ename, sal FROM emp WHERE empno = ?");
// setting the employee number input parameter for employee named "KING"
rowset.setInt(1, 7839);
rowset.execute();
...
In the preceding example, the employee number 7839 is set as the input or bind parameter for the SQL query specified in the command property of the RowSetobject. When the SQL query is processed, the RowSet object is filled with the employee name and salary information of the employee whose employee number is 7839.
Traversing RowSets
The javax.sql.RowSet interface extends the java.sql.ResultSet interface.
The RowSet interface, therefore, provides cursor movement and positioning methods, which are inherited from the ResultSet interface, for traversing through data in a RowSet object. Some of the inherited methods are absolute,beforeFirst, afterLast, next, and previous.
The RowSet interface can be used just like a ResultSet interface for retrieving and updating data. The RowSet interface provides an optional way to implement a scrollable and updatable result set. All the fields and methods provided by the ResultSet interface are implemented in RowSet.
CachedRowSet
A CachedRowSet is a RowSet in which the rows are cached and the RowSet is disconnected, that is, it does not maintain an active connection to the database.
The oracle.jdbc.rowset.OracleCachedRowSet class is the Oracle implementation of CachedRowSet.It can interoperate with the reference implementation of Sun Microsystems.
The OracleCachedRowSet class in the ojdbc5.jar and ojdbc6.jar files implements the standard JSR-114 interfacejavax.sql.rowset.CachedRowSet.
JdbcRowSet
A JdbcRowSet is a RowSet that wraps around a ResultSet object. It is a connected RowSet that provides JDBC interfaces in the form of a JavaBean interface.The Oracle implementation of JdbcRowSet is oracle.jdbc.rowset.OracleJDBCRowSet. The OracleJDBCRowSet class in ojdbc5.jar and ojdbc6.jarimplements the standard JSR-114 interface javax.sql.rowset.JdbcRowSet.#
Below Table shows how the JdbcRowSet interface differs from CachedRowSet interface.
Table 18-1 The JDBC and Cached Row Sets Compared
RowSet Type
|
Serializable
|
Connected to Database
|
Movable Across JVMs
|
Synchronization of data to database
|
Presence of JDBC Drivers
|
JDBC
|
Yes
|
Yes
|
No
|
No
|
Yes
|
Cached
|
Yes
|
No
|
Yes
|
Yes
|
No
|
JdbcRowSet is a connected RowSet, which has a live connection to the database and all the calls on the JdbcRowSet are percolated to the mapping call in the JDBC connection, statement, or result set. A CachedRowSet does not have any connection to the database open.
JdbcRowSet requires the presence of JDBC drivers unlike a CachedRowSet, which does not require JDBC drivers during manipulation. However, both JdbcRowSet and CachedRowSet require JDBC drivers during population of the RowSet and while committing the changes of the RowSet.
The following code illustrates how a JdbcRowSet is used:
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));
}
...
In the preceding example, the connection URL, user name, password, and SQL query are set as properties of the RowSet object, the SQL query is processed using the execute method,and the rows are retrieved and printed by traversing through the data populated in the RowSet object.
No comments:
Post a Comment