Articles Index
Getting Started With JDBC
By John Papageorge
(August 1997)
In the last several months there has been a lot of excitement over JDK 1.1
features, such as RMI, Beans, the new event model, and internationalization.
But according to Aaron Alpar, there has not been a whole lot of talk about
the JDBC API that is now standard with the 1.1 release. And he thinks it
deserves some loving attention.
"JDBC is the most useful tool in the JDK, and the least
understood," Alpar insists.
He believes the JDBC, a simple set of classes for accessing relational
databases from Java with a class library that mirrors the ODBC from
Microsoft, has been ignored because database access is not new when
compared to RMI and the new event model. He also believes it hasn't
gotten its due because many vendors, rather than waiting for JDBC to
be an official part of the 1.1 release, have come up with JDBC-compliant
APIs.
"If you visit the JavaSoft web site looking for something that will
allow you to write Java applications to your Sybase database, you're likely
to run into a dizzying array of options; from direct desktop drivers, to
middleware, to application-server solutions all providing JDBC support,"
Alpar says.
Keep in mind that JDBC database access is not limited to some of the larger
vendors of database servers, such as Informix, Oracle, Sybase, and
Microsoft. Database access from JDBC equally applies to smaller
desktop database systems, such as xBase files, FoxPro, MS Access, and mSQL.
JDBC, through ODBC, even works with text files, and Excel spreadsheets.
Alpar, who is currently on a 14-city tour promoting Java, plans to
celebrate the JDBC. "I'm going to discuss the JDBC API," he says.
"It's a good foundation for understanding the current state-of-the art
in Java database access. It will also help users understand the features in
some of the higher-level APIs, such as JSQL."
Within JDBC there are four particularly important classes: DriverManager,
Connection, PreparedStatement, and ResultSet. Each class corresponds to a
indispensable phase of database access:
- The DriverManager loads and configures your database driver
on your client.
- The Connection class performs connection and authentication
to your database server using URLs.
- PreparedStatement moves SQL to the database engine for
preprocessing and eventually execution.
- The ResultSet class allows for the inspection of results
from "select" statements.
Alpar explains that each class relies on it's previous class for instance
creation. For example, an instance of Connection cannot exist without being
created from the DriverManager.
Below, Alpar shows you how to perform the basics. You will find that simple
operations, like inserting a row, are simple to perform. When you get into
more complex operations, such as passing out parameters from stored
procedures, your JDBC code will get more complicated.
Because JDBC can work with a great number of drivers, there's a class called
the DriverManager to load drivers into your environment. Generally, you
will only need the driver manager once, in the beginning of the main or
init section of your application or applet, respectively.
Example
In my examples, I'll be using dbANYWHERE from Symantec with tables from a
fictitious order entry system.
String driverName =
"symantec.itools.db.jdbc.Driver";
Properties systemProperties =
System.getProperties();
systemProperties.put("jdbc.drivers", driverName);
There's only one method that you really need to know for the DriverManager,
and that's getConnection. Connection objects are created from the class
DriverManager. Connection objects are driven by URLs. For a more complete
discussion on how to form JDBC URLs, I recommend that you read the URL
specifications from JDBC Guide: Getting Started from the
JavaSoft web site.
The DriverManager is very politically correct about how it finds the right
driver for the URL specified in getConnection. It's not dictatorial but
rather operates by voluntary delegation, where JDBC visits each driver
registered with the DriverManager and asks, "will you connect to this
URL?" If the answer is no, the next driver is asked and the process
continues, otherwise the driver attempts to contact the URL:
aConnection = aDbAnywhereDriver.getConnection
("jdbc:dbaw://localhost:8889/tutorial",
"guest", "guestpass");
In the preceding example, "jdbc" is the main protocol,
"dbaw" is the sub protocol, and is followed by the hostname
"localhost" and port number "8889" of the dbANYWHERE
server.
As mentioned before, each class is responsible for creating the next class,
as in the case of the PreparedStatement objects that are created from a
Connection instance:
try {
aPreparedStatement =
aConnection.prepareStatement (
"select order_id,
description from order_items" );
} catch ( SQLException e ) {
System.out.println (
e.messageString() );
}
Retrieving the information is a fairly straightforward process of fetching
the row and retrieving the row values column by column:
try {
// Fetch the next row
aPackageResultSet.next();
// then get the first value by column position
aPackageResultSet.getDouble (1);
// and then the next value by column name.
aPackageResultSet.getString ("description");
} catch ( SQLException e ) {
System.out.println ( e.messageString() );
}
Note that first, I'm retrieving values out of the database using what
JavaSoft likes to call getXXX methods. GetXXX is simply shorthand
for a typed accessor method such as getString, getDouble, or getInteger.
Second, I've used two forms of those accessor methods. One method
retrieves values by column index starting at 1. The other method retrieves
values by column name. The first method is much more efficient, however in
my opinion, more open to producing erroneous code.
PreparedStatement objects can also take in-parameters, which are parameters
that can be passed into a SQL statement after the statement has been
prepared on the database server and before the statement is executed. By
preparing statements in advance, I can then execute them over an over again
without having to resend and reparse the statement on the database server.
This technique is a big resource saver for repetitive SQL statements.
I've used an example of an insert statement below:
PreparedStatement anInsertStatement =
aConnection.prepareStatement (
"insert into order_items (
order_id, line_id, description ) values (
?, ?, ? )" );
anInsertStatement.setDouble ( 1, 100.00 );
anInsertStatement.setDouble ( 2, 1.00 );
anInsertStatement.setString (
3, "tennis racket" );
anInsertStatement.execute();
The preceding examples demonstrate the basics of how to get things done in
JDBC. I've shown you the basics of the four classes, DriverManager,
Connection, PreparedStatement, and ResultSet. There are also features,
including transaction management, support for out parameters, and many
more. JavaSoft, in their usual style, has made an excellent set of JDBC
documentation available at no cost on their Web site. (Editors note:
We didn't pay this guy to say these nice things. Really!) There you'll
find a simple set of classes for accessing relational databases from Java
and the class library mirrors the ODBC API from Microsoft, a brief description,
and how to use them. And that's really most of what JDBC is all about.