A major goal of this chapter is to give readers the means to actually try some hands-on relational database programming. That’s an ambitious goal, because relational databases are industrial-strength and industrial-sized pieces of software. Up until a few years ago, the only choice in a database management system was which of the commercial vendors would you buy from. More recently, the explosion of interest in open source software has led to a much larger number of choices, some of which require no financial outlay. Tables 27-1 and 27-2 show some popular commercial and non-commercial products and their characteristics.

There are some truly excellent databases which are available for free download over the Internet. Some of them even come with the source code, which provides additional learning opportunities. The last three in Table 27-2 are all implemented completely in Java and run on any up-to-date JVM. The first three products are only available on certain platforms.

The Mckoi database software is used as the example here because it comes with example programs, has good documentation, and is very easy to get running. Mckoi is largely the work of talented English programmer Toby Downer, backed by his employer Diehl and Associates who support the goals of the open source movement. This chapter was written using the 0.89 beta release of McKoi, and that software is on the CD. You should check the Mckoi website, download any more up-to-date release, and adapt to any changes in installation, pathnames, etc. Toby made this software open source so that others will have the chance to learn from his work. Take that opportunity! People get to be expert programmers by spending a lot of time reading code from others.

To get started, go to the Mckoi website at

www.mckoi.com/database 

Click on the “latest version” link under “Download the software.” You can download the zip file to your C: top level directory. It is less than 1.5MB in size, so it downloads quite quickly. You could instead copy the version 0.89 beta release from the CD to your disk. After the mckoi zip file is on your disk, unpack its contents using a command like this:

cd c:
jar -xvf mckoi0.89b.zip 

Jar files have the same format as zip files, so you can also use winzip or another archive extraction utility. The unzip creates a directory called mckoi0.89b containing the database management software (binaries and Java source code), some documentation, and sample programs. You must be running at least JDK 1.2 to use this database. Since Java performance has improved greatly from release to release, I recommend using the latest available JDK that is at the FCS (First Customer Shipment, after a release has finished beta testing) revision level. In Spring 2002, that was JDK 1.4. The installation guide that accompanies the Mckoi release even explains how to rebuild the source code. It is quite straightforward. You would recompile the database if you fixed a bug in its code, or if you wanted to get the performance boost of a more recent JDK release.

Make the Mckoi libraries visible to your java compiler and runtime. There are three jar files in the directory where you just extracted the release. These jar files are:

The commands shown here to make the libraries visible to the compiler are for Windows. Make the necessary adjustments for Unix, Linux, Mac, etc. There are at least three alternative ways of making the libraries visible on a Windows system:

java -jar mckoidb.jar 

The next step is to try running one of the example database programs that accompany the release. Go to the demo directory with this command:

cd c:mckoi0.89bdemosimple 

Then run the sample database application that comes with the release. Use this command (assuming you have put the mckoi jar file in the jrelibext directory):

java  SimpleApplicationDemo 

If all is well, you will see some sample output like this, assuring you that the database libraries have been properly installed. If you do not see output like this, you will need to debug the problem based on the output you do see.

Rows in 'Person' table: 12

Average age of people:  30.0833333333

All people that live in Africa:
  Grayham Downer
  Judith Brown
  Timothy French

All people that listen to either Beatles or Oasis:
  Grayham Downer listens to Beatles
  Ivan Wilson listens to Beatles
   ... 

A database works in the classic client/server way. There is one database and many clients talk to it. (Larger enterprises may have multiple databases, but these can be considered independent for the purpose of this chapter). The clients are typically remote systems communicating over TCP/IP networks. They may talk directly to the database (called a “2-tier” system) or to a business logic server that talks to the database (known as a “3-tier” system).

How does a client or business logic program initiate a dialog with a database manager? JDBC uses a piece of software called a database driver. The database driver is specific to each vendor, and it is a library level example of the Adaptor design pattern. It knows how to connect to its database, send requests over TCP/IP, and how to listen for replies from the database. Just as an operating system device driver hides the peculiarities of an I/O device from the kernel and presents a standard interface for system calls, each JDBC driver hides the vagaries of its particular database and presents a standard interface to Java programs that use JDBC.

