Java Q&A

Cliff, vice president of technology of Digital Focus, can be contacted at [email protected]. To submit questions, check out the Java Developer FAQ Web site at http://www.digitalfocus.com/faq/.

In the past year, a lot has been written about the usefulness of the Web as an alternative to conventional custom software-based solutions for distributing business and other information. In the traditional approach, a vertical application is programmed using embedded SQL or some other technique, and application programs that access the database are distributed to users within a corporation. One disadvantage of this approach is that the application has to be redistributed every time it is changed. The web-based alternative instead relies on the simple (but powerful) HTML publishing model for the user interface and on centrally maintained CGI programs for accessing the database and making the data available to web pages.

While this approach is an improvement in cost and flexibility over the deployed application approach -- and much has been written about the cost advantages of doing things this way -- it suffers from the very limited feature set provided by the CGI protocol, which is primarily a transaction-oriented mechanism in which a server program accepts a parameter stream from the user's web-page form, then generates a new web page as output, and possibly accesses a database along the way. Tools such as Javascript make it possible to perform local processing prior to sending the query to the database -- for example, to do checking on fields -- but Javascript is a poor choice for large applications. Because it does not provide a mechanism for reusing code, commonly used routines must be cut-and-pasted into each place they are needed.

A better approach is to utilize the distributed programming model of Java, so that we can have the best of both worlds: a flexible and highly interactive (even real-time) UI, but with the low cost of a web-based approach. The need to use CGI, which is notoriously difficult to debug, is also obviated.

JDBC

Java 1.1 will incorporate a package called "java.sql," which provides a standard Java binding for accessing an ODBC-compliant database. This package is available now as a beta release from Sun's Java site (http://java.sun.com/). To use it, all you need is a JDBC/ODBC-compliant driver. Eventually, such drivers will be available in Java-only versions. At the moment, most are implemented with Java native methods which, in turn, call ODBC driver libraries on a platform-specific basis. Since native methods cannot be called from within a remotely loaded applet in most browsers, this limits these drivers to applications that run on the same host as the database. This is not practical for a web application.

The solution implemented by many driver providers is to use a special server program that resides on the same machine as the database and fields remote JDBC requests, then calls the required ODBC driver functions. To achieve this, the JDBC driver (which runs within the applet) must be designed to communicate with this special server program.

In this column, I'll use a driver provided by XDB Systems (available from http://www.xdb.com/). All of the code will use the standard JDBC packages. The only vendor-specific impact on the program is the instantiation of XDB's driver. Most vendors, including XDB, also provide a rich set of user-interface components, written in Java, to assist in the construction of an interactive web-based database application. Since those tools are vendor specific, however, I won't use them for this column and will instead concentrate on the JDBC aspects.

A Query Tool

For a demonstration applet, I'll create a query tool that allows you to enter any arbitrary SQL query and displays the resulting table in a separate browser window.

I could simply sprinkle JDBC calls throughout the code directly, the way you might have embedded SQL wherever you want to make a query. Database vendors seem to think that developers want to use their interface in this way. It is not good programming practice, however. For example, suppose you want to develop most of the application independent of the database, and merely have a test stub, which simulates a database connection. If there are database calls everywhere, it will be impossible to test the application as a whole without access to the database -- a poor strategy. It is better to encapsulate database access in a single class. You could then have a test version of this class and a real version. The test version's methods could return simulated data, whereas the real version's methods return live data.

I've encapsulated my use of java.sql into a class called QuerySvcsImpl that implements an interface called QuerySvcs. For testing, I am free to have a test version of this class, called QuerySvcsTestImpl.

QuerySvcs (see Example 1) specifies these methods. Each of these may throw java.sql.SQLException, which would indicate an error was detected by the driver. These methods are implemented in QuerySvcsImpl. The initConnection() method checks if a driver has been instantiated yet (static component), and if one has not, calls new JetDriver() -- XDB's driver object. This is the last time you have to refer to the client-side driver object. initConnection() also performs Example 2.

