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.

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

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

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

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

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

• Of Java, Databases, and Really Cool Dead Guys
  Aaron Alpar explains JDBC, as well as where he goes for programming explanation.

• Ever Feel Like Life Is Just Scrolling By?
  In a Java-code sidebar, Alpar shows you how to use JDBC to implement an aspect of cursor control.