Putting it another way, the purpose of a JDBC database driver is to know the low-level protocol for talking with its database at one end, and with JDBC classes and methods at the other end. It acts like a human language interpreter, moving information from one end and putting it in a standard form that is comprehensible to the other end (see Figure 27-1). You typically get a JDBC database driver from the database vendor. There are several different kinds of database drivers, depending on whether it is written in Java or native code, or whether it talks directly to the database or through another data access protocol such as Microsoft’s ODBC. None of that matters much to the applications programmer. As long as you have a working JDBC driver, you don’t care how it works. Essentially, all commercial and non-commercial databases now have excellent support for access from Java programs. There are good third-party libraries that can be used to access the Microsoft database products.

Figure 27-1. How JDBC establishes a connection between your code and a database.

First, you do a class.forName on the JDBC driver name. That causes its class to be loaded into the JVM that’s executing your program.

Class.forName("com.mckoi.JDBCDriver"); 

You don’t need to create an instance of the driver class. Simply getting it loaded is enough. Here’s how it works. Each JDBC driver has a static initializer that is run when the class is loaded, and in that code the driver registers itself with the JDBC. You can also load a JDBC driver into the DriverManager by adding an entry to the sql.drivers property of the JVM. For example

java -Dsql.drivers=com.mckoi.JDBCDriver  ... 

If you do this, you don’t need the above call to Class.forName. Whichever approach you take, JDBC now knows about this driver, and can make calls to it. The JDBC driver does 90% of the workthat is done in JDBC. Since Mckoi is an open source product, you can inspect the code to confirm how this works for yourself.

Next, your Java application program asks for a connection to the database, using a string that looks like a URL as an argument. The JDBC library has a class called java.sql.Connection that knows how to use that string to guide it in its search for the right database.

x.getClass().getName() 

The exact format of the pseudo-URL string will vary with each database, but it typically starts with “jdbc:” to indicate the protocol you will be using, just as “http:” indicates to a web server that you will be using the hypertext transport protocol. The string will then go on to give some indication of the database host name, the port number, and a database-specific subprotocol to use. The Mckoi database uses a pseudo-URL like this:

String url = "jdbc:mckoi:local://ExampleDB.conf?create=true"; 

That URL names a file called ExampleDB.conf on the local host in the current working directory that holds configuration information on the database. The parameter in the URL says that we expect to be creating a database in our program. Sun seems to want database implementors to cope with database creation commands based on SQL, not attributes passed in the URL. There aren’t any JDBC drivers that actually support that today. Sun’s preferred approach adds complexity because it would require a database to be able to parse SQL commands before it exists. The ‘create=true’ attribute to create a database is vendor-specific, but it’s also how Cloudscape (see Table 27-1) works for embedded databases. The exact form of the pseudo-URL will vary from vendor to vendor. You need to read the documentation that comes with the database. Then your code will call a static method of the overall JDBC driver manager to get you a connection based on that string, and strings representing a username and password.

connection = java.sql.DriverManager.getConnection(url, user, passwd); 

Behind the scenes, the DriverManager calls every JDBC driver that has registered, and asks it if that is a URL it can use to guide it to its database. If we have prepared the ground correctly, the URL will be recognized by at least one of the drivers. The first driver to connect to its database with this URL, username, and password, will be used as the channel of communication. The application program gets back a “Connection” object. (Strictly speaking, it gets an object that implements the Connection interface.) The session has been established, and the connection is now used for communication between your program and the database. Why doesn’t the application simply talk directly to the driver? It could do that, but then you don’t have a single standard library anymore—you have a collection of 50 different protocols and conventions for talking to 50 different databases. The point of the JDBC is to avoid that.

Connecting to a database is an expensive (time-consuming) operation. You would never design a servlet system that opened a new connection for every doPost() request. Most databases have a way to share connections among several different processes. This arrangement is known as “connection pooling.” JDBC 2.0 introduced a new and preferred approach to getting a connection. Instead of using a Driver directly, you use a DataSource object which you configure and register with a naming service (e.g., LDAP, YP, NIS+) that uses the Java Naming and Directory Interface (JNDI). That is intended for enterprise-level software and takes a lot more setting up, so we’ll stick to simple drivers in this chapter.

