As shown in the above setup, Web-based applications using Java applets could be developed and run on Internet/Intranet servers even though they may be for different platforms. However, in order to integrate with the back-end database system, you need to consider a couple of points. First of all, due to security constraints, Java applets can only make network connections to the Web server from which they were downloaded. Second, untrusted Java applets cannot access native codes. Thus, the traditional two-tier configuration with a Java client connecting to a database server, as shown in Figure 2, becomes unfeasible.

In the two-tier configuration, the Java Client with JDBC API [2] connects to the database server through JDBC drivers and some form of database interface, if any, such as ODBC or a vendor-specific library.

In our given setup, shown in Figure 1, the database server does not reside in the same host as the Web server for performance and security reasons. Furthermore, most of the databases such as Microsoft MS SQL server do not come with JDBC drivers purely in Java codes. Thus, there is a need for a three-tier database solution with an intermediary server between the applets and the database server. Figure 3 shows the three-tier database connection for Java applets.

The intermediary server serves as a middle man between the Java applet and the SQL server. In addition, this middleware must be in the same host as the HTTP server. In this case, no native code is to be loaded over the applet side. The intermediary server, or middleware, is responsible for accepting the requests from the applets and making the necessary connections to the database server.

In this article, we will use a Web-based book kiosk system store that we have developed for Fantastic Reading bookstore to illustrate the above Java database solution. The intermediary server or middleware that we have used to glue the Java Applet, JDBC and MS SQL [4] database server together is a Weblogic[5] T3Server, shown in Figure 4. The T3Server acts as a multitier application server which manages the connection between the client and the T3Server and manages database interactions between the client and the database.

MS SQL server is used in our setup and Weblogic JDBC driver is used for the two-tier between the T3Server and database server. Besides this, the JDBC-ODBC bridge with ODBC manager can also be used for the connection between the T3Server and MS SQL server. However, the latter approach will introduce extra overhead for JDBC-ODBC translation. We will illustrate both approaches in connecting the intermediary server to the database server.

Anatomy of Database Query Using Three-tier Solution
In this section, we will use the inquiry service for our Web-based book kiosk system, the Fantastic Reading Bookstore, to illustrate the three-tier solution presented in the previous section.

The customer with Internet access could search for book titles available in the book kiosk by specifying a title in full or partially. Fantastic Bookstore keeps the book titles in a database table named book in the SQL database server (MS SQL server in this case). Upon clicking the Go button, you will see all the titles matching the search criteria displayed in the scrollable text area.

