Chapter 7: Databases

7.2. Remote Database Applications

As we alluded to earlier in the book, client-server applications are often broken down into different levels, or tiers, to use the current vernacular. A typical application breakdown involves three tiers: user-interface functions, application objects, and data access. Up until now, we've mostly been discussing ways of distributing the application logic across the network, using distributed objects or some form of message passing to allow elements of the core application to lie on remote hosts. In this chapter, we'll discuss the options available for implementing the data access level of a distributed application.

The data access tier of a multitiered distributed application can be divided into two subtiers, as shown in Figure 7-1. First, an interface must be established between JDBC and the actual DBMS holding the data. A set of data objects representing the concepts in the database can then be defined; these objects serve as an interface from the application-level objects to the data in the database. These data objects interact with the DBMS through the JDBC interface to perform operations on the low-level data that they represent. The application-layer objects interact with the data objects to query and update the data.

Figure 7-1. Breakdown of the data access tier

7.2.1. JDBC Driver Configurations

Before you can use the JDBC API to engage in transactions with your database, a JDBC driver that is connected to your database has to be available. The JDBC driver accepts the JDBC transaction protocol and converts it to the appropriate native commands for the type of RDBMS you are using.

Figure 7-2 shows the various ways that a JDBC driver can be configured to interact with an RDBMS. The first configuration involves a JDBC driver running on the database host machine and interacting directly with the relational database. The JDBC client, consisting of JDBC API code plus the JDBC driver, resides either on a remote machine or on the database host itself. The JDBC driver accepts connections from clients using the JDBC API to issue statements to the database. The driver converts the JDBC commands directly into the RDBMS native access protocol (typically SQL with the addition of some proprietary commands) and issues the native statements to the RDBMS. The results from the database are received by the JDBC driver, converted to the appropriate transmission format, and sent to the JDBC client, where they are returned in the form of a ResultSet object to the caller.

Figure 7-2. JDBC driver configurations

The second configuration is similar to the first, except that an ODBC driver sits between the JDBC driver and the relational database. ODBC is a standard relational database driver API that has become popular because it insulates applications from the specifics of particular relational databases. ODBC drivers are offered for nearly all major RDBMS vendors on the market today. In this configuration, the JDBC driver acts as before with respect to the client, accepting and responding to JDBC transaction requests. Now, however, these transactions are converted to ODBC statements and issued to the ODBC driver, which in turn converts the statements to the RDBMS native format. This configuration uses a JDBC-ODBC bridge, which is essentially a JDBC driver that speaks ODBC, rather than a specific database vendor's access protocol. The advantage to using ODBC to communicate with the database is the ability to migrate your data and its structure to another database, or to apply the same application to another database with an ODBC driver. ODBC is also an important option to consider when a native JDBC driver is not available for the database in question. However, adding an additional conversion step in the query process can be inefficient, and sometimes unacceptable in high-throughput applications.

The third configuration is one where a JDBC driver on the client converts JDBC statements directly into a networked protocol. This protocol is then transmitted to a remote database server fitted with a module capable of receiving this protocol and responding to it. The database client now contains only the JDBC API and the JDBC driver for this networked protocol. The advantage of this configuration is that the client element of the JDBC driver can be relatively simple, since it only needs to convert JDBC commands into the networked protocol. All of the logic for converting data types to native database types and back again, issuing statements to the native database through its access protocol, and mapping the results into either Java objects or the appropriate Exception, resides with the more complex agent on the database server. This option of reducing the amount of code residing on the data client can be an important one, especially in an applet context in which the client agent is running on a user's desktop.

The first two JDBC driver configurations (JDBC directly to DBMS and JDBC to ODBC to DBMS) also can be used to access databases on remote hosts. Many native DBMS drivers as well as ODBC drivers include support for networked access of database servers. These drivers serialize native database protocols or the ODBC protocol and engage in transactions with a remote DBMS over the network. The DBMS host server needs to be augmented with drivers that can accept these networked connections. In this case, the DBMS or ODBC drivers need to reside on the client host, in addition to the network transaction drivers.

7.2.2. Defining the Data Objects

With these database driver configurations, we can examine our options for implementing the data objects of our multitiered distributed application.