In summary, your application program knows which database it wants to talk to, and hence which database driver it needs to load. The JDBC driver manager knows how to establish the JDBC end of a database connection, and the driver knows how to establish the database end. They do it. The driver manager gives you back a connection into which you can pour standard SQL queries and get results.

Now we are at the point where we can start issuing SQL commands to our database and getting back results. We do this through a Statement object that we get from the connection object described in the previous section. Table 27-4 shows several methods in Connection.

You will invoke these methods on the java.sql.Connection object that you get back from the JDBC driver manager, as shown in an upcoming. You use a connection to create a Statement object. The statement object has methods that let you send SQL to the database. Thankfully, statements are blissfully simple. You send SQL queries as strings. In other words, the JDBC designers did not try to force-fit object-oriented programming onto SQL, perhaps by creating a Select class. Here’s how you send a select query to the database:

Statement myStmt = connection.createStatement();
ResultSet myResult;
myResult= myStmt.executeQuery( "SELECT * FROM Person;" ); 

The executeQuery() method takes a string as an argument. The string contains the SQL statement that you want to execute. In the code fragment above, the SQL asks for all data to be returned from the Person table. There is an object that holds your result set. Here, we’ve called it myResult and it belongs to the ResultSet class. We’ll talk more about ResultSet in a minute. Once you have a Statement object, you call one of its methods, shown in Table 27-5, to send SQL to the database. Statement has more methods than these, but these are the ones you’ll use most.

Standard SQL has an optional ";" at the end of each SQL statement. You can leave it off. It is omitted in all the tutorials at Javasoft.

The different SQL statements have different return values. Some of them have no return value, some of them return the number of rows they affected, and the select statement returns all the data it pulled out of the database. To cope with these different possible results, you need to call a different method depending on what kind of SQL statement you are executing. The most interesting case is the select statement that gets back an entire result set of data. The next section describes how this data is conveyed to your Java program.

Almost every JDBC interaction with a database can throw an exception, and you need to handle it appropriately in your code. JDBC defines four exceptions at present: SQLException (the most common), SQLWarning, BatchUpdateException, and DataTruncation. It is very important to write each handler so it outputs meaningful error messages for every exception it gets. If you don’t pay attention to this, you will find it much harder to debug database problems and error situations.

Threads and Database Programming

Older databases sometimes have support for asynchronous SQL operations, meaning that you can start another SQL statement before you get the results back from the past one. Java doesn’t need to use this kind of SQL because you can get the same effect by issuing the statements in separate Java threads. Your JDBC programs will be more portable if you avoid doing this in SQL.

When you write multithreaded Java code that uses JDBC, you must synchronize all your accesses to all shared data as usual. Shared data means any data that is accessed in more than one thread and also written by at least one of the threads. As always, it is the programmer’s responsibility to do this. If you do not properly synchronize data access, the data can be updated or read inconsistently (with a value partly from one thread and partly from another). That leaves your code with hard-to-debug data races and data corruption problems.

You look for these potential bugs in your code by examining all the data that each thread accesses. If there are any variables that may be accessed by two threads at once, you need to synchronize the access. Chapter 11 on threads has an example of this issue, and how to do the synchronization.

Mckoi database has a visual query tool!

The Mckoi database is excellent software and provides some wonderful learning opportunities. The source code includes a visual SQL query tool, as shown in Figure 27-2. This is a Swing application that allows you to type in SQL queries in the upper window, press the “run” button, and see the results interactively.

Figure 27-2. Mckoi GUI.

The Mckoi program was invoked with these commands:

cd mckoidemosimple
java com.mckoi.tools.JDBCQueryTool -url "jdbc:mckoi:local:/ExampleDB.conf"
-u user -p "pass1212" 

There are two commands there, shown on three lines to fit on this page.

As we saw in the previous chapter, the SELECT statement extracts data from a database. Here’s an example which should be prefaced with the warning that columns are numbered starting with 1, not zero. That is an SQL convention that really had to be respected by Java. If we run this Java code fragment,

ResultSet result;
result = statement.executeQuery( " SELECT Person.name, Person.age "
                                                             + "FROM Person "
                                                             + "WHERE Person.age = 24 " );
