36.10. Programming Exercises
33.5. RowSet , JdbcRowSet , and CachedRowSet
JDBC 2 introduced a new RowSet interface that can be used to simplify database programming. The RowSet interface extends java.sql.ResultSet with additional capabilities that allow a RowSet instance to be configured to connect to a JDBC url, username, password, set a SQL command, execute the command, and retrieve the execution result. In essence, it combines Connection , Statement , and ResultSet into one interface. A concrete RowSet class can be used as a JavaBeans component in a visual GUI development environment such as JBuilder and NetBeans.
Note
| | Not all JDBC drivers support RowSet . Currently, the JDBC-ODBC driver does not support all features of RowSet . |
33.5.1. RowSet Basics
There are two types of RowSet objects: connected and disconnected. A connected RowSet object makes a connection with a data source and maintains that connection throughout its life cycle. A disconnected RowSet object makes a connection with a data source, executes a query to get data from the data source, and then closes the connection. A disconnected rowset may make changes to its data while it is disconnected and then send the changes back to the original source of the data, but it must reestablish a connection to do so.
There are several versions of RowSet . Two frequently used are JdbcRowSet and CachedRowSet . Both are subinterfaces of RowSet . JdbcRowSet is connected, while CachedRowSet is disconnected. Also, JdbcRowSet is neither serializable nor cloneable, while CachedRowSet is serializable and cloneable. The database vendors are free to provide concrete implementations for these interfaces. Sun has provided the reference implementation JdbcRowSetImpl for JdbcRowSet and CachedRowSetImpl for CachedRowSet . Figure 33.5 shows the relationship of these components .
Figure 33.5. The JdbcRowSetImpl and CachedRowSetImpl are concrete implementations of RowSet .
(This item is displayed on page 1146 in the print version)
The RowSet interface contains the JavaBeans properties with get and set methods. You can use the set methods to set a new url, username, password, and command for an SQL statement. Using a RowSet , Listing 32.1 can be simplified, as shown in Listing 33.6.
Listing 33.6. SimpleRowSet.java
1 import java.sql.SQLException; 2 import javax.sql.RowSet; 3 import com.sun.rowset.*; 4 5 public class SimpleRowSet { 6 public static void main(String[] args) 7 throws SQLException, ClassNotFoundException { 8 // Load the JDBC driver 9 Class.forName( "com.mysql.jdbc.Driver" ); 10 System.out.println( "Driver loaded" ); 11 12 // Create a row set 13 RowSet rowSet = new JdbcRowSetImpl(); 14 15 // Set RowSet properties 16 rowSet.setUrl( "jdbc:mysql://localhost/test" ); 17 rowSet.setCommand( "select firstName, mi, lastName " + 18 "from Student where lastName = 'Smith'" ); 19 rowSet.execute(); 20 21 // Iterate through the result and print the student names 22 while ( rowSet. next () ) 23 System.out.println(rowSet.getString( 1 ) + "\t" + 24 rowSet.getString( 2 ) + "\t" + rowSet.getString( 3 )); 25 26 // Close the connection 27 rowSet.close(); 28 } 29 } |
Line 13 creates a RowSet object using JdbcRowSetImpl . The program uses the RowSet 's set method to set a URL (line 16) and a command for a query statement (line 17). Line 19 executes the command in the RowSet . The methods next() are getString(int) for processing the query result (lines 22 “24) are inherited from ResultSet .
If you replace JdbcRowSet with CachedRowSet in line 13, the program will work just fine.
Tip
| | Since RowSet is a subinterface of ResultSet , all the methods in ResultSet can be used in RowSet . For example, you can obtain ResultSetMetaData from a RowSet using the getMetaData() method. |
33.5.2. RowSet for PreparedStatement
The discussion in §32.5, "PreparedStatement," introduced processing parameterized SQL statements using the PreparedStatement interface. RowSet has the capability to support parameterized SQL statements. The set methods for setting parameter values in PreparedStatement are implemented in RowSet . You can use these methods to set parameter values for a parameterized SQL command. Listing 33.7 demonstrates how to use a parameterized statement in RowSet . Line 17 sets an SQL query statement with two parameters for lastName and mi in a RowSet . Since these two parameters are strings, the setString method is used to set actual values in lines 19 “20.
Listing 33.7. RowSetPreparedStatement.java
1 import java.sql.*; 2 import javax.sql.RowSet; 3 import com.sun.rowset.*; 4 5 public class RowSetPreparedStatement { 6 public static void main(String[] args) 7 throws SQLException, ClassNotFoundException { 8 // Load the JDBC driver 9 Class.forName( "com.mysql.jdbc.Driver" ); 10 System.out.println( "Driver loaded" ); 11 12 // Create a row set 13 RowSet rowSet = new JdbcRowSetImpl(); 14 15 // Set RowSet properties 16 rowSet.setUrl( "jdbc:mysql://localhost/test" ); 17 rowSet.setCommand( "select * from Student where lastName = ? " + 18 "and mi = ?" ); 19 rowSet.setString( 1 , "Smith" ); 20 rowSet.setString( 2 , "R" ); 21 rowSet.execute(); 22 23 ResultSetMetaData rsMetaData = rowSet.getMetaData(); 24 for ( int i = 1 ; i <= rsMetaData.getColumnCount(); i++) 25 System.out.printf( "%-12s\t" , rsMetaData.getColumnName(i)); 26 System.out.println(); 27 28 // Iterate through the result and print the student names 29 while (rowSet.next()) { 30 for ( int i = 1 ; i <= rsMetaData.getColumnCount(); i++) 31 System.out.printf( "%-12s\t" , rowSet.getObject(i)); 32 System.out.println(); 33 } 34 35 // Close the connection 36 rowSet.close(); 37 } 38 } |
33.5.3. Scrolling and Updating RowSet
By default, a ResultSet object is not scrollable and updateable. However, a RowSet object is scrollable and updatable. It is easier to scroll and update a database through a RowSet than a ResultSet . Listing 33.8 gives an example that scrolls and updates a database through a RowSet . You can use the methods (e.g., absolute(int) ) to move the cursor and update the database using such methods as delete() , updateRow() , and insertRow() .
Listing 33.8. ScrollUpdateRowSet.java
(This item is displayed on pages 1148 - 1149 in the print version)
1 import java.sql.*; 2 import javax.sql.RowSet; 3 import com.sun.rowset.JdbcRowSetImpl; 4 5 public class ScrollUpdateRowSet { 6 public static void main(String[] args) 7 throws SQLException, ClassNotFoundException { 8 // Load the JDBC driver 9 Class.forName( "com.mysql.jdbc.Driver" ); 10 System.out.println( "Driver loaded" ); 11 12 // Create a row set 13 RowSet rowSet = new JdbcRowSetImpl(); 14 15 // Set RowSet properties 16 rowSet.setUrl( "jdbc:mysql://localhost/test" ) ; 17 rowSet.setCommand( "select * from Student" ); 18 rowSet.execute(); 19 20 // Iterate through the result and print the student names 21 System.out.println( "Before update " ); 22 ResultSetMetaData rsMetaData = rowSet.getMetaData(); 23 while (rowSet.next()) { 24 for ( int i = 1 ; i <= rsMetaData.getColumnCount(); i++) 25 System.out.printf( "%-12s\t" , rowSet.getObject(i)); 26 System.out.println(); 27 } 28 29 rowSet.absolute( 2 ); // Move cursor to the 2nd row 30 rowSet.deleteRow(); // Delete the second row 31 32 rowSet.absolute( 5 ); // Move cursor to the 5th row 33 rowSet.updateString( "phone" , "912921111" ); // updates the column 34 rowSet.updateRow(); // updates the row in the data source 35 36 rowSet.moveToInsertRow(); // Move cursor to the insert row 37 rowSet.updateString( "ssn" , "1111111111" ); // Update the lastName 38 rowSet.updateString( "lastName" , "Yao" ); // Update the lastName 39 rowSet.updateString( "firstName" , "An" ); // Update the firstName 40 rowSet.insertRow(); // Insert the row 41 rowSet.moveToCurrentRow(); // Move the cursor to the current row 42 43 System.out.println( "After update " ); 44 rowSet.first(); 45 while (rowSet.next()) { 46 for ( int i = 1 ; i <= rsMetaData.getColumnCount(); i++) 47 System.out.printf( "%-12s\t" , rowSet.getObject(i)); 48 System.out.println(); 49 } 50 51 // Close the connection 52 rowSet.close(); 53 } 54 } |
If you replace JdbcRowSet with CachedRowSet in line 13, the database is not changed. To make the changes on the CachedRowSet effective in the database, you must invoke the acceptChanges() method after you make all the changes, as follows :
rowSet.acceptChanges(); // Write changes back to the database
This method automatically reconnects to the database and writes all the changes back to the database.
33.5.4. RowSetEvent
A RowSet object fires a RowSetEvent whenever the object's cursor has moved, a row has changed, or the entire row set has changed. This event can be used to synchronize a RowSet with the components that rely on the RowSet . For example, a visual component that displays the contents of a RowSet should be synchronized with the RowSet . The RowSetEvent can be used to achieve synchronization. The handlers in RowSetListener are cursorMoved(RowSetEvent) , rowChanged(RowSetEvent) , and cursorSetChanged(RowSetEvent) .
Listing 33.9 gives an example that demonstrates RowSetEvent . A listener for RowSetEvent is registered in lines 14 “26. When rowSet.execute() (line 31) is executed, the entire row set is changed, so the listener's rowSetChanged handler is invoked. When rowSet.last() (line 33) is executed, the cursor is moved, so the listener's cursorMoved handler is invoked. When rowSet.updateRow() (line 35) is executed, the row is updated, so the listener's rowChanged handler is invoked.
Listing 33.9. TestRowSetEvent.java
(This item is displayed on pages 1149 - 1150 in the print version)
1 import java.sql.*; 2 import javax.sql.*; 3 import com.sun.rowset.*; 4 5 public class TestRowSetEvent { 6 public static void main(String[] args) 7 throws SQLException, ClassNotFoundException { 8 // Load the JDBC driver 9 Class.forName( "com.mysql.jdbc.Driver" ); 10 System.out.println( "Driver loaded" ); 11 12 // Create a row set 13 RowSet rowSet = new CachedRowSetImpl(); 14 rowSet.addRowSetListener( new RowSetListener() { 15 public void cursorMoved(RowSetEvent e) { 16 System.out.println( "Cursor moved" ); 17 } 18 19 public void rowChanged(RowSetEvent e) { 20 System.out.println( "Row changed" ); 21 } 22 23 public void rowSetChanged(RowSetEvent e) { 24 System.out.println( "row set changed" ); 25 } 26 }); 27 28 // Set RowSet properties 29 rowSet.setUrl( "jdbc:mysql://localhost/test" ); 30 rowSet.setCommand( "select * from Student" ); 31 rowSet.execute(); 32 33 rowSet.last(); // Cursor moved 34 rowSet.updateString( "lastName" , "Yao" ); // Update column 35 rowSet.updateRow(); // Row updated 36 37 // Close the connection 38 rowSet.close(); 39 } 40 } |
EAN: N/A
Pages: 503