Choosing a Java Database Connectivity driver

JavaSoft's Java Database Connectivity specification, released in May 1996, helped to standardize the growing selection of a very important class of Java tools that allow database access from Java applications. WebLogic introduced some of the first JDBC drivers available, and currently offers JDBC drivers that suit a broad range of developer needs. This paper discusses the JDBC specification, how JDBC has been implemented by various vendors, WebLogic's implementation, and gives some guidelines on how to choose the right kind of database driver for your application.

Contents

Why JavaSoft wrote the JDBC specification

Outline of JDBC functionality

Obtaining a Connection
Making a query and getting its results
Other JDBC classes

How JDBC has been implemented by vendors

The JDBC-ODBC bridge and ODBC access

Two-tier JDBC drivers and vendor client libraries

Two-tier Java-only drivers and proprietary protocols

Multitier JDBC implementations

Matching your application needs with a JDBC implementation

More on using JDBC in a multitier environment

Summary

More information on WebLogic products discussed in this paper

Why JavaSoft wrote the JDBC specification

JavaSoft saw the prospect of chaos in the marketplace and responded with the first release of the Java Database Connectivity (JDBC) specification in May, 1996. JDBC provided a vendor-neutral, universal framework for vendors to use in building tools that allow clients to access databases. The JDBC specification itself is just a collection of interfaces and abstract classes that each vendor must implement to write a JDBC-compliant driver.

The JDBC specification, guided by Rick Cattell and Graham Hamilton at JavaSoft, has structural and conceptual similarities with Microsoft's Open Database Connectivity driver, which Microsoft introduced in the 1980s to bring some conformity to client access to databases. Because many PC-based networks already use ODBC for their C and C++ clients, JavaSoft and Intersolv worked together to produce a JDBC-ODBC bridge, a layer that allows JDBC clients to connect via an ODBC client library, to make the transition from C/C++ clients to Java clients more expedient.

As Java has gained favor as the language of choice for Internet and intranet applications, the number of choices for JDBC drivers has grown. Developers writing database access client applications for heterogeneous networks like being able to write one application that will run anywhere; that shortens the development process and makes application maintenance much easier than in the traditional C/C++ world. In addition, other products that run on top of a JDBC driver, like WebLogic's dbKona, have been introduced to shield the developer from the low-level details of JDBC, following JavaSoft's hope that JDBC should be a foundation upon which higher-level, more abstract APIs can be built.

Outline of JDBC functionality

JDBC itself, like most driver specifications, is pretty low-level and functional. Here in a nutshell is a short review of how JDBC works.

JDBC uses a simple class hierarchy for database objects. The classes are contained in the java.sql.* package (which were released after JDK 1.0, but will be included in JDK 1.1). JDBC itself is just a specification; literally, the java.sql.* classes are descriptions of classes and methods that must be written in order to produce a JDBC driver. The functionality of JDBC is described here in terms of the normal progression through a database session, from connecting to disconnecting.

Obtaining a Connection

There are three classes that pertain to opening a connection to the DBMS: java.sql.DriverManager, java.sql.Connection, and java.sql.DatabaseMetaData. The DriverManager loads the appropriate JDBC driver, after which you can create Connection object. The Connection object is very important in JDBC; many other objects are constructed and many methods are executed in the context of a java.sql.Connection. The DatabaseMetaData returns information about the client's connection and interesting information about the database to which the client has connected.

Making a query and getting its results

After connecting, most clients will perform a query of some sort, either a select statement, or an insert, update, or delete statement. The java.sql.Statement class is used to compose and execute particular kinds of SQL queries on the DBMS. The results of a query are used to create a java.sql.ResultSet. The JDBC ResultSet is navigable in only one direction -- next -- and there are limitations on manipulating results. Although ResultSet doesn't allow for very sophisticated query management, it is a good foundation upon which to build other higher-level APIs with elegant, easy-to-use objects for data management. Meta information about the ResultSet itself is contained in the ResultSetMetaData objects.

You can also execute stored procedures on a database with two subclasses of java.sql.Statement, java.sql.PreparedStatement, and java.sql.CallableStatement.

Other JDBC classes

