Currently, the main problem in accessing a database through a network (physically connected or wireless) is that the connection to the database cannot be guaranteed at any time. Due to the limitations of other resources, even if reliable database connections are available, there is a problem of how to reasonably use these connections. Until recently, the only way to solve these problems is that the Java experts who need to solve this problem develop a buffer layer by themselves. This article introduces a new JDBC 2.0 class, which can simplify database access code, reduce the number of connections using JDBC, and provide results set scrolling that cannot be provided by the JDBC 1.0 result set. This class can be obtained from earlier database access versions of sun, which consists of three JDBC rowset interfaces. Here we will only introduce one of the -- cachedrowset -- used as a data model for simple JSP applications (that is, when Enterprise JavaBeans are used for minor purposes. You can regard cachedrowset as a disconnected result set ). It is an early implementation of javax. SQL. rowset. Because javax. SQL. rowset is an extension of the Java. SQL. resultset interface, you may be familiar with most of its methods. Rowset supports JDBC APIs from the perspective of the JavaBeans Component Object Model. It provides all the methods and features of the standard JDBC 2.0 resultset, but does not require continuous use of database connections. Like other JavaBean, the implementation of javax. SQL. rowset can also be serialized. In this way, the resultsets can be serialized and then sent to the remote client. After the update operation is performed, the resultsets can be sent back to the server. Cachedrowset has been released for some time, because many web developers are familiar with Microsoft ActiveX Data Objects (ADO) and other updatable result sets. Many entry/update forms need to provide the ability to scroll backward and forward in a record set. Ideally, the user only updates the result set in the buffer state, and then stores all the UPDATE results in the database through the storage command. Although the JDBC 2.0 resultset interface supports this type of scrolling, each session needs to maintain an open connection to the database when browsing the records. To maximize the use of database resources, you should use the database connection when absolutely needed. If you do not need a database connection, release it back to the connection pool as soon as possible. Cachedrowset provides this scalability. It only requires a database connection when performing queries and updates. I expect that the third-party implementation of javax. SQL. rowset will soon appear, but at the same time, cachedrowset is useful for developers who want to familiarize themselves with the javax. SQL. rowset interface of the new J2EE platform. You can download the implementation of cachedrowset from the Java Developer Connection (http://developer.java.sun.com/developer/earlyAccess/crs. Download and decompress the installation file, and put the "rowset. Jar" file under your class directory. Cachedrowset is in the sun. JDBC. rowset package. Create a cachedrowset Cachedrowset is actually a JavaBean. It supports the attributes that allow itself to connect to a database and obtain data. The following table describes some attributes of cachedrowset that need to be initialized without a database connection in advance: Attribute description Username database username Password Database User Password URL database jdbc url such as: JDBC: ODBC: mydsn Command SQL query statement Cachedrowset database connection attributes Because it is a JavaBean object, you can simply use the saved constructor when creating a new cachedrowset object instance:
Once this instance has been created, you can call its property setting method to initialize the bean. The fact that cachedrowset is a bean makes it very convenient to use it in JSP. To create a cachedrowset instance in JSP, use the standard JSP usebean tag. The usebean tag creates a new instance, which is placed in the corresponding session as a session value and serves as a script variable for JSP to process. You can initialize cachedrowset in the usebean label in many ways. Some of these methods depend on whether your application server has the connection pool function, while others only require the JDBC 1.0 drive. Listing 1 illustrates the most explicit method for creating and initializing cachedrowset on the JSP page: Listing 1: simple and clear cachedrowset Initialization Method
When the above method is used to complete the creation, cachedrowset will obtain its own database connection as needed. The Java code script (scriptlet) between the start and end tags of usebean is executed only once when usebean places the bean in the session for the first time. Since cachedrowset is already in the session, the usebean label and the content contained in it will be skipped during subsequent page access. For JSP containers that support J2EE connection pools, you can use the datasourcename attribute to replace the settings of the username, password, and URL attributes. If CENAME is set, cachedrowset tries to obtain a connection from JNDI instead of creating a separate connection. In this case, you need to write less code, because the connection pool of the application server has established a connection for you. Listing 2: initializing from a J2EE connection pool
Because the number of users accessing the website at the same time may be very large, creating a cachedrowset from the J2EE connection pool is much better than directly creating a connection. The datasourcename attribute tells cachedrowset where the desired connection can be found in the JNDI namespace. Once the execute () method is called, cachedrowset obtains an instance of javax. SQL. datasource from JNDI. The datasource object provides a JDBC connection to the rowset. In addition to obtaining a connection through cachedrowset, you can also pass an existing connection as a parameter to the execute () method. The example in listing 3, like in Listing 2, also obtains connections from the J2EE connection pool. The difference is that the JNDI search is explicit: Listing 3: initializing by passing an existing connection
The connection pool JNDI lookup names for different application servers are different. You can refer to the instructions of the application server you are using to provide a suitable JNDI lookup mode for the connection pool. Display the cachedrowset attribute in JSP After initializing cachedrowset, you can use it like a java. SQL. resultset object. As mentioned earlier, cachedrowset is only another Implementation of the Java. SQL. resultset interface. You can place the cursor in the target row and then use the getstring () method to access the cachedrowset attribute (assuming the corresponding data type is varchar ). Each JDBC data type has a corresponding get method. If you do not know the data type, you can use GetObject () instead. Use the index number or the original resultset to specify the column:
If you use the method in Listing 1 to include the usebean tag, you can access the cachedrowset by using the script ID of this tag anywhere on your page. The following describes how to use an HTML submitted form field to display an instance of the cachedrowset column:
To obtain attributes, you can display other information about cachedrowset, such as the current row number:
Navigation is also important for displaying the cachedrowset attribute. Cachedrowset supports all the navigation commands declared by the resultset interface. It even includes new JDBC 2.0 methods such as previous (), first (), and last. Currently, the standard JDBC version is JDBC 1.0, and sun has not updated its JDBC drive to version 2.0. However, because the data is cached on the client, cachedrowset provides forward and backward scrolling capabilities for jdbc1.0. This superior feature frees developers from the dilemma of writing their own resultset buffer objects for record browsing applications. To navigate from your JSP, simply accept the request and response command parameters:
Each navigation method returns true or false based on whether the processing is successful. If the cursor fails, the cursor must be returned to a valid position. You can use the submit button to send navigation parameters:
Update cachedrowset My favorite cachedrowset feature is that it can automatically update databases. People familiar with ASP and Microsoft ADO development may be interested in this feature. Cachedrowset can greatly reduce the number of SQL statements written by developers and complete joint updates in one operation. Joint update is very useful for Web applications that only have limited database connections and may cause bottlenecks in connection. For most cachedrowset applications, you only need to provide the initial SQL query statement. At least one update operation can be implemented using three methods on the cachedrowset, but these methods must be called in the specified order. First, call an update method of resultset if the cursor is located in the correct position. This depends on the column type in the example database. The update may be as follows:
If necessary, you can repeat the above similar code for the required columns in rowset. Then, before moving the cursor position, rowset must know whether you want to submit changes to the current column:
The above Code does not send the changes to the database, but only caches the changes to the rowset cache. When you update records, you do not need to bind a database connection. You only need to connect to the database to submit all updates of rowset. The third and last step is to call acceptchanges () and submit all the changes to the database together. If you use the cachedrowset created in the method in Listing 1, the following code submits all previous update operations including:
If you initialize and create a cachedrowset using the method in listing 3, you must specify the connection object:
On the JSP page, this operation can be associated with a request parameter. It allows users to decide when to submit updates:
Cachedrowset can also perform insert and delete operations. Although it is very simple to insert records into cachedrowset, It is not intuitive. Before inserting a row, you must place the cursor in a special location called "insert row. The following code inserts a row of data into cachedrowset:
Movetoinsertrow () moves the cursor to a blank row that may contain the initial column value. Call insertrow () to insert a new row to the end of the cursor. Then, call movetocurrentrow () to reset the cursor position before calling other navigation commands. Finally, you must call acceptchanges () for rowset to update the database. Some modifications may be required to apply it to JSP. The entry/edit form of most records inserts rows following the steps below: Insert a new row at the end of the current cursor, Initialize all fields as null, and display the newly inserted rows in the form for editing or saving, to add the record insertion function, your JSP needs to listen to a new request parameter "insert ". The insert operation code for the JSP page is as follows:
Other usage of cachedrowset The expansion of the buffer resultset is far from limited to JSP development. cachedrowset is suitable for any situation where table data needs to be serialized and sent to the client, such as mobile computing devices. Although users may disconnect Network Data Sources periodically, they still need to fill in and maintain data. You can use cachedrowset to access/update data from the EJB Session Bean. Remember, cachedrowset is fully stored in memory. If an application needs to operate on a very large dataset, cachedrowset is obviously not the best choice. Listing 4 shows a complete JSP entry/Edit page code using cachedrowset as the data model. For clarity, I added the data model initialization code to the JSP page, which is embodied in the usebean tag. In larger engineering applications, we recommend that you follow the MVC (Model-View-Controller) Guidelines and put the data model initialization code in a servlet controller. Listing 4: JSP Example update/Entry Form
The following figure shows the result of the preceding JSP code displayed in the browser. In Listing 4, JSP explicit in the browser Conclusion JSP applications require a data buffer intermediate layer to improve the sharing of connection resources. For most small-and medium-sized applications, EJB is too small to use. Cachedrowset fills in the gaps in the period by providing a method to achieve unified access to JDBC data without bundling database connections. Cachedrowset also simplifies the data access code in a typical entry/edit JSP application. The preceding example page requires only one SQL statement to update, insert, and delete the database. More importantly, cachedrowset performs arbitrary disconnection and reconnection on the original data source. Source: www.javaworld.com |
The following are all tested by myself and can be safely used ;)
Test Database: mysql4.0.1 or MySQL 5.0.28
Driver: JDBC 3.1.12 of MySQL
Database:
/*
MySQL DATA TRANSFER
Source HOST: localhost
Source database: Test
Target Host: localhost
Target database: Test
Date: 2006-5-10 10:44:08
*/
Set foreign_key_checks = 0;
------------------------------
-- Table structure for student
------------------------------
Create Table 'student '(
'Id' varchar (255) not null default '',
'Name' varchar (255) default null,
Primary Key ('id ')
) Engine = InnoDB default charset = utf8;
------------------------------
-- Records
------------------------------
Insert into 'student 'values ('1', 'user 01 ');
Insert into 'student 'values ('2', 'user 02 ');
Package test. javax. SQL;
Import java. Io. fileoutputstream;
Import java. Io. ioexception;
Import java. SQL. connection;
Import java. SQL. drivermanager;
Import java. SQL. resultset;
Import java. SQL. sqlexception;
Import java. SQL. statement;
Import javax. SQL. rowset;
Import javax. SQL. rowsetevent;
Import javax. SQL. rowsetlistener;
Import com. Sun. rowset. cachedrowsetimpl;
Import com. Sun. rowset. webrowsetimpl;
Public class testjdbcrowset {
Private Static final string db2url = "JDBC: mysql: // localhost: 3306/test? Useunicode = true & characterencoding = gb2312 ";
Private Static final string db2user = "root ";
Private Static final string db2password = "";
Private Static final string db2driver = "com. MySQL. JDBC. Driver ";
Public static void testcachedrowset (){
Connection conn = NULL;
Try {
// Obtain the database connection
Conn = drivermanager. getconnection (db2url, db2user, db2password );
Statement stmt = NULL;
Resultset rs = NULL;
Stmt = conn. createstatement ();
// Query the database to obtain table data
Rs = stmt.exe cutequery ("select * from student"); // $ NON-NLS-1 $
// Generate a cachedrowset object based on the resultset object
Cachedrowsetimpl CRS = new cachedrowsetimpl ();
CRS. populate (RS );
// Disable resultset
Rs. Close ();
// Close the database connection
Stmt. Close ();
Conn. Close ();
// Perform operations on the cachedrowset when the connection to the database is interrupted
Operateonrowset (CRS );
CRS. beforefirst ();
For (; CRS. Next ();){
System. Out. println (CRS. getstring (1 ));
}
System. Out. println ("reconnect to Database ");
Conn = drivermanager. getconnection (db2url, db2user, db2password );
System. Out. println ("update content of cachedrowset to Database ");
CRS. acceptchanges (conn );
System. Out. println ("Disable cachedrowset ");
CRS. Close ();
System. Out. println ("close database connection ");
Conn. Close ();
} Catch (sqlexception e ){
System. Out. println ("Andrew: sqlexception! "); // $ NON-NLS-1 $
E. printstacktrace ();
}
}
Public static void operateonrowset (rowset RS ){
// Register the listener for rowset
Myrowsetlistener mylistener = new myrowsetlistener ();
Rs. addrowsetlistener (mylistener );
// Operate rowset data
Try {
// Traverse and read data
For (; RS. Next (); RS. updaterow ()){
String id = Rs. getstring ("ID"); // $ NON-NLS-1 $
String name = Rs. getstring ("name"); // $ NON-NLS-1 $
System. Out. println ("ID =" + ID + ", name =" + name); // $ NON-NLS-1 $
// Connect "1" at the last position of the ID"
// Rs. updatestring (1, ID + "-1 ");
Rs. updatestring ("ID", ID + "-1 ");
}
} Catch (sqlexception e ){
System. Out. println ("Andrew: sqlexception! "); // $ NON-NLS-1 $
E. printstacktrace ();
}
}
Public static void main (string [] ARGs ){
Try {
Class. forname (db2driver). newinstance ();
} Catch (instantiationexception e ){
System. Out. println ("Andrew: instantiationexception! "); // $ NON-NLS-1 $
} Catch (illegalaccessexception e ){
System. Out. println ("Andrew: illegalaccessexception! "); // $ NON-NLS-1 $
} Catch (classnotfoundexception e ){
System. Out. println ("Andrew: classnotfoundexception! "); // $ NON-NLS-1 $
}
Testcachedrowset ();
}
}
Class myrowsetlistener implements rowsetlistener {
// Move the cursor
Public void cursormoved (rowsetevent event ){
System. Out. println ("cursor moved ");
}
// Row changed
Public void rowchanged (rowsetevent event ){
System. Out. println ("Row changed ");
}
// Rowset changed
Public void rowsetchanged (rowsetevent event ){
System. Out. println ("row set changed ");
}
}
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
