Create your own type 3 JDBC driver, Part 2

Compile, deploy, and access data using your custom-built JDBC driver

In Part 1 of this three-part series, we showed how to construct a type 3 JDBC driver, named JWDriver, that employed RMI (Remote Method Invocation) to let the client talk to the middle tier. Continuing with JWDriver's development, in this article we explain how to compile the driver, then deploy it in a Web server environment. Further, we explain the Java files' directory structure for both the client and server tiers, then we run the driver's server tier, giving any client program access. We also include a sample Java applet (deployed on the Web server) to demonstrate how the driver can access data.

This article consists of three sections: compiling, deployment, and how to create a sample Web application that employs the driver.

Compile the driver

To compile the driver, first download Extract the files, then place them into a directory. You must compile the driver on a machine with a complete Sun Microsystems JDK (Java Developer Kit) 1.3. The file additionally contains the compile.bat file, which you should execute from the command line. The compile.bat file first sets the classpath to the local directory containing the Java files as:


The compile.bat file then compiles the driver's Java files for the server and client tiers using javac as:

javac com\jw\server\*.java
                                       javac com\jw\client\*.java

RMI requires Stub and Skel class files for all remote interfaces exposed by the server. Prepare those files by executing the rmic command on the driver's four remote interface implementations:

                                       -d . com.jw.server.RemoteDriverImpl
                                       rmic -d . com.jw.server.RemoteConnectionImpl
                                       rmic -d . com.jw.server.RemoteStatementImpl
                                       rmic -d . com.jw.server.RemoteResultSetImpl

Deploy the driver server tier in the form of a JWServerDriver.jar file containing the server class files. To prepare the JWServerDriver.jar file, run the cvf command as indicated in compile.bat file.

Deploy the driver

You deploy JWDriver on the Web server. As such, the driver's server tier runs as an RMI server on the Web server machine, as does the driver's client tier, the sample Java applet, and the Webpage that invokes the applet. The driver's client tier and the sample Java applet reside in the same Web directory in the Web server machine. The JDBC driver client tier automatically downloads from the server to the client machine.

We made the driver Internet ready by keeping its configuration settings on the server tier, eliminating the need for any settings at the client tier. Figure 1 depicts the driver's deployment in a typical three-tier setup, with each tier on a different machine.

Figure 1. The type 3 JDBC driver's deployment. Click on thumbnail to view full-size image.

In Figure 1, the Web application deploys on the Web server machine running Microsoft Internet Information Server (IIS). The driver's server tier, configured through a properties file, communicates with the Microsoft SQL Server database.

To deploy the driver on your system, you must:

  • Set the environment
  • Configure the driver in the Web server
  • Set up the database
  • Run the driver middle tier as an RMI server

Let's look at each step in more detail.

Set the environment

The driver requires each deployment machine to run specific applications:

  • Client-tier machine: Should run Microsoft Internet Explorer 5.5 or 6.0 and either Microsoft Windows NT or Microsoft Windows 2000.
  • Server-tier machine: Should run either Microsoft Windows NT or Microsoft Windows 2000, Microsoft Internet Information Server 4.0 or 5.0, and the Sun JDK 1.3 runtime environment.
  • Database-tier machine: You can deploy any database with JDBC-ODBC (Open Database Connectivity) bridge capabilities. In this article, the sample program employs Microsoft SQL Server 2000.

Configure the driver in the Web server

You deploy the driver on the Web server along with the TestApp Web application. Create a TestApp directory on the Web sever under C:\Inetpub\wwwroot. Then copy the JWServerDriver.jar into the C:\Inetpub\wwwroot\TestApp directory. Also copy the driver client-tier class files, along with the remote interface and stub class files generated during compilation, into the C:\Inetpub\wwwroot\TestApp directory (maintaining their directory structures).

The file contains the JDK RMI classes you'll copy into the C:\Inetpub\wwwroot\TestApp (again, maintaining their directory structures). Figure 2 shows the resulting directory structure.

Figure 2. Web application directory structure

Set up the database

Next, create in the Web server machine (using Data Sources (ODBC) from the Control Panel) an ODBC data source for the target database. Then create a data source named Pubs, configuring it to hit pubs as the default database in Microsoft SQL Server. (The pubs sample database comes with Microsoft SQL Server.) The sample Java applet accesses the authors table in the pubs database through the driver.

After configuring the ODBC data source, set the driver's settings in the file (available in the file)., which you should copy to C:\Inetpub\wwwroot\TestApp, contains the data source attributes DSN, User, and Password, with which the driver connects to the database. User and Password are the Microsoft SQL Server login and password:


Run the driver middle tier as an RMI server

