Connecting
to databases
with
JDBC
It
has been estimated that half of all software development involves client/server
operations. A great promise of Java has been the ability to build
platform-independent client/server database applications. In Java 1.1
this has come to fruition with Java
DataBase Connectivity (JDBC).
One
of the major problems with databases has been the feature wars between the
database companies. There is a “standard” database language, Structured
Query Language (SQL-92), but usually you must know which database vendor
you’re working with despite the standard. JDBC is designed to be
platform-independent, so you don’t need to worry about the database
you’re using while you’re programming. However, it’s still
possible to make vendor-specific calls from JDBC so you aren’t restricted
from doing what you must.
JDBC,
like many of the APIs in Java, is designed for simplicity. The method calls you
make correspond to the logical operations you’d think of doing when
gathering data from a database: connect to the database, create a statement and
execute the query, and look at the result set.
To
allow this platform independence, JDBC provides a
driver
manager
that dynamically maintains all the driver objects that your database queries
will need. So if you have three different kinds of vendor databases to connect
to, you’ll need three different driver objects. The driver objects
register themselves with the driver manager at the time of loading, and you can
force the loading using
Class.forName( ). To
open a database, you must create a “database
URL” that specifies:
- That
you’re using JDBC with “jdbc”
- The
“subprotocol”: the name of the driver or the name of a database
connectivity mechanism. Since the design of JDBC was inspired by ODBC,
the first subprotocol available is the “jdbc-odbc bridge,”
specified by “odbc”
- The
database identifier. This varies with the database driver used, but it
generally provides a logical name that is mapped by the database administration
software to a physical directory where the database tables are located. For
your database identifier to have any meaning, you must register the name using
your database administration software. (The process of registration varies from
platform to platform.)
All
this information is combined into one string, the “database URL.”
For example, to connect through the ODBC subprotocol to a database identified
as “people,” the database URL could be:
String
dbUrl = "jdbc:odbc:people";
If
you’re connecting across a network, the database URL will also contain
the information identifying the remote machine.
When
you’re ready to connect to the database, you call the
static
method
DriverManager.getConnection( ),
passing it the database URL, the user name, and a password to get into the
database. You get back a
Connection
object that you can then use to query and manipulate the database.
The
following example opens a database of contact information and looks for a
person’s last name as given on the command line. It selects only the
names of people that have email addresses, then prints out all the ones that
match the given last name:
//: Lookup.java
// Looks up email addresses in a
// local database using JDBC
import java.sql.*;
public class Lookup {
public static void main(String[] args) {
String dbUrl = "jdbc:odbc:people";
String user = "";
String password = "";
try {
// Load the driver (registers itself)
Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver");
Connection c = DriverManager.getConnection(
dbUrl, user, password);
Statement s = c.createStatement();
// SQL code:
ResultSet r =
s.executeQuery(
"SELECT FIRST, LAST, EMAIL " +
"FROM people.csv people " +
"WHERE " +
"(LAST='" + args[0] + "') " +
" AND (EMAIL Is Not Null) " +
"ORDER BY FIRST");
while(r.next()) {
// Capitalization doesn't matter:
System.out.println(
r.getString("Last") + ", "
+ r.getString("fIRST")
+ ": " + r.getString("EMAIL") );
}
s.close(); // Also closes ResultSet
} catch(Exception e) {
e.printStackTrace();
}
}
} ///:~
You
can see the creation of the database URL as previously described. In this
example, there is no password protection on the database so the user name and
password are empty strings.
Once
the connection is made with
DriverManager.getConnection( ),
you can use the resulting
Connection
object to
create
a
Statement
object using the createStatement( )
method. With the resulting Statement,
you can call executeQuery( ),
passing in a string containing an SQL-92 standard SQL statement. (You’ll
see shortly how you can generate this statement automatically, so you
don’t have to know much about SQL.)
The
executeQuery( )
method
returns a ResultSet
object, which is quite a bit like an iterator: the
next( )
method moves the iterator to the next record in the statement, or returns
null
if the end of the result set has been reached. You’ll always get a
ResultSet
object back from
executeQuery( )
even if a query results in an empty set (that is, an exception is not thrown).
Note that you must call
next( )
once before trying to read any record data. If the result set is empty, this
first call to
next( )
will return
false.
For each record in the result set, you can select the fields using (among other
approaches) the field name as a string. Also note that the capitalization of
the field name is ignored – it doesn’t matter with an SQL database.
You determine the type you’ll get back by calling getInt( ),
getString( ),
getFloat( ),
etc. At this point, you’ve got your database data in Java native format
and can do whatever you want with it using ordinary Java code.
Getting
the example to work
With
JDBC, understanding the code is relatively simple. The confusing part is making
it work on your particular system. The reason this is confusing is that it
requires you to figure out how to get your JDBC driver to load properly, and
how to set up a database using your database administration software.
Of
course, this process can vary radically from machine to machine, but the
process I used to make it work under 32-bit Windows might give you clues to
help you attack your own situation.
Step
1: Find the JDBC Driver
The
program above contains the statement:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); This
implies a directory structure, which is deceiving. With this particular
installation of JDK 1.1, there was no file called
JdbcOdbcDriver.class,
so if you looked at this example and went searching for it you’d be
frustrated. Other published examples use a pseudo name, such as
“myDriver.ClassName,” which is less than helpful. In fact, the load
statement above for the jdbc-odbc driver (the only one that actually comes with
JDK 1.1) appears in only a few places in the online documentation (in
particular, a page labeled “JDBC-ODBC Bridge Driver”). If the load
statement above doesn’t work, then the name might have been changed as
part of a Java version change, so you should hunt through the documentation
again.
If
the load statement is wrong, you’ll get an exception at this point. To
test whether your driver load statement is working correctly, comment out the
code after the statement and up to the
catch
clause; if the program throws no exceptions it means that the driver is loading
properly.
Step
2: Configure the database
Again,
this is specific to 32-bit Windows; you might need to do some research to
figure it out for your own platform.
First,
open the control panel. You might find two icons that say “ODBC.”
You must use the one that says “32bit ODBC,” since the other one is
for backwards compatibility with 16-bit ODBC software and will produce no
results for JDBC. When you open the “32bit ODBC” icon, you’ll
see a tabbed dialog with a number of tabs, including “User DSN,”
“System DSN,” “File DSN,” etc., in which
“DSN” means “Data Source Name.” It turns out that for
the JDBC-ODBC bridge, the only place where it’s important to set up your
database is “System DSN,” but you’ll also want to test your
configuration and create queries, and for that you’ll also need to set up
your database in “File DSN.” This will allow the Microsoft Query
tool (that comes with Microsoft Office) to find the database. Note that other
query tools are also available from other vendors.
The
most interesting database is one that you’re already using. Standard ODBC
supports a number of different file formats including such venerable workhorses
as DBase. However, it also includes the simple “comma-separated
ASCII” format, which virtually every data tool has the ability to write.
In my case, I just took my “people” database that I’ve been
maintaining for years using various contact-management tools and exported it as
a comma-separated ASCII file (these typically have an extension of
.csv).
In the “File DSN” section I chose “Add,” chose the text
driver to handle my comma-separated ASCII file, and then un-checked “use
current directory” to allow me to specify the directory where I exported
the data file.
You’ll
notice when you do this that you don’t actually specify a file, only a
directory. That’s because a database is typically represented as a
collection of files under a single directory (although it could be represented
in other forms as well). Each file usually contains a single table, and the SQL
statements can produce results that are culled from multiple tables in the
database (this is called a join).
A database that contains only a single table (like this one) is usually called a flat-file
database
.
Most problems that go beyond the simple storage and retrieval of data generally
require multiple tables that must be related by joins to produce the desired
results, and these are called relational
databases.
Step
3: Test the configuration
To
test the configuration you’ll need a way to discover whether the database
is visible from a program that queries it. Of course, you can simply run the
JDBC program example above up to and including the statement:
Connection
c = DriverManager.getConnection(
dbUrl, user, password);
If
an exception is thrown, your configuration was incorrect.
However,
it’s useful to get a query-generation tool involved at this point. I used
Microsoft Query that came with Microsoft Office, but you might prefer something
else. The query tool must know where the database is, and Microsoft Query
required that I go to the ODBC Administrator’s “File DSN” tab
and add a new entry there, again specifying the text driver and the directory
where my database lives. You can name the entry anything you want, but
it’s helpful to use the same name you used in “System DSN.”
Once
you’ve done this, you will see that your database is available when you
create a new query using your query tool.
Step
4: Generate your SQL query
The
query that I created using Microsoft Query not only showed me that my database
was there and in good order, but it also automatically created the SQL code
that I needed to insert into my Java program. I wanted a query that would
search for records that had the last name that was typed on the command line
when starting the Java program. So as a starting point, I searched for a
specific last name, ‘Eckel’. I also wanted to display only those
names that had email addresses associated with them. The steps I took to create
this query were:
- Start
a new query and use the Query Wizard. Select the “people” database.
(This is the equivalent of opening the database connection using the
appropriate database URL.)
- Select
the “people” table within the database. From within the table,
choose the columns FIRST, LAST, and EMAIL.
- Under
“Filter Data,” choose LAST and select “equals” with an
argument of Eckel. Click the “And” radio button.
- Choose
EMAIL and select “Is not Null.”
- Under
“Sort By,” choose FIRST.
The
result of this query will show you whether you’re getting what you want.
Now
you can press the SQL button and without any research on your part, up will pop
the correct SQL code, ready for you to cut and paste. For this query, it looked
like this:
SELECT people.FIRST, people.LAST, people.EMAIL
FROM people.csv people
WHERE (people.LAST='Eckel') AND
(people.EMAIL Is Not Null)
ORDER BY people.FIRST
With
more complicated queries it’s easy to get things wrong, but with a query
tool you can interactively test your queries and automatically generate the
correct code. It’s hard to argue the case for doing this by hand.
Step
5: Modify and paste in your query
You’ll
notice that the code above looks different from what’s used in the
program. That’s because the query tool uses full qualification for all of
the names, even when there’s only one table involved. (When more than one
table is involved, the qualification prevents collisions between columns from
different tables that have the same names.) Since this query involves only one
table, you can optionally remove the “people” qualifier from most
of the names, like this:
SELECT FIRST, LAST, EMAIL
FROM people.csv people
WHERE (LAST='Eckel') AND
(EMAIL Is Not Null)
ORDER BY FIRST
In
addition, you don’t want this program to be hard coded to look for only
one name. Instead, it should hunt for the name given as the command-line
argument. Making these changes and turning the SQL statement into a
dynamically-created
String
produces:
"SELECT FIRST, LAST, EMAIL " +
"FROM people.csv people " +
"WHERE " +
"(LAST='" + args[0] + "') " +
" AND (EMAIL Is Not Null) " +
"ORDER BY FIRST");
SQL
has another way to insert names into a query called stored
procedures
,
which is used for speed. But for much of your database experimentation and for
your first cut, building your own query strings in Java is fine.
You
can see from this example that by using the tools currently available –
in particular the query-building tool – database programming with SQL and
JDBC can be quite straightforward.
A
GUI version of the lookup program
It’s
more useful to leave the lookup program running all the time and simply switch
to it and type in a name whenever you want to look someone up. The following
program creates the lookup program as an application/applet, and it also adds
name completion so the data will show up without forcing you to type the entire
last name:
//: VLookup.java
// GUI version of Lookup.java
import java.awt.*;
import java.awt.event.*;
import java.applet.*;
import java.sql.*;
public class VLookup extends Applet {
String dbUrl = "jdbc:odbc:people";
String user = "";
String password = "";
Statement s;
TextField searchFor = new TextField(20);
Label completion =
new Label(" ");
TextArea results = new TextArea(40, 20);
public void init() {
searchFor.addTextListener(new SearchForL());
Panel p = new Panel();
p.add(new Label("Last name to search for:"));
p.add(searchFor);
p.add(completion);
setLayout(new BorderLayout());
add(p, BorderLayout.NORTH);
add(results, BorderLayout.CENTER);
try {
// Load the driver (registers itself)
Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver");
Connection c = DriverManager.getConnection(
dbUrl, user, password);
s = c.createStatement();
} catch(Exception e) {
results.setText(e.getMessage());
}
}
class SearchForL implements TextListener {
public void textValueChanged(TextEvent te) {
ResultSet r;
if(searchFor.getText().length() == 0) {
completion.setText("");
results.setText("");
return;
}
try {
// Name completion:
r = s.executeQuery(
"SELECT LAST FROM people.csv people " +
"WHERE (LAST Like '" +
searchFor.getText() +
"%') ORDER BY LAST");
if(r.next())
completion.setText(
r.getString("last"));
r = s.executeQuery(
"SELECT FIRST, LAST, EMAIL " +
"FROM people.csv people " +
"WHERE (LAST='" +
completion.getText() +
"') AND (EMAIL Is Not Null) " +
"ORDER BY FIRST");
} catch(Exception e) {
results.setText(
searchFor.getText() + "\n");
results.append(e.getMessage());
return;
}
results.setText("");
try {
while(r.next()) {
results.append(
r.getString("Last") + ", "
+ r.getString("fIRST") +
": " + r.getString("EMAIL") + "\n");
}
} catch(Exception e) {
results.setText(e.getMessage());
}
}
}
public static void main(String[] args) {
VLookup applet = new VLookup();
Frame aFrame = new Frame("Email lookup");
aFrame.addWindowListener(
new WindowAdapter() {
public void windowClosing(WindowEvent e) {
System.exit(0);
}
});
aFrame.add(applet, BorderLayout.CENTER);
aFrame.setSize(500,200);
applet.init();
applet.start();
aFrame.setVisible(true);
}
} ///:~
Much
of the database logic is the same, but you can see that a
TextListener
is added to listen to the
TextField,
so that whenever you type a new character it first tries to do a name
completion by looking up the last name in the database and using the first one
that shows up. (It places it in the
completion
Label,
and uses that as the lookup text.) This way, as soon as you’ve typed
enough characters for the program to uniquely find the name you’re
looking for, you can stop.
Why
the JDBC API
seems
so complex
When
you browse the online documentation for JDBC it can seem daunting. In
particular, in the DatabaseMetaData
interface – which is just huge, contrary to most of the interfaces you
see in Java – there are methods such as
dataDefinitionCausesTransactionCommit( ),
getMaxColumnNameLength( ),
getMaxStatementLength( ),
storesMixedCaseQuotedIdentifiers( ),
supportsANSI92IntermediateSQL( ),
supportsLimitedOuterJoins( ),
and so on. What’s this all about?
As
mentioned earlier, databases have seemed from their inception to be in a
constant state of turmoil, primarily because the demand for database
applications, and thus database tools, is so great. Only recently has there
been any convergence on the common language of SQL (and there are plenty of
other database languages in common use). But even with an SQL
“standard” there are so many variations on that theme that JDBC
must provide the large
DatabaseMetaData
interface so that your code can discover the capabilities of the particular
“standard” SQL database that it’s currently connected to. In
short, you can write simple, transportable SQL, but if you want to optimize
speed your coding will multiply tremendously as you investigate the
capabilities of a particular vendor’s database.
This,
of course, is not Java’s fault. The discrepancies between database
products are just something that JDBC tries to help compensate for. But bear in
mind that your life will be easier if you can either write generic queries and
not worry too much about performance, or, if you must tune for performance,
know the platform you’re writing for so you don’t need to write all
that investigation code.
There
is more JDBC information available in the electronic documents that come as
part of the Java 1.1
distribution from Sun. In addition, you can find more in the book
JDBC
Database Access with Java
(Hamilton,
Cattel, and Fisher, Addison-Wesley 1997). Other JDBC books are appearing
regularly.