while (result.next()) {
       String p = result.getString(1);
       int a = result.getInt(2);
             System.out.println( p + " is " + a + " years");
} 

we’ll get output like this:

Robert Bellamy is 24 years
Timothy French is 24 years
Elizabeth Kramer is 24 years 

Relating that output to the code fragment shows how the ResultSet object can hold multiple values. I like to think of ResultSet as being similar to a 2D array. Instead of incrementing the most significant index variable, you call the result method next(). Each time you call next(), you are moved on to the next record in the result set. You need to call next() before you can see the first result record, and it returns false when there are no more result records, so it is convenient for controlling a while loop. That does make it different from an Iterator, however, so be alert to that difference. As a reminder, the Iterator next() method returns the next object, not a true/false value. A true/false value can be returned for a result set next() because there is another set of methods for actually getting the data. Read on to find out what!

You get individual values from a column within a record by calling one of the many methods whose signature looks like this:

SomeType  getSomeType(colNumberOrName); 

The argument can be the name of the attribute, or the column number (which starts at 1, remember). Thus, the class ResultSet has methods getBlob(), getBigDecimal(), getDate(), getBytes(), getInt(), getLong(), getString(), getObject(), and so on, for all the Java types that represent SQL types and for a column name and column number argument. The getObject() is interesting. If the database supports it, you can put a Java object into the database! You can then retrieve it later, and invoke methods on it. So your database may be able to store and catalog serialized Java objects as well as data.

Column numbers should be used for columns that are not explicitly named in the query, such as when you do a “select ‘*’”. Column names can be unreliable in this case, but otherwise they document the intent of your program better. Another advantage of using column names vs. column index for the ResultSet ‘get’ methods is that your code doesn’t break when your query changes to include more columns. Access by name might run into limitations of JDBC drivers, though. Some drivers allow access to the result set columns only in the order of the index. If you use by name and try to access columns out of order, you will get an exception.You can see all of the get-methods if you review the javadoc HTML pages for java.sql.ResultSet.

ResultSet result1, result2
result1 = myStmt.executeQuery( someSQLString );
while (result1.next()) {
    ...
    result2 = myStmt.executeQuery( someSQLString2 ); // NO!  blows
                                                       //away result1
} 

...
Statement myOtherStmt = connection.createStatement();
while (result1.next()) {
       ...
       result2 = myOtherStmt.executeQuery( someSQLString ); // OK.
} 

Good programming practice says that you should close Statement objects explicitly when they are no longer needed, with a statement like this:

myStmt.close(); 

Closing a statement when you are finished with it is important because it frees up resources (like locks and caches) on both the server and the client.

Statement stmt = conn.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE  ); 

Performance has always been one of the top concerns of database vendors, and they often go to some lengths to find ways to speed up queries. One of the bottlenecks is the time taken to package up a query, ship it over TCP/IP, and get it into the database where the SQL interpreter can start working on it. In other words, the network latency has a cost.

To reduce the overhead of network latency, many vendors support a way to batch several SQL statements together and send them to the database as a group. You can batch together any statements that have an int return type, which basically means “any SQL statements except for select.” You can see why. You are sending over a group of SQL statements to be executed together, but there is no mechanism defined for getting back the result set for each select. It is not that hard to invent such a mechanism (e.g., executing a batch returns an array of ResultSet), but this has not been done.

To bundle a group of SQL statements in a batch, you create a Statement object as usual:

Statement myStmt = conn.createStatement(); 

Then, instead of issuing an execute call for the statement, you instead do a series of addBatch(), like this:

myStmt.addBatch( myNonSelectSQL0 );
myStmt.addBatch( myNonSelectSQL1 );
myStmt.addBatch( myNonSelectSQL2 ); 

Finally, when you are ready to send the whole batch to the database, invoke the executeBatch() method:

int [] res = myStmt.executeBatch(); 

Batching SQL statements is so easy, there’s no reason to avoid it. That will cause all the statements to be sent to the database, and executed as a batch one after the other. The results come back in the form of an array of int, where the ith element holds the row count result of the ith statement in the batch (or zero if it did not return a row count).

Support for batches of statements came in with JDBC 2.0, but is not supported in the beta version of the Mckoi database used here. However, it is an easy feature to add, and will probably be in the Mckoi final release.

