JDBC drivers in
the wild - JavaWorld July 2000
Tutorial Details:
JDBC drivers in the wild
JDBC drivers in the wild
By: By Nitin Nanda
Learn how to deploy, use, and benchmark JDBC driver types 1, 2, 3, and 4
hy should you consider Java Database Connectivity (JDBC) drivers apart from the JDBC-ODBC Bridge? What level of JDBC driver is suited for your application? What parameters should you use for evaluating a JDBC driver? This article evaluates various JDBC drivers and answers these questions.
While writing JDBC applications, developers generally start with JDBC-ODBCBridge to connect to databases. But when an application reaches some advanced stage, for example, when it needs to support multithreading, the JDBC-ODBCBridge poses a few problems. So, the need arises for a robust JDBC driver. In that case, the type of driver depends on quite a few parameters: whether the application is Internet or intranet based, whether it needs to support heterogeneous databases, the number of concurrent users, and so on.
In the first section of this article, we'll look at the basic architecture underlying the four JDBC driver types and enumerate the pros and cons for choosing one type over another -- information you can use to decide what type of JDBC drivers will be best suited for your specific application.
In the second section, we'll see evaluations of five specific industry-standard drivers: Sun's JDBC-ODBC Bridge, IDS Software's IDS Driver, Ashna's JTurbo, I-net Software's I-net Sprinta, and MERANT's SequeLink. I have also provided the steps required to evaluate each driver, as well as code snippets for specifying the classpath, loading the driver, establishing a database connection, and retrieving and inserting records. You may find it useful to use the code snippets provided in your evaluation program and follow these steps to determine benchmarks suited to your software and hardware requirements.
JDBC driver types
JDBC drivers are divided into four types or levels. Each type defines a JDBC driver implementation with increasingly higher levels of platform independence, performance, and deployment administration. The four types are:
Type 1: JDBC-ODBC Bridge
Type 2: Native-API/partly Java driver
Type 3: Net-protocol/all-Java driver
Type 4: Native-protocol/all-Java driver
Type 1: JDBC-ODBC Bridge
The type 1 driver, JDBC-ODBC Bridge, translates all JDBC calls into ODBC (Open DataBase Connectivity) calls and sends them to the ODBC driver. As such, the ODBC driver, as well as, in many cases, the client database code, must be present on the client machine. Figure 1 shows a typical JDBC-ODBC Bridge environment.
Figure 1. Type 1: JDBC-ODBC Bridge
Pros
The JDBC-ODBC Bridge allows access to almost any database, since the database's ODBC drivers are already available. Type 1 drivers may be useful for those companies that have an ODBC driver already installed on client machines.
Cons
The performance is degraded since the JDBC call goes through the bridge to the ODBC driver, then to the native database connectivity interface. The result comes back through the reverse process. Considering the performance issue, type 1 drivers may not be suitable for large-scale applications.
The ODBC driver and native connectivity interface must already be installed on the client machine. Thus any advantage of using Java applets in an intranet environment is lost, since the deployment problems of traditional applications remain.
Type 2: Native-API/partly Java driver
JDBC driver type 2 -- the native-API/partly Java driver -- converts JDBC calls into database-specific calls for databases such as SQL Server, Informix, Oracle, or Sybase. The type 2 driver communicates directly with the database server; therefore it requires that some binary code be present on the client machine.
Figure 2. Type 2: Native-API/partly Java driver
Pros
Type 2 drivers typically offer significantly better performance than the JDBC-ODBC Bridge.
Cons
The vendor database library needs to be loaded on each client machine. Consequently, type 2 drivers cannot be used for the Internet. Type 2 drivers show lower performance than type 3 and type 4 drivers.
Type 3: Net-protocol/all-Java driver
JDBC driver type 3 -- the net-protocol/all-Java driver -- follows a three-tiered approach whereby the JDBC database requests are passed through the network to the middle-tier server. The middle-tier server then translates the request (directly or indirectly) to the database-specific native-connectivity interface to further the request to the database server. If the middle-tier server is written in Java, it can use a type 1 or type 2 JDBC driver to do this.
Figure 3. Type 3: Net-protocol/all-Java driver
Pros
The net-protocol/all-Java driver is server-based, so there is no need for any vendor database library to be present on client machines. Further, there are many opportunities to optimize portability, performance, and scalability. Moreover, the net protocol can be designed to make the client JDBC driver very small and fast to load. Additionally, a type 3 driver typically provides support for features such as caching (connections, query results, and so on), load balancing, and advanced system administration such as logging and auditing.
Cons
Type 3 drivers require database-specific coding to be done in the middle tier. Additionally, traversing the recordset may take longer, since the data comes through the backend server.
Type 4: Native-protocol/all-Java driver
The native-protocol/all-Java driver (JDBC driver type 4) converts JDBC calls into the vendor-specific database management system (DBMS) protocol so that client applications can communicate directly with the database server. Level 4 drivers are completely implemented in Java to achieve platform independence and eliminate deployment administration issues.
Figure 4. Type 4: Native-protocol/all-Java driver
Pros
Since type 4 JDBC drivers don't have to translate database requests to ODBC or a native connectivity interface or to pass the request on to another server, performance is typically quite good. Moreover, the native-protocol/all-Java driver boasts better performance than types 1 and 2. Also, there's no need to install special software on the client or server. Further, these drivers can be downloaded dynamically.
Cons
With type 4 drivers, the user needs a different driver for each database.
Performance evaluation of five specific JDBC drivers
To evaluate the performance of five industry-standard drivers based on parameters such as average connection time, data retrieval time, and record insertion time, I created a sample database in SQL Server 7.0. I picked industry-standard JDBC drivers representing various driver types. Note: I didn't test any type 2 drivers because they are not readily available in the market, and I wanted to stick with pure-Java drivers for this article.
To perform the tests, I downloaded and deployed the trial versions of these drivers; then I performed several tests on each so as to determine performance measures under similar software and hardware environments. Note that the readings could vary for other environments. That being said, the results here provide relative information to evaluate each type of driver.
The following JDBC drivers were tested:
JDBC-ODBC Bridge by Sun
IDS Driver by IDS Software
SequeLink by MERANT
JTurbo 1.22 by Ashna
I-net Sprinta 2000 by I-net Software
The observations made are given in table 1.
Table 1. Performance parameters of five JDBC drivers
JDBC Driver/Requirement
JDBC-ODBC
Bridge
IDS Driver
SequeLink
JTurbo 1.22
I-net Sprinta
Vendor
Comes with Sun JDK
IDS Software
MERANT
Ashna Inc.
I-net Software
Type of driver
1
3
3
4
4
Footprint (compressed)
-
114 KB
269 KB
107 KB
42 KB
Database connection time (ms)
55
22
65
25
190
Data retrieval tme (ms)(33,000 records)
10
5
10
905
675
Fetch & traversal time (ms) (33,000 records)
865
18,700
3,500
910
560
Data insertion time (ms) (1,000 records)
3,700
3,000
3,500
3,600
3,050
The database table on which the tests have been performed has columns: int, varchar(255), and datetime. The test conditions were as follows:
Table 2. Test conditions
Test conditions
Platform:
Windows NT 4.0
Computer with Java VM:
Pentium III
SQL Server 7.0:
Pentium III
JVM:
JDK 1.2.1
Now let's look at the description of these drivers and the general settings required to evaluate them.
JDBC-ODBC Bridge
The JDBC-ODBC Bridge by Sun's JavaSoft -- a type 1 driver -- results from a joint effort between JavaSoft and MERANT. Available in the Java Developer Kit (JDK), this product leverages the large number of ODBC drivers available and has provided some momentum for JDBC to become widely accepted in a short time frame.
Follow these steps to evaluate it:
Create an ODBC DSN
Using the Windows Control Panel, create an ODBC DSN (data source name) for the database.
Load the driver
In a JDBC program, one of the first things to do is to load the JDBC driver by calling the forName() static method of the Class class. forName() takes one string parameter: the name of the driver along with its package. For JavaSoft's JDBC-ODBC Bridge, this string is "sun.jdbc.odbc.JdbcOdbcDriver" . Therefore, the call would look like:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Establish a database connection
Once the JDBC driver loads, you can establish a connection to the database using the DriverManager.getConnection() method. This method's first argument is a string that contains the JDBC URL for the database. The second and third parameters are the user name and password, respectively.
A JDBC URL is formulated using the following pattern:
jdbc::
A connection to the database could be established in this manner:
String stUrl_= "jdbc:odbc:myDSN"; // let's say myDSN
// is the name of ODBC DSN
Connection connection_ = DriverManager.getConnection(stUrl_, "sa", "");
Calculate the parameters
The following code could be used to ca
Read
Tutorial at: Click here to view the tutorial
Rate Tutorial: JDBC drivers in
the wild - JavaWorld July 2000
View Tutorial: JDBC drivers in
the wild - JavaWorld July 2000
Related
Tutorials:
 Integrating Databases
