Connecting to databases with JDBC

Bruce Eckel’s Thinking in Java Contents | Prev | Next

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).

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( ).

  1. That
    you’re using JDBC with “jdbc”
  2. 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”
  3. 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.

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.


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:

  1. 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.)
  2. Select
    the “people” table within the database. From within the table,
    choose the columns FIRST, LAST, and EMAIL.
  3. Under
    “Filter Data,” choose LAST and select “equals” with an
    argument of Eckel. Click the “And” radio button.
  4. Choose
    EMAIL and select “Is not Null.”
  5. 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");

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

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.

More by Author

Must Read