Java Data Access

Java has become one of the most popular languages for application development over the past few years. With the maturation of the language has come a corresponding evolution of Java data access solutions, specifically Java Database Connectivity (JDBC) drivers. If you're going to be developing in Java and accessing a database, you need to know about JDBC. I thought it would be worthwhile to review the different categories of JDBC drivers and give you my thoughts on how to select a product.

JDBC is based on a concept similar to ODBC. The driver presents a standard application programming interface to a Java application and is responsible for initiating and managing a database connection. Recently, Sun Microsystems announced the JDBC 2.0 specification, which can be accessed at Describing the spec is beyond the scope of this column, but I want to discuss the four types of JDBC drivers defined by Sun, and describe their strengths and weaknesses.

When you obtain a Java development tool, you also receive a Type 1 JDBC driver, which is a JDBC-to-ODBC bridge by default. The driver uses ODBC to establish and manage the database connection. The driver can connect to any ODBC-compatible database, including Microsoft SQL Server 7.0. It also requires, however, that an ODBC driver manager is installed on the client. This effectively limits the Java application to a PC or server platform that can host an ODBC driver. This is inconsistent with the Java thin client, zero-deployment approach since someone -- usually a system administrator -- to install the ODBC software on the client workstation. The ODBC driver software is platform-specific and doesn't work with a downloadable applet. On the other hand, if your Java application is going to run on a PC and is going to access a local database, such as Access, dBase, FoxPro or Paradox, then this is a good solution.

The Type 2 JDBC driver is a JDBC-to-native database driver. This type of driver is typically supplied by relational DBMS vendors, such as IBM Corp., Oracle Corp., Sybase Inc. and Informix Corp. The Type 2 driver usually provides better performance than an ODBC driver because it takes advantage of the database's native network protocol and APIs. Similar to the Type 1 driver, someone must install the driver binaries on a PC or other type of fat client. Also, it is limited to a vendor-specific DBMS and can't support heterogeneous database connectivity. Most database vendors are moving away from Type 2 to either Type 3 or Type 4 drivers.

The Type 3 driver is the most flexible. It has a JDBC front-end that uses a database-independent network protocol that is translated to the target DBMS API. Multiple independent vendors provide Type 3 drivers that support both ODBC and native DBMS APIs, including BEA/WebLogic, Caribou Software, IDS, Merant, OpenLink, Symantec and WebXi. Some Type 3 drivers have a multitier architecture with a JDBC driver and a separate server that handles connections to the DBMS. The JDBC driver can run as a downloaded applet or on a Java servlet engine.

The Type 4 driver is similar to a Type 2 driver, except that it's written in Java. Most of the relational DBMS vendors' native drivers are Type 4. Since they're written in Java, they can be downloaded to a thin client or can run on a servlet engine. They typically are larger than Type 3 drivers, and can only interoperate with a single target DBMS. Since the vendor database-specific protocol typically doesn't support encryption, you must be careful about remote deployment, as well.

So which type of driver should you use? In most cases a Type 3 driver gives you the most flexibility and fewest administration headaches. Connection pooling -- which is part of the JDBC 2.0 specification -- is an important feature to look for since it can significantly reduce the overhead of opening, managing and closing connections to the DBMS. Type 1, 2 and 4 drivers can't provide connection pooling because they typically run in the context of a single client. If you need to query two or more databases, then you need a Type 3 driver.

If an application is going to be deployed outside a firewall, look for a distributed network design and security features such as encryption. For performance, look for statement or result set caching and native DBMS API support. Be sure the driver is fully thread-safe. Some drivers aren't thread-safe and they serialize data queries, which can kill performance. --Robert Craig is vice president of marketing at WebXi Inc. (Burlington, Mass.), and a former director at the Hurwitz Group Inc. Contact him at