In the previous chapter we referred to “database integrity” and explained how a fairly common situation required either all of a group of statements to be executed or else none of them. The way you do this is to group the statements together in a “transaction.” You execute the transaction in a temporary working area internal to the database. Then, based on other information from your environment, you either “commit” or “rollback” the transaction. Committing the transaction means you let all the data from the working area be copied to the database so your statements have taken effect. A rollback of the transaction means you delete the working area without copying it to the main database so none of your statements affect the database.

Transaction commitment is done through the Connection object. When a JDBC driver starts up, the Connection is in auto-commit mode. That means the Connection automatically commits changes after executing each individual statement. You can turn that off and control when commits or rollbacks are done by invoking this method on your Connection object:

boolean savedCommitValue = conn.getAutoCommit();  // save the current value
conn.setAutoCommit(false);             // turn off stmt-by-stmt commits 

Then execute as many SQL statements as makes sense for your transaction; these will frequently be grouped in a batch. Look to see if they all completed successfully, and commit the transaction. You can also restore the old setting of autocommit:

int [] res = myStmt.executeBatch();
conn.commit();    // commit the changes
conn.setAutoCommit(savedCommitValue);  // restore previous value. 

If, however, an SQLException was raised, part of the recovery from that might be to issue a rollback:

conn.rollback();  // drop the partially completed changes. 

The statements within a transaction are all the statements that you issued on a given connection since the previous commit() or rollback(). Another way of looking at this is to note that Connection and transaction are almost synonymous— you can only have one open transaction per connection. So if you want to update a database concurrently and transactionally, the most practical way can be to use one connection per transaction per thread.

Another way to boost performance is to precompile the SQL statement using what is termed a “prepared statement.” That technique and the related one of “stored procedures” are described in this section.

A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times, often changing some of the values at runtime. You get a PreparedStatement with a method of your Connection object. It’s easiest to see with a code example:

PreparedStatement pstmt = conn.prepareStatement(
    "UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?");

pstmt.setBigDecimal(1, 150000.00);
pstmt.setInt(2, linden4303);
pstmt.executeUpdate();

pstmt.setBigDecimal(1, 85000.00);
pstmt.setInt(2, jenkins2705);
pstmt.executeUpdate(); 

That code will set employee linden4303’s salary to $150,000, and employee jenkins2705’s salary to $85,000. The question marks in the SQL query represent data values that will be filled in before the statement is executed. It works like arguments to a procedure, with one difference: any of the question mark fields that you don’t change will retain whatever value you have previously set them to, so you only need to set fields that change.

PreparedStatement has its own versions of the methods executeQuery(),executeUpdate(), and execute(). In particular, PreparedStatement objects do not take an SQL string as a parameter because they already contain the precompiled SQL statement you previously created.

Let’s move on to take a look at stored procedures. As we saw in the previous chapter, these are a group of SQL statements bundled together as one unit that can be called from your program. That’s where the “procedure” part of the name comes from. The “stored” part of the name is because the procedure can be precompiled by the SQL interpreter and actually stored in the database. A stored procedure is used when you have a group of SQL statements that, taken together, carry out some task like adding a new account and initializing it. Up until now, stored procedures could not be moved outside the database, and could not be linked to software components or external libraries. These disadvantages disappear when you write stored procedures in Java.

You have two choices for creating stored procedures. You can create them using SQL commands to install and manage stored procedures, and submit these commands using executeUpdate() in the normal way. Or, you can write the stored procedure following the SQLJ conventions. SQLJ is an industry standard covering how to embed SQL statements into Java methods and how to use Java methods for stored procedures. There is more information on SQLJ at www.sqlj.org.

Using SQLJ means writing a stand-alone Java program to contain your stored procedure. This is exciting and interesting because it means that even your stored procedures are now portable between different databases. Write a public static void method in a Java class. That method will have the usual code to get a Connection, create a Statement, and execute it. You compile it and put it in a jar file. Then you use the SQLJ library to install the jar file in the database management system. There is a special SQL syntax (which varies between databases) that lets you invoke your stored procedure. We won’t cover the specialized technique here, except to say that there are full examples in the “ Further Reading ” section at the end of the chapter.

