HomeDigital EditionSys-Con RadioSearch Java Cd
Advanced Java AWT Book Reviews/Excerpts Client Server Corba Editorials Embedded Java Enterprise Java IDE's Industry Watch Integration Interviews Java Applet Java & Databases Java & Web Services Java Fundamentals Java Native Interface Java Servlets Java Beans J2ME Libraries .NET Object Orientation Observations/IMHO Product Reviews Scalability & Performance Security Server Side Source Code Straight Talking Swing Threads Using Java with others Wireless XML
 

Today, Web database publishing is an important way to publish information electronically. It actually combines the advantages of two important technologies database and the Web. Standard database interfaces such as ODBC[1] (Open Database Connectivity) are used to provide an abstraction layer so that application programs can access data in a uniform and vendor-neutral way. ODBC is a standard API interface introduced by Microsoft. Each database requires a driver that converts SQL calls into the respective database's native calls and then performs the database interactions. Currently, there are many databases that come with ODBC drivers such as Oracle, Sybase, MS SQL, etc.

Modeled after ODBC, JDBC[2] (Java Database Connectivity) provides a standard set of classes and interfaces to allow Java[3] applications and applets to access the databases. We all know that Java applets inject dynamics and liveliness into the Web content. With the release of JDBC, Java developers can now enhance the information repository and retrieval functions of their Java application or applets by integrating them with their back-end database management system. In fact, the Java database solution on the Web through JDBC provides a powerful means to organize and distribute information across different platforms on the Web.

Unlike standalone Java applications, in order to integrate Java applets with the back-end databases through JDBC there are a number of crucial points to consider. In this article, we will discuss how Java applets can access ODBC-compliant databases such as MS SQL based on the Java three-tier database solution. In addition, we will illustrate the solution with an inquiry service of a Web-based book kiosk system for a possible Internet/Intranet setup in an organization.

Why a Three-tier Java Database Solution?
Consider the possible setup for a Web content provider such as software vendors, publishers, financial/banking companies, etc. (shown in Figure 1). The servers and their respective functions are:

  • Internet server - to allow customers/clients with Internet access to make use of the services provided
  • Intranet server(s) - to provide services for internal usage
  • Database server - to provide back-end database support for the services in the Internet/Intranet servers. Most of the time, the database server does not reside in the same host as the Web server for performance and security reasons.

Figure 1
Figure 1: A possible Internet/Intranet setup

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.

Figure 2
Figure 2: Two-tier Java Database Connection

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.

Figure 3
Figure 3: Three-tier Java Database Connection

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.

Figure 4
Figure 4: Three-tier solution for the Fantastic Reading book kiosk

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.

Figure 5
Figure 5: Interactions among applets, HTTP server, intermediary server and SQL server

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:

  • Weblogic native JDBC Driver [6]
  • JDBC-ODBC bridge [2] on top of the ODBC manager
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
About the Author
Ms. Siet-Leng Lai is the creator of a concurrent object-based language on parallel machine as well as clustered workstation environment and a multi-tasking operating system on DOS. She has more than four years of IT industrial experiences. She is now conducting research into Internet/Intranet middlewares using Java.

Mr. 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.

	

Listing 1.
 
package kiosk; 

import java.awt.*; 
import java.net.*; 
import java.applet.*; 
import java.util.Properties; 
/* if you are using JDK 1.02, import xjava.sql.* 
 * if you are using JDK 1.1 import java.sql.* 
 */ 
import xjava.sql.*; 
import weblogic.common.*; 

/* inquiry.java 
 inquiry service for Fantastic Reading book kiosk 
 using Weblogic native JDBC driver 
 */ 

public class inquiry extends Applet { 

  T3Client t3 = null; 
  Connection conn = null; 

  TextArea ta; 
  Label lb; 
  TextField searchfield; 
  Button submit_button; 

  public void init() { 

    Panel p1; 

    setLayout(new BorderLayout()); 

    lb = new Label("Title to be searched : "); 
    searchfield = new TextField(30); 

    ta = new TextArea(12, 50); 

    submit_button = new Button("Go"); 

    p1 = new Panel(); 
    p1.add(lb); 
    p1.add(searchfield); 
    p1.add(submit_button); 
  

    add("North", p1); 
    add("South", ta); 

  } 
  

  public boolean action(Event e, Object obj) {  

    String search_string, result_string; 

    if (e.target == submit_button) 
    { 
      search_string = searchfield.getText(); 
      ta.setText(""); 

      if (search_string.length() == 0) 
      { 
        ta.setText("Pls enter a search string!"); 
        ta.show(); 
        return true; 
      } 

      result_string = search_title(search_string); 
      if (result_string != null) { 
         ta.appendText(result_string); 
         return true; 
      } else { 
         return false; 
      } 
    } 

    return false; 
   
  } 

  public void stop() { 
    try {conn.close();}    catch (Exception e) {;} 
    try {t3.disconnect();} catch (Exception e) {;} 
  } 

  void establish() { 

    try { 

      t3 =  new 
        T3Client("t3://cobam.ccs.np.ac.sg:7001"); 
      t3.connect(); 
       
      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"); 
       
      Class.forName("weblogic.jdbc.t3client.Driver"); 
      conn = 
      DriverManager.getConnection("jdbc:weblogic:t3client", 
         t3properties); 
       

    } catch (Exception e) { 
      e.printStackTrace(); 
    } 

 } 
       