One of the design principles we've been advocating is encapsulation--hiding the low-level implementation details of a system or subsystem and providing a clean, well-defined interface to it. This principle is also applicable in the design of the data access layer of distributed applications. The "low-level details" we want to hide through encapsulation are things like the type of DBMS being used (e.g., relational or object-oriented, vendor name, DBMS version), the specific location (e.g., host, database name, etc.) of the data on the network, and even the structural layout of the data in the database (e.g., field, table, and view names in a relational database).

Data layer encapsulation is achieved in the Java environment by defining classes that represent the conceptual data stored in your database. Think about storing and retrieving personal information (e.g., name, address, phone number). Ignoring the data layout issues for the moment, we would normally want to encapsulate this data as a Java class that represents a person:

class DBPerson {
    Connection c;    // The connection to the database
    String name;     // Name of the desired person
    // Any other data we want to cache within the object
        ...

    public DBPerson(String name, Connection c, boolean preload) {
        dbConn = c;
        if (preload) {
            initFromDB();
        }
    }

    public String getAddress() {
        // Perform database transactions to retrieve 
        // address information
            ...
    }

The data objects that we use to represent concepts stored in our database tables can be either local or remote with respect to the application logic of our system. If they are local, the data objects could be Java objects that access the database using JDBC directly. If our system will involve remote data objects, perhaps due to issues regarding direct access to the database, then the data objects would need to be accessible from the local client as a data server. This access could be provided through distributed objects implemented in RMI or CORBA, or it could be through simpler message-passing techniques such as those described in Chapter 6, "Message-Passing Systems". In the following sections we'll examine an example database application, and the different ways the data objects could be implemented in both a local and remote context.

7.2.3. A Scheduling Example

To demonstrate these ideas, let's turn to a distributed application involving databases. Our example will revolve around a generic scheduling system, where the data to be stored in a DBMS will include information on resources, tasks to be accomplished with these resources, temporal constraints between tasks, and assignments of resources to tasks. Let's assume for now that the data is stored in a relational database of some sort, and is arranged into tables as shown in Figure 7-3. A RESOURCE record identifies an available resource, along with its name, coded type, and a size value that is interpreted based on its type (e.g., the size of a storage bin may be some volume measurement, while the size of a drill press may be the largest job it can process). A TASK record represents a task that needs to be accomplished using the available resources. It includes a coded type field and a size that is also interpreted based on its type. A TIME_CONSTRAINT record represents a constraint on the completion times of two tasks. A coded type field indicates what kind of temporal constraint is being placed on the two tasks (e.g., "before," "after," "during"). A RES_ASSIGNMENT record represents the assignment of a task to a resource at a given time. The complete set of RES_ASSIGNMENT records represents a schedule for this set of resources and tasks. The RESOURCE and TASK tables have primary keys (rid and tid, respectively), which are unique identifiers for the records in these tables. The records in the TIME_CONSTRAINT table are keyed off of the TASK records that they reference in their task1 and task2 fields. The RES_ASSIGNMENT records are also indirectly keyed off of the resource and task IDs that they associate in time.

Figure 7-3. Data layout for a scheduling database

In designing the data objects that will be used to access this data, we have to consider several factors, including:

• What is a sensible way to represent this information in an application?

  In this case, it seems obvious that we will need object representations of resources and tasks. But what about temporal constraints and resource assignments? If the goal is to develop a scheduling application with update capabilities, then it will probably be worthwhile having an explicit object representation of both constraints and resource assignments, so that they can be created and manipulated directly by the scheduling algorithm. If we are developing a simple schedule viewing tool, then these objects may not be necessary, and it would be sufficient to be able to ask a resource object for a list of tasks assigned to it, and to ask a task for a list of the other tasks which constrain it temporally.

• How will the objects access the database--through a centralized access connection, or through individual database connections?

