"Java Alley"
by  Bruce Eckel
Web Techniques, October 1997

Web Techniques grants permission to use these listings for private or
commercial use provided that credit to Web Techniques and the author is
maintained within the comments of the source. For questions, contact
editors@web-techniques.com.
LISTING ONE

//: 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") );
      }
    } catch(Exception e) \{
      e.printStackTrace();
    }
  }
}

LISTING TWO
//: 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());
      }
    }
  }
  static class WL extends WindowAdapter \{
    public void windowClosing(WindowEvent e) \{
      System.exit(0);
    }
  }
  public static void main(String args[]) \{
    VLookup applet = new VLookup();
    Frame aFrame = new Frame("Email lookup");
    aFrame.addWindowListener(new WL());
    aFrame.add(applet, BorderLayout.CENTER);
    aFrame.setSize(500,200);
    applet.init();
    applet.start();
    aFrame.setVisible(true);
  }
}