Other JDBC classes are supplied for utility purposes, like java.sql.Types, that encapsulate Java types for database use, and java.sql.Date, java.sql.Time, and java.sql.Timestamp. There are some classes that a developer writing an application may never use directly, but which the vendor who implements the JDBC specification will use internally, like java.sql.DataTruncation, java.sql.DriverInfo and java.sql.DriverPropertyInfo. Exception handling is provided for in the classes java.sql.Exception and java.sql.Warning.

In addition to the classes prescribed by the JDBC specification, a JDBC implementation may also write certain extensions to JDBC that fulfill operations specific to a particular DBMS. For example, WebLogic's jdbcKona/Oracle driver provides an extension to JDBC for creating and using Oracle sequences.

How JDBC has been implemented by vendors

In general, there are three major groups of JDBC drivers that connect a client directly to a DBMS; these are commonly referred to as "two-tier drivers." Here is an illustration that shows general distinctions between the groups, followed by a discussion of the strengths and weaknesses of each approach.

For reference, we have also added notes about the JavaSoft categories of JDBC drivers that are on the page of JDBC driver vendors.

The JDBC-ODBC bridge and ODBC access

JDBC access via the JDBC-ODBC bridge and ODBC

There are advantages in using a JDBC implementation that relies on ODBC, particularly if your application will need to access databases that may not be widely supported by other, more vendor-specific JDBC drivers.

Two-tier JDBC drivers that use vendor client libraries

A two-tier driver that interacts with vendor-supplied client libraries removes the need for ODBC and the JDBC-ODBC bridge. The advantages of using such a JDBC driver is that it reduces the complexity -- and therefore development, debugging, and maintenance time -- of a client application, as well as providing direct access to the full functionality of the database.

Two-tier drivers that interact with vendor-supplied client libraries are often not pure-Java, however. Since the client libraries are usually written in C or C++, the JDBC implementation must use a layer of C or C++ in order to make calls to the vendor libraries. This requires that each client have a local copy of the .DLL or .SO that contains the C/C++ layer for communicating with the vendor libraries. Often drivers like these are called "native" drivers, because their layer of non-Java code requires the use of "native methods" in Java. JDBC drivers that use native methods can't currently be used in applets for security reasons.

JDBC access via vendor client libraries

Two-tier Java-only drivers and proprietary protocols

JDBC access via proprietary DBMS protocol

Multitier JDBC implementations

Just as with two-tier drivers, there are several possible multitier JDBC architectures.

One common scenario uses a Java JDBC client to communicate with the vendor's proprietary protocol (shown as "SQLNet" in the illustration below) with an HTTP server, which accepts requests on port 80 and routes them to the DBMS. There are some advantages and disadvantages to this scenario: first of all, it is easy to implement, because all you will need to write is the Java JDBC (applet) client, using a JDBC driver that can communicate via the vendor's propriety protocol. On the down side, however, the HTTP server may not supply the infrastructure that is desirable for a robust, commercial multitier application, since the HTTP server isn't specifically designed for such a task. This also may be extremely slow, since the HTTP server -- which is designed to serve HTML pages, not function as an intermediary database server -- may be hard to tune for best application performance. Because you will write an applet to act as your JDBC client, and applets cannot communicate with hosts other than the one from which the applet was loaded, this scenario means that you must co-locate the HTTP server and the DBMS on the same host, which may not be desirable. The HTTP host may live outside the firewall in your network, and the database server may exist on some large machine deep in the secure recesses of your network.

JDBC access via an HTTP intermediate server

Another common arrangement uses an intermediate server written in C or C++ which talks to its clients over TCP/IP through a layer of ODBC, and then to the database server via vendor client libraries (or ODBC) and a proprietary database protocol. Such an arrangement is probably more suited to large Internet or intranet applications than using an HTTP server as an intermediate server, but it requires the use of ODBC, and the server itself doesn't have any of the advantages of Java.

JDBC access via a C++ intermediate server and ODBC

The third scenario is least common, but -- we think -- most powerful. In this scenario, the intermediate server (like WebLogic) is written exclusively in Java, and uses TCP/IP to communicate with its Java JDBC clients. WebLogic then can use any JDBC-compliant driver to communicate with the DBMS via the database vendor's proprietary protocol.

JDBC access via a Java intermediate server

Because WebLogic is written in Java, it can run on any operating system and platform for which there is a Java Virtual Machine (JVM). That gives the developer a great deal of flexibility in accessing data; for example, a WebLogic Server running on a Macintosh might make a Macintosh-only database accessible to many JDBC clients, using Windows, UNIX, MacOS, etc.

