BEA Logo BEA WebLogic Server Release 1.1

  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 paper discusses the JDBC specification and WebLogic's implementation and provides guidelines to help you choose the right kind of database driver for your application.

Contents

JDBC Implementations

2-tier JDBC implementations

Multitier JDBC driver implementations

Overview of JDBC functionality

Opening a connection

Performing a quety

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

Important questions to ask when shopping for a JDBC driver

JDBC Implementations

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

2-tier JDBC implementations

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

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.


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.

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 can't 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.

A two-tier driver that interacts with native libraries removes the need for ODBC and the JDBC-ODBC bridge. The advantages of the JDBC driver that uses native libraries is that it provides full functionality of the database, and reduces 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, 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 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.


JDBC access via native client libraries

Java-only drivers and proprietary protocols

A third type of 2-tier JDBC drivers are pure-Java drivers that make no calls to the native libraries of the DBMS, but rather communicate with the DBMS directly using its proprietary protocol. Some DBMS vendors provide two-tier JDBC drivers for access to their particular DBMS. (JavaSoft calls these native-protocol all-Java drivers.) Most choices of this type of driver are available only from the DBMS vendor itself, and are single-DBMS specific.


JDBC access via proprietary DBMS protocol

Multitier JDBC driver implementations

The second group of JDBC drivers is designed for a multitier, often called 3-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

This 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. 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, applets cannot communicate with hosts other than the one on which the applet is loaded. Therefore, this scenario requires you to co-locate the HTTP server and the DBMS on the same host. This may not be desirable because 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

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

Java intermediate server

The third scenario is increasing in popularity and is, according to BEA WebLogic, the 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). Developers, therefore, have a great deal of 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

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

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

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.

What should I consider when choosing a JDBC driver?

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

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's important to find out whether your operating system is one that is supported for the JDBC driver you're 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'll 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 plan to use JDBC in an applet. If your two-tier JDBC choice of drivers uses any native methods, then you won't 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'll need to use a pure-Java JDBC driver for applet access to databases.

Important questions to ask when shopping for a JDBC driver

When you're 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 don't depend on ODBC, like WebLogic's jDrivers for Oracle and Microsoft 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 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.

Will the client be an applet that will run from a browser?
If so, you won't be able to use a JDBC driver that uses "native methods." Most browsers won't allow classes that contain native methods to load. You'll 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 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 don't 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 don't 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

Sun JDBC information