This section shows the complete program to create, update, and select from a database using JDBC. A longer version of this code comes with the Mckoi database and can be found in directory c:mckoidemosimple. The code has been split into two programs there for convenience, one to create the tables, and one to query them.

/**
 * Demonstrates how to use JDBC.
 */

import java.sql.*;

public class Example {

  public static void main(String[] args) {

    // Register the Mckoi JDBC Driver
    try {
      Class.forName("com.mckoi.JDBCDriver");
    }
    catch (Exception e) {
      System.out.println("Can't load JDBC Driver. " +
                         "Make sure classpath is correct");
      return;
    }

    // This URL specifies we are creating a local database.  The
    // config file for the database is found at './ExampleDB.conf'
    // The 'create=true' argument means we want to create the database.
    // If the database already exists, it can not be created.
    // So delete .data*  when you want to run this again.
    String url = "jdbc:mckoi:local://ExampleDB.conf?create=true";

    //  Use a real username/password in a real application
    String username = "user";
    String password = "pass1212";

    // Make a connection with the database.
    Connection connection;
    try {
      connection = DriverManager.getConnection(url, username, password);
    }
    catch (SQLException e) {
      System.out.println("Connect problem: " + e.getMessage());
      return;
    }

    // --- Set up the database --
    try {
         // Create a Statement object to execute the queries on,
         Statement statement = connection.createStatement();
         ResultSet result;

         System.out.println("-- Creating Tables --");

         // Create a Person table,
         statement.executeUpdate(
   "        CREATE TABLE Person ( " +
   "           name      VARCHAR(100) PRIMARY KEY, " +
   "           age       INTEGER, " +
   "           lives_in  VARCHAR(100) ) " );

         System.out.println("-- Inserting Data --");

         statement.executeUpdate(
   "       INSERT INTO Person ( name, age, lives_in ) VALUES "
   + "         ( 'Robert Bellamy', 24, 'England' ), "
   + "         ( 'Grayham Downer', null, 'Africa' ), "
   + "         ( 'Timothy French', 24, 'Africa' ), "
   + "         ( 'Butch Fad', 53, 'USA' ), "
   + "         ( 'Judith Brown', 34, 'Africa' ) ");

          System.out.println("-- SQL queries --");
          // get average age of the people
          result = statement.executeQuery("SELECT AVG(age) FROM Person");
          if (result.next()) {
              System.out.println("Av. age:  " + result.getDouble(1));
         }
         System.out.println();
         // List the names of all the people that live in Africa
         result =  statement.executeQuery(
           "SELECT name FROM Person WHERE lives_in = 'Africa' ");

         System.out.println("All people that live in Africa:");
         while (result.next()) {
            System.out.println("  " + result.getString(1));
         }

         // Close the statement and the connection.
         statement.close();
         connection.close();

      }
      catch (SQLException e) {
         System.out.println(
         "An SQLException occurred: " + e.getMessage());
      }
      catch (Exception e) {
        e.printStackTrace(System.err);
      }
   }
} 

Make sure the three Mckoi jar files are in your classpath, then you can compile and run this code with these commands:

javac Example.java
java Example 

The output will look like this:

-- Creating Tables -
-- Inserting Data -
-- SQL queries -
Av. age:  27.0

All people that live in Africa:
  Grayham Downer
  Timothy French
  Judith Brown 

“Meta-anything” is a higher or second-order version of the anything. Metadata is data about data. The classic example of metadata is file and directory information on your disk drive. You don’t directly put it there, but you need it to keep track of your real data, and it is maintained by the system on your behalf. Databases have a large amount of metadata describing their particular capabilities and configuration.

The database metadata is going to be different for each database, and JDBC lets you get hold of it through the java.sql.DatabaseMetaData interface. You get an instance of the Metadata class by invoking a method of Connection. There you will find 100 or so fields and methods that you can use to find out specific details on the database. For example, it can tell you if the database supports transactions, and if so, to what level.