The java.sql.DriverManager class has a static method called getConnection() (Example 2) that takes three parameters: a dataset URL, database user id, and password. The URL is the concatenation of the driver protocol, host, and database dataset name. JDBC defines a driver-protocol syntax composed of two parts separated by a colon. The first part is simply "jdbc," while the second part specifies the name of the driver and is vendor specific. In this case, the full protocol is "jdbc:jet.jettp:".

The host is normally the Internet DNS name or the IP address of the database host machine. For example, it could be www.somewhere.com, or 205.123.456.789. If you are testing your database on your own machine, however, you will probably want to specify "loopback" if your machine's TCP/IP stack has loopback support (Windows 95 does not; NT does). However, note that Netscape Navigator requires you to have a real connection, and will not like loopback. If you are testing on Windows 95 and only have a dial-up connection, you can put code into your applet to get your dynamic IP address with the java.net.InetAddress.getLocalHost() method and use this IP address in your getConnection() call -- all this to make the browser happy!

The getMetaData() method obtains a database meta data object that can be used to query information about the status of the database. For example, from a DatabaseMetaData object you can obtain the dataset URL, the database product name and version, and the name and version of the driver.

The select() method executes a SQL query against the database; see Example 3. As you can see, you first have to create a java.sql.Statement object before you can perform a query. You then call executeQuery() against the Statement object, passing it the SQL string to evaluate. The result that is returned is a java.sql.ResultSet, which is essentially a currency pointer on the query result. No data is actually returned to the client until the ResultSet is traversed.

vselect() does all this, then traverses the ResultSet, and converts it into a java.util.Vector object, with each element of the vector consisting of an array of objects corresponding to the fields in each row of the result. This method is useful if you want to get the whole result and then do something with it, like graph it.

Example 4 is the core of vselect(). Thus, to find out how many columns are contained in the result, you can get the ResultSetMetaData object and then call getColumnCount(). Note that many of the ResultSetMetaData methods can only be called once during the life of any ResultSet instance.

What you've discovered is that the ResultSet.getObject() method involves a lot of overhead and results in very slow performance. Alternatively, you could use the JDBC type-specific methods of the form get<type>();. The disadvantage is that when using these, you must anticipate the type of each column in the result.

The JDBC standard also provides for parameterized precompiled statements (java.sql.PreparedStatement), which will result in faster performance if a select is executed within a loop.

