Jdbc (RowSet offline result set)

Jdbc (RowSet offline result set)

ResultSet is a class for beginners who use Jdbc programming and commonly used database operations. It is easy to use the RowSet interface since JDK 1.4. The RowSet interface extends the standard java. SQL. ResultSet interface. The RowSetMetaData interface extends the java. SQL. ResultSetMetaData interface. Therefore, developers familiar with JDBC APIs must learn a few new APIs before using rowset. In addition, third-party software tools supporting JDBC ResultSet objects can also be conveniently used for rowset. However, in JDK 1.4, there is only one RowSet interface, which causes the RowSet usage range to be discounted. However, JDK 5.0 defines five standard JDBC RowSet interfaces and provides reference implementations. Therefore, you can easily use the functions provided by the RowSet interface.

The RowSet object can establish a connection with the data source and maintain the connection throughout its lifecycle. In this case, the object is called the connected rowset. Rowset can also establish a connection with the data source, obtain data from it, and then close it. This type of rowset is called a non-connected rowset. A non-connected rowset can change its data when it is disconnected, and then send the changes back to the original data source, but it must establish a new connection to complete this operation. Compared with java. SQL. in terms of ResultSet, the offline operations of RowSet can effectively take advantage of the increasingly adequate memory of the computer, reducing the burden on the database server. Because data operations are performed in the memory and then submitted to the data source in batch, flexibility and performance have been greatly improved. By default, RowSet is a scrollable, updatable, and serializable result set. It acts as a JavaBeans and can be easily transmitted between networks for data synchronization at both ends.
1. Comparison with ResultSet
(1) RowSet extends the ResultSet interface, so you can use RowSet like ResultSet.
(2) RowSet extends the ResultSet interface, so it has more functions than ResultSet.
(3) by default, all RowSet objects are scrollable and updatable. The ResultSet can only scroll forward and read-only.
(4) RowSet can be non-linked, while ResultSet is connected. Therefore, the CacheRowSet interface can be used to operate data offline.
(5) added jdbc api support for the JavaBeans component model for the RowSet interface. Rowset can be used as a JavaBeans component in the visual Bean development environment.
(6) RowSet adopts the new method of connecting to the database.
(7) CacheRowSet can be serialized.
(8) both RowSet and ResultSet represent the data, attributes, and related operation methods of a row.
(9) In my opinion, we should tend to regard RowSet as something unrelated to the database. It is just an object that represents a row of data, and ResultSet is something closely related to the database.
2. Five standard RowSet interfaces of JDK 5.0
In JDK 5.0, five standard RowSet interfaces include CachedRowSet, WebRowSet, FilteredRowSet, JoinRowSet, and JdbcRowSet. The corresponding reference implementation is provided by Sun, which is located in the com. sun. rowset package, including CachedRowSetImpl, WebRowSetImpl, FilteredRowSetImpl, JoinRowSetImpl, and JdbcRowSetImpl. In the five standard interfaces, JdbcRowSet is the linked rowset, while the other four are non-linked rowsets.
(1) CachedRowSet: The most common RowSet. The other three rowsets (WebRowSet, FilteredRowSet, and JoinRowSet) are directly or indirectly inherited from and extended. It provides offline operations on the database. It can read data to the memory for addition, deletion, modification, and query, and then synchronize the data to the data source. CachedRowSet is rolling, updatable, and serializable. It can be transmitted across networks as a JavaBeans. Supports event listening, paging, and other features. The CachedRowSet Object usually contains multiple rows from the result set, but can also contain any rows from table files (such as workbooks.
(2) WebRowSet: inherits from CachedRowSet and can write WebRowSet to an XML file. You can also use a compliant XML file to fill WebRowSet.
(3) FilteredRowSet: provides data filtering by setting Predicate (in the javax. SQL. rowset package. You can filter and filter data in a RowSet based on different conditions.
(4) JoinRowSet: provides SQL JOIN-like functions to combine data in different rowsets. Currently, only Inner Join is supported in Java 6 ).
(5) JdbcRowSet: an encapsulation of the ResultSet so that it can be used as a JavaBeans. It is the only RowSet that maintains the database connection. The JdbcRowSet object is the connected RowSet object, that is, it must use a JDBC-enabled Driver ("JDBC driver") to maintain its connection to the data source.

3. Fill in RowSet
As mentioned above, we should tend to regard RowSet as an object that is irrelevant to the database and only represents a row of data. Therefore, it involves the problem of where the data comes from.
(1) directly retrieve data from the database
In most cases, dealing with data is also dealing with the database. Therefore, the RowSet interface provides a method to directly obtain data from the database through JDBC. For example, the following describes how to implement JdbcRowSetImpl:
[Java]
RowSet rs = new JdbcRowSetImpl (); // It can also be CachedRowSetImpl, WebRowSetImpl, FilteredRowSetImpl, and JoinRowSetImpl.
Rs. setUrl ("jdbc: mysql: // test ");
Rs. setUsername ("root ");
Rs. setPassword ("");
Rs. setCommand ("SELECT * from employees ");
Rs.exe cute ();
After the execute () method is run, the data in the EMPLOYEES table is filled in the rs object.
In addition to setting the JDBC connection URL, user name, and password, RowSet can also use the value of the data source name attribute to find the DataSource object that has been registered in the Naming Service. After retrieval, you can use the DataSource object to create a connection to the data source it represents. You can use the setDataSourceName () method to set the data source name.
(2) Use ResultSet to fill
If you want to use a ready-made ResultSet as a RowSet, or if the DBMS does not provide full support for rolling and updating, to make non-rolling and read-only ResultSet objects scrollable and updatable, you can create a CachedRowSet object filled with the data of the ResultSet object.

[Java]
ResultSet rs = stmt.exe cuteQuery ("SELECT * from employees ");
CachedRowSet crs = new CachedRowSetImpl (); // It can also be WebRowSetImpl, FilteredRowSetImpl, and JoinRowSetImpl, because they all inherit from CachedRowSetImpl
Crs. populate (rs );
After running the populate () method, the data in the rs of the ResultSet object is filled into the crs object.

(3) Filling in XML
If you want to use XML as the data exchange format to transmit data between the client and your server, and edit the data offline or use the XML format, you can use the WebRowSet interface to fill data with XML.
[Java]
WebRowSet wrs = new WebRowSetImpl ();
Wrs. readXml (new FileReader (new File ("D: \ employees. xml ")));
After the readXml () method is run, the data in the employees. xml file is filled in the wrs object. For the format of the employees. xml file, see the appendix.

(4) Fill in with other methods
If the shape is filled in other formats or methods, such as csv, excel, text, and http, you need to write your own code to implement RowSet.

4. Operate data and metadata in RowSet
Except for the operation data and metadata methods provided by ResultSet, The RowSet interface does not provide many additional methods.
1) update data
Rs. absolute (5 );
Rs. updateInt (1, 10 );
Rs. updateInt (2, 1000 );
Rs. updateString (3, "John ");
Rs. updateRow ();

