Expeditor Developers: Data access application development best practices

There are typically two reasons for creating an application that needs to make use of database technologies. One reason for using database technologies is to create a new lightweight client application. Another reason is to adapt an existing server-side application for use as a lightweight client application.

Enterprise Java™ developers who have written applications requiring the use of JDBC typically rely on obtaining access to the database through a DataSource object. The DataSource object will have already been bound by the Java Naming and Directory Interface (JNDI). The application developer simply needs to locate the DataSource using JNDI, and obtain from the DataSource a connection to the database. The application deployer cooperates with the system administrator to define the mapping and access to a physical database, causing the DataSource object to be bound into JNDI.

Also, in the server environment, database management often falls within the administration realm of a database administrator. A database administrator is typically responsible for creating the physical database, partitioning the database for use among several applications, creating tables and indexes for a particular application, managing the access rights to a database, and monitoring the performance of the database. When the application developer has successfully located a DataSource using JNDI, the developer can obtain a Connection, and begin performing actions against the database.

When adapting a server-side application to run on a client, the application developer often takes more responsibility, performing some roles typically handled by a database administrator. The application developer might be responsible for initially creating the database, creating tables, and configuring the database. In addition, depending upon the set of components available on the client, the application developer might also be responsible for creating the DataSource objects, either directly within the application, or within a JNDI environment on the client. If no JNDI support is available in the client runtime environment, the application developer should use the standard JDBC 3.0 javax.sql.DataSource creation methods. Regardless of how the DataSource object is created or located, application developers still obtain Connection objects from the DataSource, and create Statement objects from the Connection.

In order to ensure minimum changes to applications that use JDBC, some best practices should be followed:

Use DataSource objects as they exist across the JDBC 3.0 specifications; DriverManager does not exist in the JDBC Optional Package for CDC/Foundation Profile. Limiting the JDBC application usage to the JDBC Optional Package for CDC Foundation Profile subset of JDBC 3.0 provides the most portability from desktops to devices.
Isolate the DataSource creation or location to a single class. Later, if the environment changes, a change needs to be made in only one place.
Ensure you close all objects (ResultSet, Statement, Connection) when you have completed work. Servers typically include more sophisticated connection management known as connection pooling, which can accommodate mismanagement of connections. However, on the clients, application developers are directly responsible for the life cycle of the objects. By promptly closing objects, memory requirements will remain at a minimum.
Do not hard code a schema identifier during statement creation. DB2^® Everyplace^® does not support schema names, so all statements would need to be changed if an application needed to be migrated to DB2 Everyplace.
The SQL statements supported by a particular database might not match the statements used within an existing application. The application either needs to adapt statements depending upon database type, or obtain statement information from externalized information.

Lotus^® Expeditor includes the Lotus Expeditor Toolkit, which provides Target Profile support. These Target Profiles simplify the creation and configuration of database application projects, enabling you to select the target-embedded database, and provide automatic management of the requisite JDBC libraries. In an Eclipse SDK plug-in development environment, you can use the standard plug-in dependency tooling to provide the necessary access to DB2e and Cloudscape™ libraries during database application development. Eclipse is an award-winning, open source platform for the construction of powerful software development tools and rich desktop applications.

While for Lotus Expeditor development we recommend the use of the DataSource interface for JDBC programming, there may also be cases where the DriverManager interface must be used for legacy code. If this is the case, please note that while in previous releases of the Lotus Expeditor runtime, the platform would load the database driver classes at platform startup, this is no longer the case. The method for accessing a database via JDBC using the DriverManager interface involves 2 steps:

Loading the database driver via a Class.forName() method call.
Connecting to the database.

The DriverManager class to be loaded for IBM^® Cloudscape is org.apache.derby.jdbc.EmbeddedDriver and the DriverManager class to be loaded for DB2 Everyplace is com.ibm.db2e.jdbc.DB2eDriver.

For more information on JDBC programming please see the SUN JDBC tutorial at http://java.sun.com/docs/books/tutorial/jdbc/index.html.