Integrating Databases
|
SQLJ: The 'open
sesame' of Java database applications
SQLJ: The 'open
sesame' of Java database applications
|
Scripting power
saves the day
for your Java
apps
Scripting power
saves the day
for your Java
apps
|
Create your own type 3 JDBC driver, Part 2
Create your own type 3 JDBC driver, Part 2
|
Create your own type 3 JDBC driver, Part 3
Create your own type 3 JDBC driver, Part 3
|
High-availability mobile applications
High-availability mobile applications
|
Simple classes for JDBC
Simple classes for JDBC
|
Data Syncronization Tool
Data Syncronization Tool
Daffodil Replicator is a powerful Open Source data synchronization tool that allows bi-directional data synchronization between heterogeneous databases supporting JDBC drivers. Its flexible publish and subscribe model support
|
HA-JDBC: High-Availability JDBC
HA-JDBC: High-Availability JDBC
Summary
HA-JDBC is a JDBC driver proxy that provides light-weight, transparent clustering capability to any underlying JDBC driver.
|
The JDBC RowSet Implementations Tutorial
In "The JDBC RowSet Implementations Tutorial," you will look at how to use the standard JDBC RowSet implementations specified in JSR-114.
|
XlsJdbc Platform independent readonly jdbc driver to read xls files.
XlsJdbc is Platform independent readonly jdbc driver to read xls files.
|
First Step towards JDBC
This article introduce you with JDBC and shows you how to create a database application to access the databases.
|
Jeff Schmitt's JDBC Page
This tutorial assumes you are using the MySQL database and the GWE JDBC drivers. The host computer is triton.towson.edu.
|
Overview of JDBC and its use with Microsoft Access
JDBC provides a set of classes for Java with a standard SQL database access interface. Goal is uniform access to a wide range of relational databases.
|
Device Driver Tutorial for the Solaris OS
Make your hardware work with the Solaris OS on x86 or SPARC architectures. If you are a beginning Solaris kernel programmer, start with this new tutorial on docs.sun.com.
|
First Step towards JDBC!
First Step towards JDBC!
First Step towards JDBC
Introduction
T his article introduce you with JDBC and shows you how to create a database application to access the databases. For the shake of simplicity, in very first example Access database and
|
First Step towards JDBC!
First Step towards JDBC!
Welcome to our JDBC Section
First Step towards JDBC
This article introduce you with JDBC and shows you how to create a database application to access the databases.
Accessing the Database from Servlet
This article shows
|
Connecting to MySQL database and retrieving and displaying data in JSP
page
Connecting to MySQL database and retrieving and displaying data in JSP page
Connecting to MySQL database and retrieving and displaying data in JSP page
This tutorial shows you how to connect to MySQL database and retrieve the data from the
|
Introduction to the JDBC
Introduction to the JDBC
Introduction to the JDBC
Introduction
T his article introduce you with JDBC and shows you how to our search engine with database.
What is JDBC?
J ava Database Connectivity or JDBC for short is set of Java API's that
|
New Technical Articles: 64-bit Programming on Solaris 10 OS for x86 Platforms
Four technical articles describe the new Sun Studio 10 software's 64-bit programming features on the Solaris 10 OS for x86 and AMD64 platforms. Important issues regarding the AMD64 ABI (Application Binary Interface), debugging, migration to 64-bits, and p
|
|
|
|