  Generally, the best data throughput is achieved by creating a single database connection and accessing all data associated with various objects through it, rather than creating a new connection and executing a database transaction for each data element of each object. This is due to the overhead typically involved in setting up the connection to the database: if each new object has to create its own connection, setting up the connection will delay the availability of the data that object represents. There are situations, however, where it makes sense for a data object to manage its own database connection. A multi-database query interface, for example, may want to display a set of data hits using some metadata collected during the search, connected to a data object that is triggered to retrieve the actual data when the user selects it. If the set of remote databases included in the search is large or variable, it may be more sensible to have each data object manage its own connection to the source of its data.

• How dynamic will these objects be with respect to the database; i.e., how and when will an update to the database be reflected in corresponding Java data objects?

  This also depends on the nature of the application being developed on top of this data. It's safest to have no data cached within the data objects themselves, and have every request generate a new transaction with the database to get the newest data. This guarantees that the data presented to the user or the requesting agent is always current. If, however, we know that the update frequency of the data on the database server is fairly low, if we are accessing remote data sources over a low-bandwidth or unreliable network, or if we suspect that the data server will be unable to handle the extra requests, then we may want to cache data in the data objects to improve the overall performance of the application.

• What kinds of privileges will these objects be expected to have over the data they represent (e.g., will they be able to update the database data, or only represent a read-only view of the data)?