WebLogic's multitier implementation of JDBC, one of WebLogic JDBC's features, takes care of the communication between client and the WebLogic Server, which provides database access as one of its services and acts as an intermediate server. WebLogic JDBC works along with a two-tier JDBC driver that maintains the connection between the WebLogic Server and the DBMS.

JDBC multitier access in WebLogic

WebLogic also provides a set of common server facilities -- like application-wide logging, security (SSL and ACLs), name and directory services, configuration, instrumentation and management through its graphical GUI management console.

Matching your application needs with a JDBC implementation

There are several considerations when choosing a JDBC driver.

Database. First of all, you must determine which JDBC drivers are available for the database platform you will be using. All of the major database platforms, like Oracle and Sybase now have at least a few choices for JDBC drivers in the marketplace; and many less well-supported database platforms are accessible through ODBC, which gives you other options for JDBC drivers.

Operating system/platform. You must also find out whether a particular JDBC driver is available for the appropriate operating system(s). Although pure-Java applications run on any machine for which there is a JVM, not all of the JDBC drivers on the market are necessarily pure-Java. Some JDBC drivers are merely a Java wrapper around what formerly existed as a C or C++ driver; other JDBC drivers may be written primarily in Java, but have a very thin layer of C or C++ that is used solely to make calls into the vendor libraries (like the WebLogic two-tier driver model). In both cases, each JDBC client will need to install a .DLL or .SO in order to operate. JDBC drivers that require installation of a .DLL or .SO on the client machine will also have operating system dependencies, and it is important to find out whether your operating system is one that is supported for the JDBC driver you are interested in using.

Vendor-specific capabilities. If your application will be using vendor-specific SQL extensions, like sequences in Oracle or methods for reading and writing large text/image types in Sybase, you may be interested in finding a JDBC that supports vendor-specific extensions. (The WebLogic two-tier and multitier JDBC drivers support various vendor extensions that fall outside the capabilities of JDBC drivers that depend on ODBC and the JDBC-ODBC bridge.)

Scaling and performance. If you are developing a commercial-quality application, you may also be concerned about scaling and performance issues. Because of the direct one-to-one relationship of client to DBMS, a two-tier solution may not scale very well; an application that needs thousands of concurrent client connections to a database will likely prove cumbersome for both the network and the DBMS. In many cases, you will want to investigate a multitier JDBC solution that uses an intermediate server to mediate between clients and the DBMS.

Thread support. A two-tier driver may also be unable to take advantage of the multithreading capabilities of Java, if the client libraries themselves are not multithreaded. Threads are important for increasing performance because they allow multiple transactions on database data; thread safety means that conflicting operations in separate threads can be synchronized to make sure that operations take place in the proper order. Java has built-in multithreading support, as well as ways to synchronize multithreaded operations; but if the client libraries are not thread-safe, the JDBC driver cannot really take advantage of Java's multithreading. Using a multitier JDBC driver with a robust, pure-Java intermediate server will bring the added advantages of Java's thread support. The client can operate as a multithreaded application and let the intermediate server handle the synchronization and waiting that results from communicating with a single-threaded client library.

Security. You may also be concerned with the security of needing to supply each client with an unencrypted username and password for database access. A secure intermediate server can provide means for you to shield the client from direct access to DBMS username and password information by providing pools of connections to the DBMS that are secured by access to the intermediate server, rather than needing to have a username and password for the database.

Encryption of the data stream. A two-tier driver will probably not provide any encryption of the data that passes between the client and the DBMS. Intermediate servers provide less line exposure to sensitive data, since the line between the intermediate server and the DBMS may very well be on a protected part of the network even though the line between the client and the intermediate server is not. For even more protection, the intermediate server may provide encryption or remote computing services for the client to shield the data from snooping.

More on using JDBC in a multitier environment

Intermediate application servers, like Weblogic, play an important role in supplying DBMS data to clients in networked environments, particularly over WANs, where maximizing network bandwidth and speed may be crucial to the usability of an application. An application server may be able to service thousands of clients with a very small number of connections to the database; WebLogic optimizes network traffic between itself and its clients with its very efficient packet-based, queue-based protocol.