 String search_title(String title_string) { 

    String str; 
    StringBuffer sb1 = new StringBuffer(); 
    int counter; 

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

    if ((t3 != null) && (conn != null)) { 
       str = new 
        String("select * from book where title like '%"+ 
             title_string+"%'"); 
         
        try { 
          Statement stmt = conn.createStatement(); 

          stmt.execute(str); 
          ResultSet rs = stmt.getResultSet(); 
          counter = 0; 

          while(rs.next()) { 
             counter++; 
             sb1.append(counter+ 
               ") Catalogue No. :  "+ 
               rs.getString("catalogue_no")+ 
               "\n"+ 
               "~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n"+ 
               "Title     :"+ 
               rs.getString("title")+ 
               "\n"+ 
               "Author    :"+ 
               rs.getString("major_author")+ 
               "\n"+ 
               "Publisher :"+ 
               rs.getString("publisher")+ 
               "\n"+ 
               "Unit Price:"+ 
               rs.getString("unit_price")+ 
               "\n\n"); 
             
           } 

           if (counter == 0) { 
              return("No matching title !"); 
           } else { 
              return(sb1.toString()); 
           } 

        } catch(Exception exception) { 
          return("SQL query Error"); 
        } 

    } else { 
      return(null); 
    } 

  } 

} 

Listing 2.
 
<html> 
<head> 
<title>Fantastic Reading Book Store</title> 
</head> 

<body> 
<img src=logo.gif> 
<APPLET CODEBASE="http://cobam.ccs.np.ac.sg/" 
CODE="kiosk.inquiry.class" 
width=500 height=240> 
</APPLET> 
</body> 
</html> 

Listing 3.
 
package kiosk; 

import java.awt.*; 
import java.net.*; 
import java.applet.*; 
import java.util.Properties; 
/* if you are using JDK 1.02 import xjava.sql.* 
   if you are using JDK 1.1 import java.sql.* 
 */ 
import xjava.sql.*; 
import weblogic.common.*; 

/* inquiry.java 
 inquiry service for Fantastic Reading book kiosk 
 using JDBC-ODBC Bridge 
 */ 
  

public class inquiry_b extends Applet { 

  T3Client t3 = null; 
  Connection conn = null; 

  TextArea ta; 
  Label lb; 
  TextField searchfield; 
  Button submit_button; 

  public void init() { 

    Panel p1; 

    setLayout(new BorderLayout()); 

    lb = new Label("Title to be searched : "); 
    searchfield = new TextField(30); 

    ta = new TextArea(12, 50); 

    submit_button = new Button("Go"); 

    p1 = new Panel(); 
    p1.add(lb); 
    p1.add(searchfield); 
    p1.add(submit_button); 
  

    add("North", p1); 
    add("South", ta); 

  } 
  

  public boolean action(Event e, Object obj) {  

    String search_string, result_string; 

    if (e.target == submit_button) 
    { 
      search_string = searchfield.getText(); 
      ta.setText(""); 

      if (search_string.length() == 0) 
      { 
        ta.setText("Pls enter a search string!"); 
        ta.show(); 
        return true; 
      } 

      result_string = 
          search_title(search_string); 

      if (result_string != null) { 
         ta.appendText(result_string); 
         return true; 
      } else { 
         return false; 
      } 
    } 

    return false; 
   
  } 

  public void stop() { 
    try {conn.close();}    catch (Exception e) {;} 
    try {t3.disconnect();} catch (Exception e) {;} 
  } 

  void establish() { 

    try { 

      t3 = new 
       T3Client("t3://cobam.ccs.np.ac.sg:7001"); 
      t3.connect(); 
       
      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"); 
       
      Class.forName("weblogic.jdbc.t3client.Driver"); 
      conn = 
       DriverManager.getConnection("jdbc:weblogic:t3client", 
          t3properties); 

    } catch (Exception e) { 
      e.printStackTrace(); 
    } 

 } 
       

 String search_title(String title_string) { 

    String str; 
    StringBuffer sb1 = new StringBuffer(); 
    int counter; 

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

    if ((t3 != null) && (conn != null)) { 
        str = 
          new String("select * from book where title like '%"+title_string+"%'"); 
         
        try { 
           Statement stmt = conn.createStatement(); 

           stmt.execute(str); 
           ResultSet rs = stmt.getResultSet(); 
           counter = 0; 

           while(rs.next()) { 
             counter++; 
             sb1.append(counter+ 
                ") Catalogue No. :  "+ 
                rs.getString("catalogue_no")+ 
                "\n"+ 
                "~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n"+ 
                "Title     :"+ 
                rs.getString("title")+ 
                "\n"+ 
                "Author    :"+ 
                rs.getString("major_author")+ 
                "\n"+ 
                "Publisher :"+ 
                rs.getString("publisher")+ 
                "\n"+ 
                "Unit Price:"+ 
                rs.getString("unit_price")+ 
                "\n\n"); 
             
           } 

           if (counter == 0) { 
              return("No matching title !"); 
           } else { 
              return(sb1.toString()); 
           } 

        } catch(Exception exception) { 
          return("SQL query Error"); 
        } 

    } else { 
      return(null); 
    } 

  } 

} 

Listing 4.
 
<html> 
<head> 
<title>Fantastic Reading Book Store</title> 
</head> 

<body> 
<img src=logo.gif> 
<APPLET CODEBASE="http://cobam.ccs.np.ac.sg/" 
 CODE="kiosk.inquiry_b.class" 
width=500 height=240> 
</APPLET> 
</body> 
</html> 


 

All Rights Reserved
Copyright ©  2004 SYS-CON Media, Inc.
  E-mail: [email protected]

Java and Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. SYS-CON Publications, Inc. is independent of Sun Microsystems, Inc.