The driver's server tier runs as an RMI server on the Web server machine. Before running the driver middle tier as an RMI server, set the classpath to:

set classpath=.;
                                       C:\Inetpub\wwwroot\TestApp\ JWServerDriver.jar;%claasspath%

Then start the Web server machine's RMI registry naming service by executing the following command on the command line, which launches the RMI registry window:

start rmiregistry

The driver's RMI server starts by running the following command on the command line in the C:\Inetpub\wwwroot\TestApp directory:

java com.jw.server.RemoteDriverImpl

Executing the above command displays the command-line window showing the running RMI server. Entering "Y" stops the RMI server. You've now successfully configured the driver, which is now ready for use in the client programs.

Sample Java applet

The sample Java applet, SampleApplet, retrieves the data from the database by executing a SQL statement. The applet executes in the client browsers through an HTML page. In this section, we explain how to develop and run the sample Java applet.

The SampleApplet uses JWDriver for all database communications. The SampleApplet's code adheres to the standard JDBC interface methods.

Load the driver class

The driver's JWDriver class loads in the applet's init() method:

                                       void init()
                                       btnGetData_ = new Button("Get Data");
                                       // Load the com.jw.client.JWDriver class in the client browser JVM
                                       catch(Exception ex)
                                       msg_ = ex.getMessage();

The init() method creates a Get Data button. When a user clicks on that button, the data is fetched from the database. The JWDriver class loads in the browser JVM by invoking Class.forName("com.jw.client.JWDriver"). The driver registers with the browser's JDBC driver manager when the JWDriver class loads.

Get the connection

SampleApplet's getData() method invokes whenever the user clicks on the Get Data button. The method first gets the connection from the JWDriver as:

public void getData()
                                       Properties props = new Properties();
                                       String server = getCodeBase().getHost();         
                                       // Prepare the JWDriver URL      
                                       String DriverProtocol = JWDriver.getURLPrefix() + server;
                                       Connection conn = DriverManager.getConnection(DriverProtocol,props);

The getData() method first retrieves the host machine name (the Web server running the driver RMI server) from which the applet originated. The getData() method then prepares the driver's requisite URL by appending the name of the Web server machine in the jdbc:JWDriver:. That URL passes to the DriverManager, which returns the connection. Figure 3 illustrates the internal process required to retrieve the connection.

Figure 3. JWDriver's connection sequence diagram. Click on thumbnail to view full-size image.

In Figure 3's sequence diagram, SampleApplet first calls the DriverManager.getConnection() method. The DriverManager asks each registered driver whether it subscribes to the driver URL starting with jdbc:JWDriver:. After the driver returns true, the DriverManager calls the JWDriver.connect() method, which extracts the Web server's name from the given URL string, then DriverManager gets the reference to the remote driver interface IRemoteDriver by checking the RMI naming service for the RemoteDriver object running on the Web server. Because you obtain the remote driver reference only once, all subsequent connection-retrieval calls need not get the remote driver reference. Obtain a remote connection from the remote driver and embed it into a local JWConnection object. The actual JDBC-ODBC bridge connection is held in the remote connection. Eventually, the JWConnection object instance returns to the SampleApplet.

Create the statement

The SampleApplet's getData() method, using the connection object, then creates the statement:

// Create the statement 
                                       Statement stmt = conn.createStatement();

The JWStatement object internally holds the reference to the remote statement, which holds the reference of a JDBC-ODBC bridge statement object.

Execute the query

The getData() method, again using the statement object, then executes a SQL query that retrieves an author's last name and author ID from the author table in the pubs database (you previously configured the driver RMI server to target the pubs database):

                                       Execute the select query (we are getting a JWResultSet)
                                       ResultSet rs = stmt.executeQuery("Select au_lname from authors 
                                       Where au_id = '409-56-7008'");

The JWResultSet object internally holds the reference to the IRemoteResultSet, which holds the reference of a JDBC-ODBC bridge ResultSet.

Iterate the JWResultSet

The getData() method then iterates through the JWResultSet to retrieve the author last name:

// Iterate over the JWResultSet
                                          msg_ = "Author Last Name: " + rs.getString(1);

Figure 4 shows the internal process required to iterate through the JWResultSet.

Figure 4. JWResultSet iteration sequence diagram

JWResultSet's next() method forwards the request to the Web server's RMI server through the IRemoteResultSet. The RemoteResultSetImpl associated with IRemoteResultSet retrieves the next data row from the JDBC-ODBC ResultSet. The retrieved data row is packed into an array of Object and returned to the client. In the client, the JWResultSet keeps this row as a class member. The next() method returns true. If JWResultSet has no more rows to iterate, then false returnes:

1 2 Page 1
Page 1 of 2