RowSet Interface

In the previous examples, you learned how to query a database by explicitly establishing a Connection to the database, preparing a Statement for querying the database and executing the query. In this section, we demonstrate the RowSet interface, which configures the database connection and prepares query statements automatically. Interface RowSet provides several set methods that allow the programmer to specify the properties needed to establish a connection (such as the database URL, user name and password of the database) and create a Statement (such as a query). Interface RowSet also provides several get methods that return these properties.

RowSet is part of the javax.sql package. Although part of Java 2 Standard Edition, the classes and interfaces of package javax.sql are most frequently used in the context of the Java 2 Platform Enterprise Edition (J2EE). J2EE is used in industry to build substantial distributed applications that often process database data. J2EE is beyond the scope of this book. You can learn more about J2EE by visiting java.sun.com/j2ee/.

There are two types of RowSet objectsconnected and disconnected. A connected RowSet object connects to the database once and remains connected until the application terminates. A disconnected RowSet object connects to the database, executes a query to retrieve the data from the database and then closes the connection. A program may change the data in a disconnected RowSet while it is disconnected. Modified data can be updated in the database after a disconnected RowSet reestablishes the connection with the database.

J2SE 5.0 package javax.sql.rowset contains two subinterfaces of RowSetJdbcRowSet and CachedRowSet. JdbcRowSet, a connected RowSet, acts as a wrapper around a ResultSet object, and allows programmers to scroll through and update the rows in the ResultSet. Recall that by default, a ResultSet object is non-scrollable and read onlyyou must explicitly set the result-set type constant to TYPE_SCROLL_INSENSITIVE and set the result-set concurrency constant to CONCUR_UPDATABLE to make a ResultSet object scrollable and updatable. A JdbcRowSet object is scrollable and updatable by default. CachedRowSet, a disconnected RowSet, caches the data of a ResultSet in memory and disconnects from the database. Like JdbcRowSet, a CachedRowSet object is scrollable and updatable by default. A CachedRowSet object is also serializable, so it can be passed between Java applications through a network, such as the Internet. However, CachedRowSet has a limitationthe amount of data that can be stored in memory is limited. Besides JdbcRowSet and CachedRowSet, package javax.sql.rowset contains three other subinterfaces of RowSet. For details of these interfaces, visit java.sun.com/j2se/5.0/docs/guide/jdbc/getstart/rowsetImpl.html.

Figure 25.32 reimplements the example of Fig. 25.25 using a RowSet. Rather than establish the connection and create a Statement explicitly, Fig. 25.32 uses a JdbcRowSet object to create a Connection and a Statement automatically.

Figure 25.32. Displaying the authors table using JdbcRowSet.

(This item is displayed on pages 1225 - 1226 in the print version)

 1 // Fig. 25.32: JdbcRowSetTest.java
 2 // Displaying the contents of the authors table using JdbcRowSet.
 3 import java.sql.ResultSetMetaData;
 4 import java.sql.SQLException;
 5 import javax.sql.rowset.JdbcRowSet; 
 6 import com.sun.rowset.JdbcRowSetImpl; // Sun's JdbcRowSet implementation
 7
 8 public class JdbcRowSetTest
 9 {
10 // JDBC driver name and database URL
11 static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
12 static final String DATABASE_URL = "jdbc:mysql://localhost/books";
13 static final String USERNAME = "jhtp6";
14 static final String PASSWORD = "jhtp6";
15
16 // constructor connects to database, queries database, processes
17 // results and displays results in window
18 public JdbcRowSetTest()
19 {
20 // connect to database books and query database
21 try
22 {
23 Class.forName( JDBC_DRIVER ); // load database driver class
24 
25 // specify properties of JdbcRowSet 
26 JdbcRowSet rowSet = new JdbcRowSetImpl(); 
27 rowSet.setUrl( DATABASE_URL ); // set database URL 
28 rowSet.setUsername( USERNAME ); // set username 
29 rowSet.setPassword( PASSWORD ); // set password 
30 rowSet.setCommand( "SELECT * FROM authors" ); // set query
31 rowSet.execute(); // execute query 
32 
33 // process query results
34 ResultSetMetaData metaData = rowSet.getMetaData();
35 int numberOfColumns = metaData.getColumnCount();
36 System.out.println( "Authors Table of Books Database:" );
37 
38 // display rowset header
39 for ( int i = 1; i <= numberOfColumns; i++ )
40 System.out.printf( "%-8s	", metaData.getColumnName( i ) );
41 System.out.println();
42 
43 // display each row
44 while ( rowSet.next() )
45 {
46 for ( int i = 1; i <= numberOfColumns; i++ )
47 System.out.printf( "%-8s	", rowSet.getObject( i ) );
48 System.out.println();
49 } // end while
50 } // end try
51 catch ( SQLException sqlException )
52 {
53 sqlException.printStackTrace();
54 System.exit( 1 );
55 } // end catch
56 catch ( ClassNotFoundException classNotFound )
57 {
58 classNotFound.printStackTrace();
59 System.exit( 1 );
60 } // end catch
61 } // end DisplayAuthors constructor
62 
63 // launch the application
64 public static void main( String args[] )
65 {
66 JdbcRowSetTest window = new JdbcRowSetTest();
67 } // end main
68 } // end class JdbcRowSetTest

 

Authors Table of Books Database:
authorID firstName lastName
1 Harvey Deitel
2 Paul Deitel
3 Tem Nieto
4 Sean Santry

 

Line 26 uses Sun's reference implementation of the JdbcRowSet interface, JdbcRowSetImpl from package com.sun.rowset, to create a JdbcRowSet object. Package com.sun.rowset provides implementations of the interfaces in package javax.sql.rowset. We used class JdbcRowSetImpl here to demonstrate the capability of the JdbcRowSet interface. Some databases may provide their own RowSet implementations.

Line 27 invokes JdbcRowSet method setUrl to specify the database URL, which is then used by the DriverManager to establish a connection. Line 28 invokes JdbcRowSet method setUsername to specify the username, which is then used by the DriverManagger to establish a connection. Line 29 invokes JdbcRowSet method setPassword to specify the password, which is then used by the DriverManager to establish a connection. Line 30 invokes JdbcRowSet method setCommand to specify the SQL query. Line 31 invokes JdbcRowSet method execute to execute the SQL query. Method execute performs four actionsit establishes a Connection, prepares the query Statement, executes the query and stores the ResultSet returned by query. The Connection, Statement and ResultSet are encapsulated in the JdbcRowSet object. The remaining code is almost identical to Fig. 25.25, except that line 34 obtains a ResultSetMetaData object from the JdbcRowSet, line 44 uses the JdbcRowSet's next method to get the next row of the result, and line 47uses the JdbcRowSet's getObject method to obtain a column's value. Note that the output of this application is exactly the same as that of Fig. 25.25.

In this chapter, you learned basic database concepts, how to interact with data in a database using SQL and how to use JDBC to allow Java applications to manipulate database data. You learned the explicit steps for obtaining a Connection to the database, creating a Statement to interact with the database's data, executing the statement and processing the results. Finally you saw how to use a RowSet to simplify the process of connecting to a database and creating statements. In the next chapter, you will learn about servlets, which are Java programs that enhance a Web Server's capabilities. Servlets sometimes use JDBC to interact with databases on behalf of users who make requests via Web browsers.