Chapter 14. Java and JDBC

Contents:

In Chapter 13, "C and C++", we
introduced you to the C APIs for MySQL and mSQL. Unfortunately, each
API only enables you to program for the database it supports. If you
are looking to port an application between MySQL and mSQL, or even
worse, if you are looking to make an application work on
Oracle or Sybase or any other database
engine, you must rewrite your database code to make use of that
database engine's proprietary API. Java programmers, however,
are mostly freed from database portability issues. They have a single
API, the Java DataBase Connectivity API (JDBC), that
provides them with a unified interface into all SQL
databases.

Because JDBC is a single interface to all databases, you need only to
learn it in order to be able to write applications that run on both
MySQL and mSQL. In fact, as long as you write proper JDBC code, the
Java applications you write will be able to run against any database
engine. If you have access to a database other than MySQL or mSQL,
you should give this claim a test by running this chapter's
examples on that database.

In this chapter, we are assuming a basic understanding of the Java
programming language and Java concepts. If you do not already have
this background, we strongly recommend taking a look at
Exploring Java (O'Reilly & Associates,
Inc.). For more details on how to build the sort of three-tier
database applications we discussed in Chapter 8, "Database Application Architectures",
take a look at Database Programming with JDBC and Java
(O'Reilly & Associates, Inc.).

14.1. What Is JDBC?

Like all Java APIs, JDBC is a set of classes and interfaces that work
together to support a specific set of functionality. In the case of
JDBC, this functionality is naturally database access. The classes
and interfaces that make up the JDBC API are thus abstractions from
concepts common to database access for any kind of database. A
Connection, for example, is a Java interface
representing a database connection. Similarly, a
ResultSet represents a result set of data returned
from a SQL SELECT statement. Java puts the classes
that form the JDBC API together in the java.sql
package which Sun introduced in JDK 1.1.

The details of database access naturally differ from vendor to
vendor. JDBC does not actually deal with those details. Most of the
classes in the java.sql package are in fact
interfaces -- and thus no implementation details. Individual
database vendors provide implementations of these interfaces in the
form of something called a JDBC driver. As a database programmer,
however, you need to know only a few details about the driver you are
using -- the rest you manage via the JDBC interfaces. The vendor
specific information you need in order to use JDBC includes:

The JDBC URL for the driver

The name of the class that implements
java.sql.Driver

NOTE

The new JDBC 2.0 specification adds an
optional standard extension API for vendors to implement. If your
JDBC vendor implements this standard extension, you do not even need
to know the JDBC URL or Driver class
implementation. It prescribes a
DataSource class that you can look up by a
configurable name in a JNDI[22]-supported directory.

[22]JNDI is the Java Naming
and Directory Interface API. It lets you store Java objects in a
naming and directory service like an Lightweight Directory Access
Protocol (LDAP) server and then look them up by name.

Both of these items can be supplied at runtime, either on the command
line or in a properties file. Your code never needs to mention these
two implementation-dependent pieces. We will cover what the JDBC URL and
Driver class do in a few paragraphs when we cover
database connections. Figure 14-1 diagrams the
interfaces of JDBC.

Figure 14-1. The classes and interfaces of the JDBC API

14.1.1. The Database Connection

Your first step is to connect to the database. One of the few
implementation classes in the
java.sql.package is the
DriverManager class. It maintains a list of JDBC
implementations and provides you with database connections based on
JDBC URLs you provide it. A JDBC URL comes in the form of
jdbc:protocol:subprotocol. It tells a
DriverManager which database engine you wish to
connect to and it provides the DriverManager with
enough information to make a connection.

NOTE

JDBC
uses the word "driver" in multiple contexts. In the
lower-case sense, a JDBC driver is the collection of classes that
together implement all of the JDBC interfaces and provide an
application with access to at least one database. In the upper-case
sense, the Driver is the class that implements
java.sql.Driver. Finally, JDBC provides a
DriverManager that can be used to keep track of
all of the different Driver implementations.

The protocol part of the URL refers
to a given JDBC driver. In the case of MySQL and mSQL, the protocol
is mysql and msql,
respectively. The subprotocol provides the implementation-specific
connection data. Both MySQL and mSQL require a host name and database
name in order to make a connection. Optionally, they may require a
port if your database engine is not running as root. The full mSQL
URL therefore looks like: jdbc:msql://athens.imaginary.com:1114/test.
It says that the DriverManager should find the
mSQL JDBC driver and connect to the database test at
athens.imaginary.com on port 1114. All of this
is done via a single call to the DriverManagergetConnection() method. Example 14-1
shows how to make a connection to an mSQL database.

Example 14-1. A Code Snippet from the Examples that Come with the Imaginary JDBC Driver for mSQL Showing How to Make a Connection

The line con = DriverManager.getConnection(url, "borg",
"") makes the database connection in this example. In this
case, the JDBC URL and Driver implementation class
names are actually hard coded into this application. The only reason
this is acceptable is because this application is a demo for the
mSQL-JDBC driver. For a serious application, you would want to load
this information from a properties file, pass it as command line
arguments, or pass it as system properties. The
Driver implementation will automatically be loaded
if you pass it as the system property
jdbc.drivers -- in other words, you do not
have to call
Class.forName().newInstance(driver_name) when you
pass the driver name as the jdbc.drivers system
property. The second and third arguments to
getConnection() are the user ID and password to
use for the connection. Because mSQL does not use passwords for user
authentication, this example just uses an empty string. In MySQL,
however, you will need to provide a password.[23]

[23]MySQL
actually has several JDBC drivers. At least one of them allows you to
specify the user ID and password as part of the URL.

14.1.2. Maintaining Portability Using Properties Files

Though our focus is on two specific databases, it is good Java
programming practice to make your applications completely portable.
To most people, portability means that you do not write code that
will run on only one platform. In the Java world, however, the word
"portable" is a much stronger term. It means no hardware
resource dependencies, and that means no database dependencies.

We discussed how the JDBC URL and Driver name are
implementation dependent, but we did not discuss how to avoid hard
coding them. Because both are simple strings, you can pass them on
the command line as runtime arguments or as parameters to applets.
While that solution works, it is hardly elegant since it requires
command line users to remember long command lines. A similar solution
might be to prompt the user for this information; but again, you are
requiring that the user remember a JDBC URL and a Java class name
each time they run an application.

A more elegant solution than either of the above solutions would be
to use a properties file. Properties files are supported by the
java.util.ResourceBundle and its subclasses to
enable an application to extract runtime specific information from a
text file. For a JDBC application, you can stick the URL and
Driver name in the properties file, leaving the
details of the connectivity up to an application administrator. Example 14-1 shows a properties file that provides
connection information.

Example 14-1. The SelectResource.properties File with Connection Details for a Connection

We have gotten rid of anything specific to mSQL in the sample
connection code. One important issue still faces portable JDBC
developers -- one that stings mSQL developers in particular. JDBC
requires any driver to support SQL2 entry level. This is an ANSI
standard for minimum SQL support. As long as you use SQL2 entry level
SQL in your JDBC calls, your application will be 100% portable to
other database engines. Unfortunately, while MySQL is SQL2 entry
level, mSQL is not. Applications you write for mSQL will very likely
port to other databases without issue, but applications written to
use the full range of SQL92 entry level will not port back to mSQL
without pain.