Corporate Info | News | Solutions | Products | Partners | Services | Events | Download | How To Buy

Choosing a JDBC driver

About this document

WebLogic offers Java Database Connectivity (JDBC) drivers that suit a broad range of developer needs. This document describes typical JDBC driver implementations and functionality, and provides guidelines to help you choose the right kind of database driver for your application.

Contents

JDBC Implementations
  Two-tier JDBC implementations
  Multitier JDBC driver implementations

Overview of JDBC functionality
  Opening a connection
  Performing a query
  JDBC utilities

Matching your application needs with a JDBC implementation
  What should I consider when choosing a JDBC driver?
  Advantages of JDBC in a multitier environment

Further reading
  WebLogic JDBC drivers
  Sun JDBC Data Access API

JDBC Implementations

JDBC drivers appear in two groups -- two-tier drivers that connect a client directly to the DBMS and multitier, often called three-tier, drivers that connect the client to the DBMS through an interim server.

Two-tier JDBC implementations

The three basic kinds of two-tier JDBC drivers that connect a client directly to a DBMS are:

- JDBC-ODBC bridge

- JDBC drivers that use native libraries

- Java-only JDBC drivers

The JDBC-ODBC bridge and ODBC access

Currently, many JDBC implementations rely on ODBC (Microsoft's Open Database Connectivity driver) and the JDBC-ODBC bridge, which was developed in mid-1996 by Sun and Intersolv. In this case, ODBC acts as a mediating layer between the JDBC driver and the vendor client libraries. JavaSoft lumps all of these drivers into a single group that uses the JDBC-ODBC bridge.

Figure 1-1 JDBC access via the JDBC-ODBC bridge and ODBC

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

JDBC drivers that use native libraries

Another type of JDBC implementation is a two-tier driver that connects the client to the DBMS by way of the vendor-supplied libraries for the DBMS. These two-tiered drivers are called "native" drivers, because their layer of non-Java code requires the use of C/C++ methods in Java. JavaSoft calls these native-API partly-Java drivers, and there is a wide range of variation between vendors in how much of the driver is Java, and how much is C/C++. JDBC drivers that use native methods cannot currently be used in applets for security reasons.

Some vendors wrap an existing C/C++ driver in a thin layer of Java. Other vendors, including WebLogic, build the driver in Java and add a very thin layer of C/C++ to make calls to the native libraries. WebLogic's two-tier drivers, the jDriver for Oracle and the jDriver for Microsoft SQL Server, fall into this category.

Two-tier drivers that interact with native libraries remove the need for ODBC and the JDBC-ODBC bridge. Native library JDBC drivers offer these important advantages: 1) full functionality of the database, and 2) reduction of the complexity-the development, debugging, and maintenance time-of a client application.

Two-tier drivers that interact with native libraries are often not pure-Java. 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 native 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 native libraries.

Figure 1-2 JDBC access via native client libraries

Java-only drivers and proprietary protocols

A third type of two-tier JDBC driver is the pure-Java driver that makes no calls to the native libraries of the DBMS, but that communicates with the DBMS directly using its proprietary protocol. Some DBMS vendors provide two-tier JDBC drivers for access to their particular DBMS; these drivers are available only from the DBMS vendor. (JavaSoft calls these native-protocol all-Java drivers.)

Figure 1-3 JDBC access via proprietary DBMS protocol

Multitier JDBC driver implementations

The second group of JDBC drivers is designed for a multitier, often called three-tier, environment. JavaSoft calls these net-protocol all-Java drivers. These drivers work with an intermediate application server that sits between the client and the DBMS. WebLogicTM/JDBC provides support for this kind of JDBC access.

Just as with two-tier drivers, there are several possible multitier JDBC architectures, based on the type of intermediate server implemented, such as:

- HTTP intermediate server

- C/C++ intermediate server

- Java intermediate server

HTTP intermediate server

This scenario uses a Java JDBC client to communicate with the vendor's proprietary protocol (shown as "SQLNet" in Figure 1-4 below) with an HTTP server, which accepts requests on port 80 and routes them to the DBMS. The advantage to this scenario is that it is easy to implement, because all you 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 necessary infrastructure for a robust, commercial multitier application. The HTTP server may be extremely slow, because it is designed to serve HTML pages, not function as an intermediary database server; therefore, you may not obtain optimum application performance.

An applet functions as your JDBC client; however, an applet only communicates with the host on which the applet is loaded. Therefore, this scenario requires you to co-locate the HTTP server and the DBMS on the same host. A major disadvantage to this scenario is that 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.