You use the database metadata when you know your code is going to run against several different databases. By looking at the metadata, your code can discover the individual features of a database, and perhaps take advantage of performance-related options. Often, but not always, there is a slower more standard way to achieve an effect, and you may prefer to write your database application code that uses that, instead of querying the database about its advanced features. Using database metadata is an advanced technique, beyond the scope of this book. The Javadoc documentation is extensive if you want to pursue this topic further.

Result sets also have metadata. An object of type java.sql.ResultSetMetaData can get information about the columns in a ResultSet object. Here is an example. The following code fragment creates a ResultSet and gets the corresponding ResultSetMetaData object from it. The code then uses that object to find out two pieces of information about the result set.. It calls two methods, one to find out how many columns the result has, and one to learn whether the first column in the result set can be used in a WHERE clause (i.e., it is a “searchable” column).

ResultSet result = statement.executeQuery(
   "SELECT c1, c2 FROM myTable; "

ResultSetMetaData rsmd = result.getMetaData();
int numberCols = rsmd.getColumnCount();
boolean b = rsmd.isSearchable(1); 

There are some excellent book-length treatments of relational databases and JDBC in particular. One book I like is the JDBC API Tutorial and Reference (Addison Wesley, Reading: MA, 1999), by Graham Hamilton and Rick Cattell, and then by Maydene Fisher, and then by Seth White and Mark Hapner, and finally by Maydene and Seth again. If you buy this book, be sure to get the most up-to-date edition!

In addition, Sun has an online tutorial on JDBC at java.sun.com/docs/books/tutorial/jdbc/index.html. This contains some of the same material in the JDBC API Tutorial book.

This is a brief light relief section because I inserted it at the very last minute as the book went to press. Now that the book is finished, I had the time to go web surfing again and discovered two amazing products.

The first one is another free download from IBM’s alphaworks. They call it the “Self Voicing Kit,” and it lets your Java applications speak! You should buzz over to www.alphaworks.ibm.com/tech/svk right now and download the 11 MB speech kit for Windows. Install it by unzipping the download and then running

java install 

Then run an example application like this:

cd c:/jdk1.4b3/demo/jfc/SwingSet2
java -jar SwingSet2 

That will start up a Swing demo, and it will be accompanied by a voice that comments on what you are doing and identifies components on the screen. This is intended as an evaluation, so the library times out after 45 minutes. But it is an amazing proof of concept. It works and it works well. The download includes everything you need to make Java talk.

The Self Voicing Kit can make a Java application automatically speak to the end user. It is quick to add a speaking interface, too. It uses the “accessibility” hooks that allow disabled users to operate their PC (large fonts, etc., and now spoken descriptions of what’s on the screen). Wonderful!

The second amazing Java thing that I stumbled across today is a Java OS for the Compaq iPAC Pocket PC. Leading edge PDAs now have the spec of a PC from a few years ago (200MHz CPU with 64 MB memory) and are powerful enough to run J2SE. You can buy the $20 Jeode JVM for the Compaq from Insignia Solutions and run Java Micro Edition. Or you can get the $100 Java OS from SavaJe Technologies and run full Java 2 Standard Edition 1.3.1 on your iPAQ or Psion netBook! You can develop Java programs on your desktop and deploy them onto the PDA with ease—as long as the application plus libraries fit in 60 MB and on a 240 by 320 pixel screen. Version 1.0 supports the Compaq iPAQ 32 MB and 64 MB color models such as the iPAQ H3600. The next release of iPAQ will come bundled with the Jeode JVM and Bluetooth (wireless connectivity standard) support. You can download a 30-day Java evaluation from www.savaje.com and then buy the license online to remove the time restriction. Wow! Full Java on a PDA! I don’t know about you, but this gadget is an immediate “must have” for me.

The SavaJe software includes several applications that run in the limited PDA memory. There is a browser, mp3 player, email client, a personal information manager, games, an editor, the MS-Office compatible ThinkFree suite, and so on. Add on an ethernet adapter, and you can run this PDA as a Java-based web server! Throw in a folding keyboard from ThinkOutside, and now you’re cooking with gas.

You can buy these products from Handandgo at their website, www.handandgo.com. Looking at their html files, it is clear they make extensive use of Java servlets to run the site! It’s amazing what you can find when you have time to web surf. Now I’m getting a Java-capable iPAQ; I will probably never again have free time to surf.