(2) Insert data
Rs. moveToInsertRow ();
Rs. updateInt (1, 10 );
Rs. updateInt (2, 1000 );
Rs. updateString (3, "John ");
Rs. insertRow ();

(3) Delete data
Rs. absolute (5 );
Rs. deleteRow ();

(4) set attributes
Rs. setCommand ("select id, salary, name from employees where id =? ");
Rs. setInt (1, 1 );
Rs.exe cute ();

(5) Metadata
RowSetMetaData rsmd = (RowSetMetaData) rs. getMetaData ();
Int count = rsmd. getColumnCount ();
Int type = rsmd. getColumnType (2 );

5. Transactions and updates of underlying data sources
RowSet itself only represents specific data. Updates to transactions and underlying data sources are closely related to underlying data sources. For JDBC data sources, the corresponding standard interface JdbcRowSet is implemented through database-related methods, such as commit () and rollback. For non-connected rowset of the standard interface, such as CachedRowSet, after the data in the RowSet is changed, the writer of the RowSet object is called internally to write the changes to the data source by running the acceptChanges () method, then, the changes in the CachedRowSet object are propagated back to the underlying data source.
6. serializable non-connected RowSet
One of the main reasons for using the CachedRowSet object is to transfer data between different components of the application. Because the CachedRowSet object is serializable, you can use it (for example,) to send the query results of the Enterprise JavaBeans component running in the server environment to the client running on the web browser through the network.
Because the CachedRowSet object is not connected, it is more concise than the ResultSet object with the same data. Therefore, it is especially suitable for sending data to thin clients (such as PDAs), which are not suitable for JDBC drivers due to resource restrictions or security considerations. Therefore, the CachedRowSet object provides a way to "retrieve rows" without implementing all JDBC APIs.
EbRowSet inherits from CachedRowSet. In addition to the advantages of CachedRowSet, ebRowSet can also output WebRowSet into XML or convert XML into WebRowSet, which is more suitable for Web environments. The standard WebRowSet XML schema definition is located at URIhttp: // java.sun.com/xml/ns/jdbc/webrowset.xsd. The code example for saving WebRowSet as XML is as follows:

[Java]
Wrs. setCommand ("select id, salary, name from employees ");
Wrs.exe cute ();
Wrs. writeXml (new FileWriter (new File ("D: \ employees. xml ")));

Appendix: employees. xml
[Html]
<? Xml version = "1.0"?>
<WebRowSet xmlns = "http://java.sun.com/xml/ns/jdbc" xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance"
Xsi: schemaLocation = "http://java.sun.com/xml/ns/jdbc http://java.sun.com/xml/ns/jdbc/webrowset.xsd">
<Properties>
<Command> select id, salary, name from employees </command>
<Concurrency> 1008 </concurrency>
<Datasource> <null/> </datasource>
<Escape-processing> true </escape-processing>
<Fetch-direction> 1000 </fetch-direction>
<Fetch-size> 0 </fetch-size>
<Isolation-level> 2 </isolation-level>
<Key-columns>
</Key-columns>
<Map>
</Map>
<Max-field-size> 0 </max-field-size>
<Max-rows> 0 </max-rows>
<Query-timeout> 0 </query-timeout>
<Read-only> true </read-only>
<Rowset-type> ResultSet. TYPE_SCROLL_INSENSITIVE </rowset-type>
<Show-deleted> false </show-deleted>
<Table-name> employees </table-name>
<Url> jdbc: mysql: // test </url>
<Sync-provider>
<Sync-provider-name> com. sun. rowset. providers. RIOptimisticProvider </sync-provider-name>
<Sync-provider-vendor> Sun Microsystems Inc. </sync-provider-vendor>
<Sync-provider-version> 1.0 </sync-provider-version>
<Sync-provider-grade> 2 </sync-provider-grade>
<Data-source-lock> 1 </data-source-lock>
</Sync-provider>
</Properties>
<Metadata>
<Column-count> 3 </column-count>
<Column-definition>
<Column-index> 1 </column-index>
<Auto-increment> false </auto-increment>
<Case-sensitive> false </case-sensitive>
<Currency> false </currency>
<Nullable> 0 </nullable>
<Signed> true </signed>
<Searchable> true </searchable>
<Column-display-size> 11 </column-display-size>
<Column-label> id </column-label>
<Column-name> id </column-name>
<Schema-name> </schema-name>
<Column-precision> 11 </column-precision>
<Column-scale> 0 </column-scale>
<Table-name> employees </table-name>
<Catalog-name> test </catalog-name>
<Column-type> 4 </column-type>
<Column-type-name> INT </column-type-name>
</Column-definition>
<Column-definition>
<Column-index> 2 </column-index>
<Auto-increment> false </auto-increment>
<Case-sensitive> false </case-sensitive>
<Currency> false </currency>
<Nullable> 1 </nullable>
<Signed> true </signed>
<Searchable> true </searchable>
<Column-display-size> 11 </column-display-size>
<Column-label> salary </column-label>
<Column-name> salary </column-name>
<Schema-name> </schema-name>
<Column-precision> 11 </column-precision>
<Column-scale> 0 </column-scale>
<Table-name> employees </table-name>
<Catalog-name> test </catalog-name>
<Column-type> 4 </column-type>
<Column-type-name> INT </column-type-name>
</Column-definition>
<Column-definition>
<Column-index> 3 </column-index>
<Auto-increment> false </auto-increment>
<Case-sensitive> false </case-sensitive>
<Currency> false </currency>
<Nullable> 1 </nullable>
<Signed> false </signed>
<Searchable> true </searchable>
<Column-display-size> 25 </column-display-size>
<Column-label> name </column-label>
<Column-name> name </column-name>
<Schema-name> </schema-name>
<Column-precision> 25 </column-precision>
<Column-scale> 0 </column-scale>
<Table-name> employees </table-name>
<Catalog-name> test </catalog-name>
<Column-type> 12 </column-type>
<Column-type-name> VARCHAR </column-type-name>
</Column-definition>
</Metadata>
<Data>
<CurrentRow>
<ColumnValue> 1 </columnValue>
<ColumnValue> 1000 </columnValue>
<ColumnValue> John </columnValue>
</CurrentRow>
<CurrentRow>
<ColumnValue> 2 </columnValue>
<ColumnValue> 1200 </columnValue>
<ColumnValue> Tom </columnValue>
</CurrentRow>
</Data>
</WebRowSet>