Both network traffic and DBMS use is reduced when you open a small number of connections to the database, leave them open, and then allow (trusted) clients to use connections from the pool. You can also reduce network traffic by limiting and managing the query results that are passed to the client; you cache the query results on the intermediate server, and then parcel out those results as (and only if) the client asks for them.

With WebLogic's multitier JDBC implementation and its high-level database access product dbKona, you can also create a DataSet that is saved on the WebLogic Server, and may be shared over multiple sessions by multiple clients, without ever making another request to the DBMS. WebLogic JDBC's multitier implementation supports connection pools, cached connections, and cached query results. In addition, your application benefits from the many server facilities, like application-wide logging, instrumentation and management, configuration, and clustering, that are available to all WebLogic applications. With WebLogic's graphical management console, you can delve deep into the details of your WebLogic JDBC clients.

Another reason to use a multitier JDBC driver with an application server is because you plan to use JDBC in an applet. If your two-tier JDBC choice of drivers uses any native methods, then you will not be able to load the applet in most browsers. Browsers prevent classes from loading native methods for security reasons. The practical outcome of this is that you will need to use a pure-Java JDBC driver for applet access to databases.

Summary

Do I want/need to use ODBC? Do I want/need to avoid ODBC?

ODBC may provide the right kind of services for your application; for some of the more esoteric databases, ODBC may be a necessary means of access to JDBC. ODBC can, however, add unnecessary layers of complexity to your application. There are JDBC drivers that do not depend on ODBC, like WebLogic's jdbcKona drivers for Oracle, Sybase, and MS SQL Server.

How many clients will use the application?

As the number of clients using your application increases, two-tier JDBC drivers may mean scaling and performance problems for both your network and your DBMS. If you will be serving many concurrent JDBC clients, you might want to investigate using a multitier intermediate server to buffer the interaction between your clients and the DBMS. There are several flavors of intermediate application servers; WebLogic offers JDBC database access services, as well as many other services and facilities that will improve performance and scalability.

Will the client be an applet that will run from a browser?

If so, you will not be able to use a JDBC driver that uses "native methods." Most browsers will not allow classes that contain native methods to load. You will definitely need to investigate pure-Java JDBC drivers, like WebLogic JDBC.

Will clients be accessing the DBMS over a WAN?

With the explosion of the web and the resultant increased need for access to data over wide area networks, network optimization has become very important for client applications. Your application may benefit from using an application server like the WebLogic Server, which is optimized for efficient network communications between client and server. Other features of WebLogic JDBC services include cached connections, connection pools, and cached DataSets.

Will multiple clients working with the same DBMS records need realtime updates?

With WebLogic's dbKona and WebLogic Events, you can also take advantage of WebLogic's event notification and management services to provide "eventful" data to your clients. When a record in an "eventful" query result is saved to the DBMS, other clients interested in those "eventful" records will be notified of the change; simply put, all the clients that have registered an interest in the "eventful" data will get realtime updates.

Will your clients be accessing sensitive data?

Two-tier drivers do not offer much help in shielding data that passes over the wire between DBMS and client from line snoopers. If you use an intermediate server in a multitier environment, you can locate the intermediate server so that the connection between the server and the DBMS is well-protected. The intermediate server may also provide security services that also shield sensitive data as it passes from intermediate server to client.

Will clients need to share data?

Two-tier JDBC drivers can't offer any sharing of data between clients. If you use an intermediate server like the WebLogic Server, however, a client can save a DataSet (query result) into the system workspace, and other clients of the WebLogic Server can access and use the DataSet. You can even ask the WebLogic Server to complete the query when it starts up, before any clients log in, to make the data immediately available to all interested users.

Will you be concerned about allocating scarce resources among multiple clients?

Two-tier drivers do not offer much for allocation of scarce resources since they are limited to the services that can be provided by the DBMS. If you use WebLogic JDBC, however, you can allocate certain resources, like database connections, for certain users, and you can control how many such resources are in use at a time.

More information on WebLogic products discussed in this paper

Whitepapers:

Overview of the WebLogic dbKona architecture
WebLogic Event architecture
Database connectivity in WebLogic

Product descriptions:

dbKona
The jdbcKona family of JDBC drivers
WebLogic JDBC Drivers

Copyright © 2000 BEA Systems, Inc. All rights reserved.
Required browser: Netscape 4.0 or higher, or Microsoft Internet Explorer 4.0 or higher.
Last updated 04/27/1999