Figure 1-4 JDBC access via an HTTP intermediate server

C/C++ 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. This implementation is suited to large Internet or intranet applications, but it requires the use of ODBC, and does not have the advantages of a Java server.

Figure 1-5 JDBC access via a C++ intermediate server and ODBC

Java intermediate server

The third scenario is increasing in popularity and BEA WebLogic recognizes this scenario as the most powerful in the market. In this scenario, the intermediate server (like WebLogic server) is written exclusively in Java, and uses TCP/IP to communicate with its Java JDBC clients. WebLogic, therefore, can use any JDBC-compliant driver to communicate with the DBMS via the database vendor's proprietary protocol.

Figure 1-6 JDBC access via a Java intermediate server

Because WebLogic is written in Java, it can run on operating systems and platforms for which there is a Java Virtual Machine (JVM), and provides developers optimum flexibility in accessing data. For example, a WebLogic Server running on a Macintosh can 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.

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.

Overview of JDBC functionality

Here is an overview the JDBC classes and functionality. Please note that JDBC, like most driver specifications, is low-level and functional.

JDBC uses a simple class hierarchy for database objects. The classes are contained in the java.sql.* package (included in JDK 1.1 and later). JDBC is a specification that consists of a collection of interfaces and abstract classes. 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.

Opening a connection

Three classes pertain to opening a connection to the DBMS - java.sql.DriverManager, java.sql.Connection, and java.sql.DatabaseMetaData.

The java.sql.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.

Performing a query

Two classes pertain to queries and results - java.sql.Statement and java.sql.ResultSet.

After connecting, clients often perform a query, such as a select, an insert, an update, or a 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 does not 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.

JDBC utilities

Other JDBC classes are supplied for utility purposes, like java.sql.Types, which encapsulates Java types for database use, java.sql.Date, java.sql.Time, and java.sql.Timestamp.

There is a group of classes that is used internally by vendors who implement the JDBC specification, such as java.sql.DataTruncation, java.sql.DriverInfo and java.sql.DriverPropertyInfo. Application developers may use this group but generally do not.

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 jDriver for Oracle provides an extension to JDBC for creating and using Oracle sequences.

Matching your application needs with a JDBC implementation

Choosing the optimum JDBC implementation requires thorough knowledge of your current system and future system requirements. The following topics will help you define your needs and choose the correct driver.

- Important things to consider when choosing a JDBC driver

- Advantages of JDBC in a multitier environment

- Important questions to ask when shopping for a JDBC driver

What should I consider when choosing a JDBC driver?

There are several areas to consider when choosing a JDBC driver, including:

- Database

- Operating system/platform

- Vendor-specific capabilities

- Scaling and performance

- Thread support

- Data stream encryption

Database

First, you need to determine which JDBC drivers are available for the database platform. 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 pure-Java. Some JDBC drivers are only 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 the JDBC driver you want to use supports your operating system.

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 user name and password for database access. A secure intermediate server can provide means for you to shield the client from direct access to DBMS user name 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 user name and password for the database.

Encryption of the data stream

A two-tier driver will probably not provide 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.

Advantages of JDBC in a multitier environment

Intermediate application servers, like WebLogic Server, supply DBMS data to clients in network environments, particularly over WANs, where maximizing network bandwidth and speed is crucial to the usability of an application. An application server is capable of serving thousands of clients with a 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 can 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 want the option of using JDBC in an applet. If your two-tier JDBC driver uses native methods, you will not able to load the applet in most browsers. Browsers prevent classes from loading native methods for security reasons.

Important questions to ask when shopping for a JDBC driver

When you are shopping for a JDBC driver, here are some questions to ask about your development project.

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 jDrivers for Oracle and Microsoft SQL Server.

How many clients will use the application?
  If you will be serving many concurrent JDBC clients, you may 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. As the number of users increases, a multitier JDBC driver will prevent network and DBMS scaling and performance problems that could occur with a two-tier driver.

Will the client be an applet that will run from a browser?
  You will want to consider pure-Java JDBC drivers, like WebLogic JDBC, if you plan on running applets from a browser. JDBC drivers that use "native methods" will not allow you to load the applet in most browsers.
  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 real time 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 do not share 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 also 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.

Further reading

WebLogic JDBC drivers

WebLogic JDBC Options

BEA WebLogic jDriver Family

Overview of the WebLogic dbKona architecture

Sun JDBC Data Access API

JDBC Data Access API

Copyright © 2000 BEA Systems, Inc. All rights reserved. Required browser: Netscape 4.0 or higher, or Microsoft Internet Explorer 4.0 or higher.