JDBC: A technological premiere

S Gokul

Introduction

The Java Database Connectivity API (JDBC) from Sun is a layer in-between the
Java programs and the databases. Its primary purpose is to hide the database
specific implementations from the eyes of the Java program, and provide a
standard set of interfaces for almost all kinds of interactions that can take
place between the programs and the database.

Thus, JDBC allows programs to access the data without being aware of the
actual database that has stored the data. The Java programs that make use of the
JDBC API can either be standalone Java applications or server side Java modules
like servlets or JSPs.

Why databases should be hidden?

It is worthwhile to ponder over why we need to have a middle layer in between
the databases and the programs that are utilizing its services.

Some of the obvious advantages are:

• Flexibility: Since the codes stand independent of database
  specific implementations, the underlying database can be changed easily.
• Cross DB connectivity: It is relatively easy to develop
  applications that get connected to more than one database at a time.
• Standardization: It is much better to write a standard set
  of codes for getting connected to different databases, rather than dealing
  with a unique set of codes for every other database.
• Responsible broker: The middle layer (i.e. the JDBC) can be
  well programmed to take care of bugs that are normally committed by the
  programmer. And it can be improved from time to time, for better performance
  and reliability.

JDBC standard interfaces

The JDBC API from Sun defines a set of eight standard interfaces* that must be
implemented by the JDBC drivers (explained below). These interfaces are all
packed inside the java.sql package.

java.sql.Driver

java.sql.Connection

java.sql.Statement

java.sql.PreparedStatement

java.sql.CallableStatement

java.sql.ResultSet

java.sql.ResultSetMetaData

java.sql.DatabaseMetaData

Unless the JDBC drivers implement all of these standard interfaces, they
cannot claim to be JDBC compliant.

* An Interface is an Object Oriented Concept by which a set of business tasks
are simply defined and not performed. Classes that implement this interface must
necessarily perform the tasks laid out in the interface.

JDBC drivers

As seen above, the JDBC API merely defines a set of Standard Interfaces that
must be implemented for making JDBC connectivity. The actual implementations of
these interfaces are usually undertaken by the database vendors themselves (for
their respective products) or third party developers. These database specific
implementations of the generic JDBC APIs are called the JDBC drivers.

Thus, the developers need to have independent JDBC drivers for every unique
database they want to get connected.

As on date, JDBC drivers are available for around 123 different databases —
including the most popular RDBMS products like Oracle, Informix and MySQl - that
are available in the market. The availability of these drivers can be checked
out from Sun’s Web site: http://industry.java.sun.com/products/jdbc/drivers.

Types of JDBC drivers

JDBC drivers are available in several flavors — called Type I, Type II, Type
III drivers etc. Thus, there may be more than one JDBC solution available for
the same database.

While a detailed discussion on various types of JDBC drivers is beyond the
scope of this tutorial, it is generally believed that drivers written in pure
Java language, with minimal or no usage of native database specific APIs are
most efficient.

JDBC vs. ODBC

Conceptually, JDBC is not much different from ODBC (Open Database Connectivity)
— a well-known technology in Windows based platforms. It will be interesting
to note that there is even a JDBC-ODBC bridge from Javasoft, which allows Java
programs to get connected to the database through the ODBC layer.

But, since ODBC uses the C interface, Java applications that use them are not
pure Java solutions anymore and cannot be ported across operating systems, other
than Windows!

Implementing the JDBC API

Java client programs that make use of the JDBC API, usually follow the sequence
outlined below:

• Load the relevant JDBC Driver, using the DriverManager object.
• The JDBC Driver Classes must be available in the Classpath of the system
  environment.
• The purpose of loading the driver through the DriverManager is to keep
  track of different JDBC implementations that a program might be using. Later
  on, when connections are requested from the DriverManager, it can make use
  of the appropriate drivers and establish a connection with the database.
• Initialize a connection object through the DriverManager, using
  database URL, ID and password.
• The DriverManager expects three parameters to initialize a connection: The
  database URL (A URL system used by JDBC to identify a database resource on
  the network), a valid username and password.
• Once a connection object is successfully obtained, any number of queries
  can be performed on the database, as long as it is alive and valid.
• Connection is a costly resource and should be used carefully.
• Initialize a statement object that can eventually be used to perform
  the SQL queries.
• Perform the queries — Select, Insert, Update etc. and catch the
  results using a ResultSet Object.
• While Select queries return ResultSets, Insert and Update queries return
  the number of rows affected by the query.
• The ResultsetMetadata object can be used to query the nature of results
  contained in the ResultSet object.
• Close the connection object.
• Closing the connection, after all interactions are over, is a very
  important task that should not be overlooked. As indicated earlier,
  connection is a very costly resource and a few careless open connections can
  well neigh bring down the performance of the database.