Data Management

Choose the right JDBC driver for your database interface

Pick the right driver to optimize connectivity between your Java apps and database.

You may or may not have an enterprise client, but if you’re using Java, you already know that presentation isn’t everything: database access is as crucial a design consideration as the interface. The Java Database Connectivity (JDBC) API is your all-purpose link between applications and your databases. Based on Microsoft’s Open Database Connectivity technology, JDBC is relational database-friendly and supports SQL. With the correct driver, you can use this API to connect to any database that supports an ODBC interface. Putting it another way, if your application is three-tier, you can use JDBC to create a layer of abstraction between your business logic level and your data level: All you need to slip a new database in place under your apps is new drivers.

Note
It’s important to remember that while JDBC is RDBMS-oriented, it can work with any database that uses tables.

Type is everything
The good news is that JDBC drivers are available for most commercial databases and most open source databases, as well—more than 200 in all. However, this doesn’t get you all the way home. Database flavor aside, JDBC drivers come in four distinct types, and the appropriateness of your choice of type for your particular environment is crucial.

These drivers must adhere to the principle of enabling database code that is independent of any particular database. There is a standard that ensures this, the ANSI 92 SQL-2 Entry Level command set. Living with the broad connectivity made possible by the compliance of JDBC drivers means that a truly abstract interface may not be able to make use of all the features of a given database technology, but the freedom to mix and match database technologies without rewriting applications is generally a trade worth making in this age of the enterprise. You can create your own JDBC driver, by the way, and if it works for you, make it available to others.

Note
JDBC can pass any command from application to engine, so you can still access whatever database features you like, as long as you understand that you are sacrificing some of the abstraction you ostensibly sought in creating a database-independent interface.

As you might guess, you will most often obtain JDBC drivers from the vendor of your particular database. Alternately, you might find them at Java’s Web site.

Here are the JDBC driver types:
  • Type 1 - Java’s database connectivity API is ODBC-compatible. When it is desirable to connect an ODBC-supporting database with Java code, a Type 1 JDBC driver can form a bridge between ODBC and JDBC. This driver was designed early on, to encourage the use of JDBC by making it compatible with the installed base of ODBC databases already in the marketplace.
  • Type 2 - These drivers are similar in principle to Type 1 drivers, but they bridge to specific database interfaces rather than to a standard. This type was developed to leverage the Java Native Interface (JNI). A Type 2 driver will convert database calls into function calls appropriate to the database using JNI.
  • Type 3 - More sophisticated than Type 1 or Type 2, these drivers handle calls from the API by translating them into generic database calls, then retranslating them into calls using the protocol of the database. These drivers may seem a bit overly complex, but are the most conceptually pure.
  • Type 4 - These drivers are the roll-up-your-sleeves-and-get-it-done kind. They are custom Java drivers painstakingly written to bridge JDBC to specific databases. As you might guess, the availability of such drivers has been expanding with time. There are no calls involved—the mechanism used is to talk directly to the database using the appropriate protocol.

How to choose
In general, Type 1 and Type 3 drivers are generic, while Type 2 and Type 4 drivers are database-specific. However, this selection process alone won’t necessarily drive your decision. Here are some other considerations:
  • Type 1 - You’ll seldom need a Type 1 driver, unless you are already using a database that supports ODBC but not JDBC. A generic JDBC-ODBC bridge driver is included with the Java SDK, but it underscores the weakness of this type: they’re slow. It is hard to think of a reason why you’d want to use a Type 1 JDBC driver if you had another type available to you.
  • Type 2 - There aren’t that many Type 2 drivers around these days, so it’s not likely to be a choice. However, they’re a good choice for stand-alone apps and server apps, and generally require less technical knowledge of the mechanics of the target database.
  • Type 3 - Type 3 drivers easily trump Type 1 drivers, and there are going to be instances when a particular database simply has no Type 2 or Type 4 drivers available for it. Microsoft Access is an example. If no Type 4 driver exists and it’s a choice between Type 2 and Type 3, consider that Type 3 gives you greater portability (if that’s important to you).
  • Type 4 - Type 4 drivers will generally be the most efficient choice, since they establish a direct connection rather than a call relay. If a Type 4 driver is available for the particular database you’re using, it will most often be the way to go. The exceptions to Type 4 drivers might be stand-alone apps and server apps, where the protocol-specific nature of Type 4 buys you nothing. However, the speed of a Type 4 is still there (as well as a Type 2) because there is only one translation step.

You can learn more about JDBC drivers at Sun's Web site.

About

Scott Robinson is a 20-year IT veteran with extensive experience in business intelligence and systems integration. An enterprise architect with a background in social psychology, he frequently consults and lectures on analytics, business intelligence...

0 comments

Editor's Picks