When a Java applet of the inquiry service is downloaded to the client side, it will connect first to the intermediary server (Weblogic's T3Server[5]) on the HTTP server and the book title that the user specified will be sent to the intermediary server. The intermediary server, upon accepting the request from the Java applet, will make the necessary connection to the MS SQL server on the other host by contacting the ODBC manager residing on the HTTP server. Figure 5 summarizes the sequence of events for the Java applet to access the back-end database.

Upon receiving the result of the SQL query, the intermediary server will pass the result back to the applet which will display it on the client's browser.

In the following section, we will describe the detailed implementation of the three-tier Java database solution by using two approaches to connect the intermediary server to the database server:

Listing 1 depicts the Inquiry class built for the book kiosk inquiry service by using Weblogic native JDBC driver; i.e., the first approach. The following classes need to be imported into the applet:

import java.util.properties;
/* if you are using JDK1.0.2, import xjava.sql.*
* if you are using JDK1.1, import java.sql.*
*/
import xjava.sql.* ;
import weblogic.common.*

All the GUI components are created and initialized in the init() method and they are kept to the minimum number for code readability. When you click on the Go button, the method search_title() will be invoked to perform the following operations: 1. Establish the connection[5] with the T3Server (refer to the method establish() in Listing 1), if it has not been established before.

if ((t3 == null) || (conn == null)) {
establish();

In other words, the T3 connection is created upon first transaction entry. Thus, subsequent retrievals will be faster because there is no need to re-establish the connection. However, this may use up the client context and connection over the T3Server. The steps in the establish() method are further explained below:
a. Create a T3Client object with the URL of the T3Server (t3://cobam.ccs.np.ac.sg:7001) as an argument. This includes the port where the T3Server is listening for the Client login request. Once the client is created, use the connect() method to open a connection to the T3Server.
b. Set up respective properties for connecting to the database.

The Properties object contains all the information for making a JDBC connection. It contains the details about how a JDBC client should access both the database and the intermediary server; i.e., T3Server. As shown in Listing 1, two properties, namely dbproperties and t3properties, are used. dbproperties is used to set parameters for the connection between the T3Server and the database server; e.g., user ("sa"), password (""), server name ("cobam").

Properties dbproperties = new Properties();

dbproperties.put ("user", "sa");
dbproperties.put ("password", "");
dbproperties.put ("server", "cobam");

Properties t3properties = new Properties();
t3properties.put ("weblogic.t3", t3);
t3properties.put ("weblogic.t3.dbprops", dbproperties);
t3properties.put ("weblogic.t3.driverClassName",
"weblogic.jdbc.dblib.Driver");
t3properties.put ("weblogic.t3.driverURL",
"jdbc:weblogic.mssqlserver");

t3properties sets the parameters for the connection between the jdbcT3Client and the database server with the T3Server. This includes T3Client object, dbproperties, the class name for the JDBC Driver between the T3Server and the database server, the URL of the two-tier JDBC driver between the T3Server and the database server. c. Identify and load the JDBC Driver over the client side and create the JDBC connection with the URL of the Weblogic JDBC driver and the properties object.

Class.forName("Weblogic.jdbc.t3client.Driver"); conn = DriverManager.getConnection("jdbc.weblogic.t3client", t3properties);

2. Create and execute the SQL statement; i.e., it will send the SQL query to the intermediary server.

... Statement stmt = conn.createStatement(); stmt.execute(str); ...

where the variable str contains the SQL statement

3. Get back the query result from the T3Server and append it to the text area to be displayed.
a. Create result set handle by invoking the getResultSet() method.
b. Loop through result set for the multiple rows returned from the T3Server.
c. Append them to the text area for displaying.

...
ResultSet rs = stmt.getResultSet()
...
while (rs.next()) {
...
sb1.append( ...
+ rs.getString("catalogue_no") +
...
+ rs.getString("title") +
... )
}

Listing 2 shows the HTML codes for the inquiry service using the first approach; i.e., Weblogic JDBC native driver. In order to load Weblogic specific classes, they must be loaded relative to the path specified by the codebase (i.e., http://cobam.ccs.np.ac.sg/, the home directory of the HTTP server). The applet code is specified relative to the codebase (i.e., kiosk.inquiry.class). kiosk is the package name specified in the Java applet and inquiry.class resides in the subdirectory kiosk of the home directory of the HTTP server.

Listing 3 depicts the Inquiry_b class built for the book kiosk inquiry service using the JDBC-ODBC bridge between the intermediary server and the SQL server. It is similar to that in the Weblogic native JDBC approach except for the following segment in the establish() method:

...
Properties dbproperties = new Properties();
dbproperties.put("user", "sa");
dbproperties.put("password", "");

Properties t3properties = new Properties();
t3properties.put("weblogic.t3", t3);
t3properties.put("weblogic.t3.dbprops", dbproperties);
t3properties.put("weblogic.t3.driverClassName", "sun.jdbc.odbc.JdbcOdbcDriver");
t3properties.put("weblogic.t3.driverURL", "jdbc:odbc:TestSQL");
...

The driver class is set to the JDBC-ODBC bridge driver and the driver URL is set to jdbc:odbc:TestSQL where TestSQL is the data source name set up in the HTTP host. The respective HTML document is shown in Listing 4, which is similar to Listing 3 except that inquiry_b is used in the <applet> tag.

Conclusion
In this article, a three-tier Java database solution for Web database publishing using a book kiosk inquiry service is discussed in detail. As we have shown, Java applets with JDBC access to the back-end database engine not only inject liveliness into the Web but also provide a powerful means to manage and disseminate information across the Internet.

Reference
1. Microsoft Press, ODBC 2.0 Programmer's reference and SDK guide (1994)
2. Javasoft JDBC specification http://splash.javasoft.com/jdbc
3. Javasoft Java specification http://www.javasoft.com
4. Microsoft MS SQL reference http://www.microsoft.com/sql
5. Weblogic product information http://www.weblogic.com/proddir.html
6. Weblogic native JDBC driver specification
http://www.weblogic.com/products/jdbckona.html

Related Links

▪ Figure 1

▪ Figure 2

▪ Figure 3

▪ Figure 4

▪ Figure 5

▪ Source Code

About Siet-Lang Lai
Siet-Leng Lai has published and presented at several international conferences,
and is now conducting research into XML, WML and WAP. Since 1990 she has been
a lecturer at the Center for Computer Studies, Ngee Ann Polytechnic, Singapore,
on subjects ranging from operating systems, system programming, networking,
Internet computing, and parallel and distributed computer systems to intelligent
building. Lai has also conducted short courses for the public on the above-
mentioned topics. She holds degrees from the National University of Singapore.

About Joo-Hwee Lim
Joo-Hwee Lim proposed novel neural networks techniques for timetabling and developed execution monitoring engine for a complex planner. He is now a senior software engineer leading an Agent Engineering Team researching agent-based software engineering and multi-agent systems using Java. The project is funded by Japan's Real World Computing Partnership.