To put all this together into a query tool, I wrote an applet, Client.java (see Listing One,that instantiates a text field for capturing the input query from the user. A button handleEvent() method retrieves this text, and passes it to my QuerySvcs.select() method, which returns a ResultSet. I then call toHTML() with this result set, to generate HTML output for the results window. I use the "javascript:" content protocol to display the generated string of HTML statements: getAppletContext().showDocument(querySvcs.toHTML(resultSet), "OUTPUT_WINDOW");. Figure 1 is the completed applet and Figure 2 is a results table generated by it.

Notes about Data Types

Table 1 lists some of the more useful datatypes. In addition, the Microsoft SQL types DATETIME and SMALLDATETIME are mapped by most ODBC drivers to the ODBC type TIMESTAMP, which maps to java.sql.Timestamp. While you cannot create more than one TIMESTAMP field in a record, there is no such restriction on the DATETIME types, and so you can, from your JDBC program's point of view, have more than one java.sql.Timestamp object in a record. This is useful for storing java.util.Date objects, since java.sql.Timestamp derives from java.util.Date, and because some databases do not support DATE and TIME, but instead have a DATETIME type. Simply use the java.sql.Timestamp type in your program instead of java.util.Date, and you will be okay -- the Timestamp object should work wherever a Date object is required. The only consideration is that java.sql.Timestamp overrides java.util.Date's date parsing and toString() methods, and so it has different (more TIMESTAMP-like) rules for how it parses and displays date and time strings. Note that java.util.Date objects can only represent dates as far back as 1970; java.sql.Timestamp objects have this same limitation. If you want to represent dates beyond this, you may have to add an offset. Beware of a bug in the parsing of java.util.Date objects, which has been fixed in Netscape's version of Java, but not in Internet Explorer. This bug causes a date expressed as 3/1/95 to be interpreted as April 1, 1995 (no joke).

The Bignum datatype is very inefficient, and should be used only where monetary values are represented or where exact precision is required.

The Browser Factor

Sun made a mistake in making the sql package part of package java. Package java.sql will be a standard component of package java when JDK 1.1 is released. In the meantime, however, the fact that this package is part of package java makes it impossible to load it into Netscape Navigator or Internet Explorer in a normal manner. A browser generally will not let you download any component of package java from a remote source, for security reasons. Since package java.sql is not incorporated into either of the aforementioned browsers (and probably will not be until after JDK 1.1 is released), this means that package java.sql is effectively inaccessible to a browser.

A fairly ugly but usable workaround, until 1.1 is released, is to add java.sql to the browser's own class library. In the case of Netscape Navigator, as of this writing, these classes are hidden in a ZIP file called java_30. To put java.sql into this ZIP archive, you must rename the archive to java_30.zip, open it with a tool such as WinZip, add java.sql immediately after the other java packages in the file, and then rename the file back to its original name. For Internet Explorer, the procedure is similar, except that the ZIP file is called classes.zip, and it is in the \Windows\java\classes directory.

Now that you have added java.sql to your java_30, users of your applet will have to replace their Netscape java_30 file with yours. You can put it on the web site that serves the applet, with instructions about where to put it after it is downloaded. While this is a little bit cumbersome, it is workable for an intranet application.

An even better alternative is to use a product such as Marimba's Castinet (http://www.marimba.com/), which automatically installs applets on a client system. Castinet does not have the same restrictions that browsers have, because the assumption is that the user has approved the applet and trusts its source. This kind of setup allows you to download new components of package java (such as java.sql).

The complete code for the query tool is available electronically (http://www.digitalfocus.com/ddj/code/; or see "Availability," page 3).

Conclusion

The JDBC interface, combined with a suitable driver, makes it easy to create portable database applications with Java. As of this writing, some installation is required for users to use JDBC applets within a browser, but this obstacle will likely disappear with the next major round of browser releases. There are also alternatives to browsers.

JDBC makes it possible to make enterprise data available, from dynamic and interesting Java applications, without requiring the developer to use precompilers or to write stored procedures: The full power and flexibility of Java is available. Further, these applications can be used with any database for which there are JDBC/ODBC or pure JDBC drivers, and most ODBC driver vendors are developing or now provide JDBC drivers. Java has become a formidable business tool.

DDJ

Listing One

/* Copyright (c) Digital Focus, 1996, 1997. * This code may be used for non-commercial purposes.
 * Digital Focus gives no warrantee or guarantee.
 */


</p>
import java.applet.*;
import java.awt.*;
import java.io.*;
import java.util.*;
import java.net.*;


</p>
/** Query Tool.  */
public class Client extends Applet
{
    TextArea queryWindow = new TextArea(3, 40);
    Button go = new Button("Execute");
    QuerySvcsImpl querySvcs;


</p>
    /** Initialize the applet. */
    public void init()
    {
   add(queryWindow);
  add(go);
   // Instantiate query services
   try
   {
  querySvcs = new QuerySvcsImpl(getParameter("protocol"), 
getParameter("host"), getParameter("dsn"), getParameter("uid"), 
getParameter("pwd"));
   }
   catch (java.sql.SQLException ex)
   {
  ex.printStackTrace();
  showStatus(ex.getMessage());
  return;
   }
    }
    /** Handle the button press event. */
    public boolean handleEvent(Event e)
    {
   if ((e.target == go) && (e.id == Event.ACTION_EVENT))
   {
  java.sql.ResultSet r = null;
  String html = null;
  URL url = null;


</p>
  try
  {
 r = querySvcs.select(queryWindow.getText());
 html = querySvcs.toHTML(r);
 System.out.println(html);
 url = new URL("javascript:'" + html + "'");
 getAppletContext().showDocument(url, "results_window");
  }
  catch (Exception ex)
  {
 ex.printStackTrace();
 showStatus(ex.getMessage());
 return true;
  }
  return true;
   }
   else return super.handleEvent(e);
    }
}


</p>


</p>

Back to Article