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

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

//: 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.



Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Today's agile organizations pose operations teams with a tremendous challenge: to deploy new releases to production immediately after development and testing is completed. To ensure that applications are deployed successfully, an automatic and transparent process is required. We refer to this process as Zero Touch Deployment™. This white paper reviews two approaches to Zero Touch Deployment--a script-based solution and a release automation platform. The article discusses how each can solve the key …

  • On-demand Event Event Date: December 18, 2014 The Internet of Things (IoT) incorporates physical devices into business processes using predictive analytics. While it relies heavily on existing Internet technologies, it differs by including physical devices, specialized protocols, physical analytics, and a unique partner network. To capture the real business value of IoT, the industry must move beyond customized projects to general patterns and platforms. Check out this webcast and join industry experts as …

Most Popular Programming Stories

More for Developers

RSS Feeds