Using Rowsets to Display Data - Java Database Programming Bible - page 452

Since CachedRowSets are JavaBeans, they can be serialized just like any other JavaBean This makes
them very useful when working with a remote client, such as a PDA.

You may recall that the database design examples in Chapter 2 revolve around a database for
managing and invoicing projects or, as lawyers prefer to call them, "matters" for a law firm. A part of this
example included the creation of a number of tables to handle contact information. It would obviously be
useful for the employees of the firm to have a copy of the contact names and addresses in their PDAs.
An elegant way to do this is through the use of a CachedRowSet , since a CachedRowSet only needs
to connect to its data source while it is reading or updating data.

The first thing to consider in creating the contact list is the SQL query required to build the RowSet . The
contact information required for the RowSet is distributed across the three following tables because of
the nature of the application:

The client is frequently a corporation, represented by several individuals.

A corporation may operate out of a number of different locations.

Each individual may have a different phone number, mail drop, or cell phone, but all may have the
same mailing address.

Since one of the principles of database design is to avoid storing the same item of information in two or
more places, this means that the information about a client has to be divided among a number of
different tables. The structure and relationships of these tables is shown in Figure 18-1 .

Figure 18-1: Tables containg contact information

Server-side code

The best way to retrieve the required contact information is to define a SQL stored procedure called
GET_CONTACT_LIST and to call it to get the data. As you can see from Listing 18-6 , the stored
procedure is relatively simple.

Listing 18-6: Stored procedure to retrieve contact data

CREATE PROCEDURE GET_CONTACT_LIST AS
SELECT c.fName, c.lName, f.name AS firm, a.address_1 as street, a.city,
a.state_province AS state, a.zip_postal_code AS zip, c.phone
FROM CONTACTS c, Address_info a, clients f
WHERE f.address_id = a.id AND c.company_id = f.id

Table 18-3 shows the ResultSet returned by the query. Obviously, you can include the contact's cell
phone number, e-mail address, and so on.