Connecting to databases with JDBC

Bruce Eckel’s Thinking in Java

Contents | Prev | Next

with
JDBC

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.

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

force the loading using

Class.forName( )

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";

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

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.

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.

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

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

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

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?

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

Connecting to databases with JDBC

Getting
the example to work

A
GUI version of the lookup program

Why
the JDBC API

CodeGuru Staff

Company

Categories

Connecting to databases with JDBC

Getting the example to work

A GUI version of the lookup program

Why the JDBC API

CodeGuru Staff

Company

Categories

Getting
the example to work

A
GUI version of the lookup program

Why
the JDBC API