  Again, the answer to this depends on the nature of the application. If we are developing a schedule generation engine, then the data objects need update privileges on the database tables so that new time constraints and resource assignments can be added. If a schedule viewing tool is being developed, then the data object will simply need select privileges on the database tables.

For the scheduling example we are discussing, we can assume that the data objects will need to create new time constraint and resource assignment entries in the database. We should also provide a means to create new resources and tasks, unless we know that this data will always be created by another agent.

7.2.4. JDBC-Enabled Data Objects

For the first version of our scheduling data objects, we'll implement the Java classes using JDBC directly to access the scheduling database. We'll be depending on the JDBC driver configuration to provide networked access to remote databases. Later we'll look at how to access remote databases in other ways.

The Java classes for our JDBC example will be designed along the lines of Figure 7-4. A DatabaseItem class serves as the base class for all of our data objects. It maintains a global database Connection for all of the data objects in the application, and provides two abstract methods for reading and writing the objects in the database pointed at by the global Connection. The data objects that we'll derive from this base class will have a one-to-one mapping with the tables in our database.

Figure 7-4. Data objects for the scheduling example

Example 7-2 shows the Java code for the DatabaseItem interface. This base class provides slots for a global java.sql.Connection object and for a validity flag. The global Connection is used for all transactions made by the data objects with the database to retrieve or update their data. The DatabaseItem interface also includes two abstract methods: updateToDbase() and updateFrom-Dbase(). These have to be implemented in subclasses to perform the necessary database transactions to store the object's data to the database, and to retrieve the object's current data from the database, respectively. Anytime during the lifetime of a DatabaseItem, its updateFromDbase() method can be called to refresh the local data from the database, or its updateToDbase() method can be called to update the database with the local data.

The classes representing the data objects for our scheduling examples are shown in Example 7-3 through Example 7-6. The SchedResource and SchedTask classes in Example 7-3 and Example 7-4 represent resources and tasks in the database, while the TimeConstraint and ResAssignment classes in Example 7-5 and Example 7-6 represent constraints between tasks and assignments of resources to tasks.

An individual SchedResource or SchedTask object can be constructed in two ways: using an integer identifier for the desired database record, or using data items representing a new database record that is to be created. In the first case, the SchedResource and SchedTask constructors set their identifiers to the one given as an argument, then try to update themselves from the database by calling their updateFromDbase() methods. Their updateFromDbase() methods create a JDBC statement from the database connection and execute a SELECT query to the database to retrieve the data for the record. If the indicated record isn't found, then the valid flag on the object is set to false. In the second case, the constructor attempts to create a new record in the database with the given data by creating a JDBC statement and executing an INSERT query to the database. If the data insertion fails, then the valid flag on the object is set to false. The SchedResource and SchedTask classes also provide static methods for retrieving all of the resources or tasks from the schedule database. SchedResource.getAllResources() returns a vector of SchedResources representing all of the resource records found in the database, and the SchedTask.getAllTasks() method does the same for SchedTask objects.

TimeConstraint s and ResAssignments are created using their constructors with the relevant resource or task identifiers as arguments. The TimeConstraint constructor takes arguments that indicate its type, the IDs of two tasks that are to be constrained, and an insert flag that indicates whether the new constraint should be stored as a new record in the database. This flag is useful in situations where a local-only object is desired (e.g., a possible schedule for the existing tasks and resources is being evaluated locally before being committed to the database). The ResAssignment constructor has arguments for the ID of the resource being assigned, the ID of the task it is being assigned to, the time that the assignment is to occur, and an insert flag similar to the one for the TimeConstraint constructor. The TimeConstraint class also provides a static method, constraintsFor() , which takes a task identifier and returns all of the constraints from the database involving that task, as a list of TimeConstraint objects. ResAssignment has a similar method, assignmentsFor() , which takes a resource identifier and returns all assignments to tasks for that resource, in the form of a list of ResAssignment objects.

With these data objects defined, we can now use them in several distributed application contexts to access local and remote schedule databases. If the schedule database is local, then we simply need to have the JDBC drivers for the DBMS on our class path when we run an application built against these objects. The application will create a java.sql.Connection object with the name of the local database and use it to initialize all schedule-related objects. If we want to put the database on a remote server, then we have two ways to connect these data objects to the database. If the native DBMS interface provides a network interface and if the JDBC drivers we are using support this vendor-specific network interface, then we can install the DBMS network interface and modify the URL used to create the JDBC connection to the database to reflect the remote location. If this is not an option, either because the DBMS has no network interface, or because we cannot use the DBMS network interface on the client machine for some reason, then we have the option of using JDBC drivers with their own network protocol (as we discussed in an earlier section). The database host machine would need to have the corresponding server drivers installed, including the drivers that speak the native DBMS interface protocol.

7.2.5. Data Caching Issues

The JDBC data objects previously shown cache all of their data locally, i.e., within the data object representation itself. When each data object is created, all of the data associated with that object is read from the database and stored in local data variables; these local data variables are returned in response to method calls on the data objects. This caching scheme is appropriate when the data being accessed is fairly stable, or is only updated from one client at any given time. If our scheduling database is being accessed from multiple clients, and each client updates objects in the database at will, we may want to think about a shorter-duration caching scheme.

At the opposite end of the caching spectrum are data objects that have no cached data. Each request for data from the object is serviced by generating an SQL query to the database that gets the current data from the source. If we reimplemented our SchedResource class to eliminate data caching, its data access methods would look something like this getName() method:

public String getName()
{ 
    String name;
    Statement s = null;
    try {
        s = dbConn.getStatement();
        ResultSet r =
         s.executeQuery("SELECT name FROM resource where rid = "
                        + rid);
        name = r.getString("name");
    }
    catch {Exception e) { name = null; }
    finally {
        if (s != null) {
            s.close();
        }
    }
    return name;
}

Complete elimination of data caching is not generally useful for remote database applications, since the additional overhead involved in having all data read dynamically is usually unacceptable. Intermediate caching schemes involve data updates of varying frequency, and only a subset of the data served from the database is cached--the data that is least likely to be updated during the lifetime of the client agent. The data update frequency can be controlled using timers; for example, if we wanted the "name" field on our resource object to be cached, but have the cache refreshed from the database every hour in case of an update, then our getName() method on our SchedResource class might look something like this:

public String getName()
{ 
    String currName;
    Calendar now = Calendar.getInstance();
    Calendar lastUpdate = Calendar.getInstance();
    lastUpdate.setTime(nameTimestamp);
    // Add the cache expiration time (in minutes) to the time
    // of last update
    lastUpdate.add(cacheExpirationTime, Calendar.MINUTE);
    // If the name cache has expired, then go to the database
    if (lastUpdate.before(now)) {
        try {
            Statement s = dbConn.getStatement();
            ResultSet r =
             s.executeQuery("SELECT name FROM resource where rid = "
                            + rid);
            name = r.getString("name");
            // Reset the cache timer
            nameTimestamp = new Date();
        }
        catch {Exception e) { currName = name; }
    }
    // Otherwise, use the previous value cached in the class
    // data member
    currName = name;

    return currName;
}

In this example, the resource data object has a nameTimestamp variable that is used to expire the cache for the name data member. When getName() is called, the timestamp on the cache for that variable is checked. If the time since the cache was refreshed has gone beyond the limit we set (represented in minutes by the cacheExpirationTime data member), then a call to the database is made to refresh the cache. If not, then the local data member is used as the name of the resource.

7.2.6. Remote Data Servers

Now it may become necessary to isolate the direct data access portion of our data tier on a remote data server, and provide indirect data access to the application layer. One reason for this may be that the data representations required for a given application have many interdependencies and data constraints. If we want to keep the client-side data access lean and free from this complex data logic (e.g., if we are using these data objects in an applet), then we would prefer to put the data objects on a data server and provide a simplified data access layer to the client. An example of this is the potential complexity of the SchedTask data object. Updating this object in the database could involve some complex logic for verifying the validity of existing time constraints on the task, as well as resources currently assigned to completing the task. We could avoid inflicting this complexity on the client by providing a way for the client to transmit its update request to the data server, have the data server run the request through the data dependencies, update the database, and return the result to the client. Other reasons for separating data logic from the application tier may involve security issues (e.g., the potential for reverse-engineering the bytecodes of a data object to determine the proprietary structure of a database, physical access to the database server, etc.).

If the data objects also reside on a host that is remote from the client host, then we can interface the application tier to the data tier using either the message-passing techniques discussed in Chapter 6, "Message-Passing Systems", or using distributed objects implemented in CORBA, Java RMI, or the equivalent. In the following sections, we'll examine both of these possibilities.

7.2.6.1. Message passing with the data server

To provide access to remote data using message passing, we first need to establish a data server that can respond to messages from clients and access the data referenced in the messages. This is easily accomplished using existing data objects such as those we saw earlier, by simply developing a set of messages that can talk to these data objects on the data server. We could have a GetResourceMsg class, for example, which is a message passed from a client to the data server asking for the data associated with a given resource. If we model this message-passing system after the Message and MessageHandler classes described in Chapter 6, "Message-Passing Systems", then the Do() method on GetResourceMsg might look something like this:

public boolean Do() {
    boolean success = true;
    // Get the resource ID from the message arguments
    Integer rid = (Integer)argList.elementAt(0);
    // Connect to the database.
    Connection c = DriverManager.getConnection(...);
    // Create the referenced data object from the database
    SchedResource res = new SchedResource(rid.intValue(), c);

    GetResourceResp grr = new GetResourceResp();
    // If we were able to find the resource in the database,
    // return a success flag, along with the resource data.
    if (res.isValid()) {
        grr.addArg("success");
        grr.addArg(res.getName();
        grr.addArg(new Integer(res.getType()));
        grr.addArg(new Float(res.getSize()));
    }
    // If the database query failed, return a failure flag
    else {
        grr.addArg("failed");
        success = false;
    }

    handler.sendMsg(grr);
    return success;
}

A GetResourceMsg object would be created by the MessageHandler on the data server when a message of this type was received from a data client. This Do() method would then be invoked. A SchedResource object is created, which pulls the relevant data from the database using JDBC, and the resource data is formatted into a return message to the client. On the client, we could provide message-passing versions of our data objects. These could be modeled closely after the JDBC-enabled data objects, with the implementation altered to use messages for data transactions rather than the JDBC API. For example, the constructor for a message-passing version of our SchedResource object could be implemented as shown in Example 7-7. The constructor calls an updateFromDbase() method, just as the JDBC data object does, but this method generates a GetResourceMsg message, sends it to the data server, then parses the response to extract the data.

With the data tier split in this way--message-passing objects on the client talking to JDBC-enabled data objects on the data server--the issue of data caching becomes more complicated. Data can now be cached in both the client agent and in the objects on the data server, if we so desire. Some of the same issues are involved in determining the proper level of caching on both levels of the data tier:

• How frequently is each data item updated on the next data level (data server or DBMS) by agents other than our own?

• What is the caching scheme used in the next data level?

• What is the nature of our connection to the next data level in terms of bandwidth and reliability, and what effect does this have on the effective data throughput from the database itself to the end user?

• How frequently can we update the local cache without imposing unreasonable overhead on data access times?

Other issues are specific to each level of the data tier. On the data server:

• Is the data server the single entry point for data clients (e.g., a multithreaded data server), or are multiple data servers servicing data transactions?

And on the data client:

• Are we the only client accessing this data server? Can we use network bandwidth issues alone to decide our caching scheme, or do we have to consider updates from external entities as well?

7.2.6.2. Distributed objects from the data server

Another option for splitting the data tier between the client and the data server is to use distributed objects. In this situation, data object stubs on the client use distributed object protocols to interact with data objects on the server. The server-side data objects then access the database directly, using JDBC or some other RDBMS interface library. In this section we'll develop an RMI interface to our scheduling data objects.

With the JDBC-enabled data objects for our scheduling application in hand, we need to perform the following steps to enable RMI access to them:

• Create stub interfaces to the data objects.

• Update the existing data objects so that they implement the stub interfaces and the appropriate RMI interfaces.

• Provide a registered server object that can act as a schedule object server. A stub to this object will be accessed by the client through the registry, and then remote objects will be generated through its interface.

Let's define the client stub interfaces to our data objects first. Each data object that we defined in the previous section will need a remote stub; the interfaces to the remote objects are shown in Example 7-8. The RMIDatabaseItem interface mirrors the DatabaseItem class, and exports the updateToDbase() and updateFromDbase() methods. Derived from this we have stub interfaces for each of our data objects: RMISchedResource, RMISchedTask, RMITimeConstraint, and RMIResAssignment. Each of these interfaces exports the getXXX() and setXXX() methods from the schedule data classes.

Now we need to provide the client with the ability to create new data objects. The only remote objects that the client can request directly from the remote server are objects that have been explicitly registered with the RMI registry running on the server. So we need to provide an object broker, similar to the object activation facilities provided in CORBA by the server's ORB. The RMISchedDbase interface shown in Example 7-9 serves this function by providing methods for creating new resources, tasks, constraints, and resource assignments in the database, and for retrieving existing data records from the database. The results are provided in terms of the remote stubs in Example 7-8. These methods replace the constructors and static utility methods in Example 7-7.

Now we need to turn to the server implementations of these interfaces. The server implementations of our RMI data objects are nearly identical to the first versions we discussed earlier, except that now they derive from the RMI UnicastRemote-Object class, and implement the corresponding RMI stub interface. For example, the RMI-enabled version of the SchedResource class is shown in Example 7-10, and is called SchedResourceImpl. The implementation is nearly identical to that of the SchedResource class shown in Example 7-3, except that each RMI-exported method throws RemoteException, and the class implements its RMI stub counterpart as well as the DatabaseItemImpl base class. Also, the DatabaseItemImpl class, which isn't shown here, extends the UnicastRemoteObject class, so all of its subclasses become UnicastRemote-Objects as well.

The implementation of the RMISchedDbase is the SchedDbaseImpl class shown in Example 7-11. In addition to implementing the utility methods that create and retrieve schedule objects from the database, the SchedDbaseImpl class also has a main() method that registers an instance of the class with the local RMI registry under a name passed in as a command-line argument. This allows clients to obtain an RMI stub to the object by looking up the object by name through their local registries.

A client that uses RMI to look up objects in the database is shown in Example 7-12. This particular client is requesting an RMISchedDbase object registered under the name "ScheduleDataServer" on a remote host:

(RMISchedDbase)Naming.lookup("//my.server/ScheduleDataServer");

Before this client can access the object, we have to register an instance of the SchedDbaseImpl class on the remote data server by invoking its main() method. This can be done by executing the following rmiregistry command (the commands shown are for a Unix server):

my.server% rmiregistry &
my.server% java dcj.examples.dbase.SchedDbaseImpl ScheduleDataServer
Registering SchedDbaseImpl with naming service as ScheduleDataServer
ScheduleDataServer ready.
my.server%

Then the client scheduler can be run (the commands are shown for a Windows-based client):

C:\SCHED> java dcj.examples.dbase.RMIScheduler
Got 17 resources.

C:\SCHED>

7.1. An Overview of JDBC		7.3. Multi-Database Applications

Copyright © 2001 O'Reilly & Associates. All rights reserved.