Chapter 43

Developing Your Own Database Application

by George Reese


CONTENTS


Chapter 31, "Exploring Database Connectivity with JDBC," covered Java's JDBC API, which provides Java applications with access to relational databases. A database application, however, is much more complex than simply making JDBC calls. You must first have a properly set up database engine that can handle your application's needs. To understand how a database engine interacts with your application, you must understand the issues surrounding database access, especially as they relate to Java. You also want to display that information to the user. Finally, you have to write the actual application code.

This chapter looks at the different kinds of databases and how they meet the needs of various applications. During this examination, we will glance at object databases but focus on relational databases. To fully understand the issues behind database application development in Java, we will build a guest book database applet that allows people to visit your Web page, sign in, and make comments.

Different Database Systems

A database is a storage mechanism that facilitates the retrieval of data. Of course, there is more than one way to do this. The most well-known types of databases include hierarchical, relational, and object databases. Of these three systems, relational databases are overwhelmingly the system of choice.

Hierarchical Databases

If you have done any programming with the Windows registry, you have experience with a hierarchical database. Hierarchical databases, which were the prevalent database system before relational databases took hold, store data in a tree. You access its data by naming the data's location in the hierarchy. Unfortunately, a hierarchical system does not provide any way for data in one branch of the database to relate to data in another branch. Except for very specialized uses (such as the Windows registry), you do not see hierarchical databases very often any more-Java currently has no support for them.

Relational Databases

Relational databases enable you to access data based on its relation to other data. Instead of storing relational data as a tree, a relational database stores data in tables similar to a spreadsheet layout. Each table contains a set of related data. For example, in the guest book applet we are about to build, you have a comments table. As with a spreadsheet, you have columns that include e-mail address, visitor's name, date, and comment. You can also create a people table in which you store detailed information about people you know. For anyone who enters a comment in your comments table, you can cross-reference that information with the people table to get more detailed information about the person making the comments. With a spreadsheet, this arrangement means that you have two separate spreadsheets containing the data but no way to relate the data in the spreadsheets. A relational database, however, contains relationships that allow you to relate data in the comments table with data in the people table.

Relational databases also provide a special query language, SQL, that allows you to retrieve data based on its relationships. JDBC requires a basic level of SQL support from a relational database. If you have no experience with SQL, I recommend that you pick up a book on it, because a discussion of SQL is beyond the scope of this chapter. SQL is, however, a very simple language to learn. If you understand enough Java to be this far into a Java programming book, you should have only minimal difficulty reading the SQL in this chapter even without any SQL background.

Object Databases

Object databases are a relatively new class of database management systems that are growing in popularity. As an object-oriented programming language, Java supports the idea that the data of an object is inseparable from its behavior. In fact, object-oriented design dictates that an object's data should even be hidden from external systems.

Because a relational database is about storing pure data, it violates the object-oriented concept of encapsulation. In contrast, an object database does not store object data separate from the object itself. When you want to access object data, you access it through that object's data access methods just like you do inside a Java application. In fact, access to object databases in the future may even be as simple as calling specific Java methods. Unfortunately, Java access to object databases at this time is immature to nonexistent.

Structuring Your Relational Database

Before you can get started with any application, you must set up your database. You have to organize your data requirements into related tables of data. Figure 43.1 shows what is called a logical data model for the guest book application. It breaks down data into "entities" and shows how they are related to each other.

Figure 43.1: The logical data model for the guest book applet.

The most important thing to learn from this logical data model is what entities are in the database and in what ways we can relate them. The data model for our application is actually quite simple. A data model for a complex system, however, can span an entire wall.

The logical data model does not really say much about what data we want to store in the database. Once our logical data model is complete, we must define the data we want to store. In general, each row of each table requires a unique identifier. As a rule of thumb, this unique identifier, referred to as the primary key, is never used as a display value and should not have any meaning outside of uniquely identifying a specific row.

With primary keys defined for your database entities, you can begin defining the columns that make up each table. Some of these columns, like the e-mail address column for the comments table, have extended information contained in other tables. In the comments table, we will use the e-mail address for that person to relate it to the people table. Normally, we would use a primary key to create this relationship. Because users entering comments have no knowledge of the people table, however, we cannot rely on their ability to determine primary keys for relating comment data to people data. This relationship is called a join. Figure 43.2 shows the detailed table information in a diagram called the physical data model.

Figure 43.2: The physical data model for the guest book applet.

You may run into some situations in which many rows from one database entity are related to many rows of another database entity. Because databases do not allow you to store more than one value in a single field, you have to create a third table to capture this many-to-many relationship. The third table contains two columns representing the primary keys from the other two tables. The intersection of two primary keys defines a unique row and creates the relationship between the two entities.

An example of such a situation is a table relating football players to football teams. A football team has many players; over time, a player can belong to many teams. Figure 43.3 shows the physical data model that relates football players to football teams.

Figure 43.3: The intersection table defining a many-to-many relationship for football players to football teams.

With the entities fully defined in the form of a physical data model, we can now create the database tables using SQL. There are even some data-modeling computer applications that create tables for you based on the data model you draw. I used an mSQL database for the examples in this chapter. Listing 43.1 provides the table creation scripts for mSQL.

Note
mSQL is a small relational database engine provided for free to nonprofit users and at a low cost for commercial users. You can find more information about it at http://www.imaginary.com/Java.


Listing 43.1. The mSQL creation scripts for setting up the guest book applet support tables.

DROP TABLE comments\p\g
CREATE TABLE comments (
    comment_id INT       PRIMARY KEY,
    email      CHAR(40)  NOT NULL,
    name       CHAR(40)  NOT NULL,
    date       CHAR(30)  NOT NULL,
    comment    CHAR(255) NOT NULL
)\p\g

DROP TABLE people\p\g
CREATE TABLE people (
    people_id   INT       PRIMARY KEY,
    email       CHAR(40)  NOT NULL,
    first_name  CHAR(30)  NOT NULL,
    last_name   CHAR(30)  NOT NULL,
    birth_date  CHAR(30) 
)\p\g

DROP TABLE sys_gen\p\g
CREATE TABLE sys_gen (
    id          CHAR(30)  PRIMARY KEY,
    next_id     INT        NOT NULL
)\p\g

Java Database Issues

The biggest challenge of writing an object-oriented application against a relational database is mapping the objects in your object model to entities shown in the data model. On the surface, it looks like a fairly simple task because most of the objects in the object model have entities by the same name in the data model.

Objects in an object-oriented system, however, do not relate in the same way that data relates in a relational database. Because our applet does not have a complex object model, mapping objects to relational entities is not a problem. It is, however, a huge problem for complex Java applications.

Security Issues

Java does put some restrictions on applets for security reasons that can appear to be particularly limiting to the database developer. The following are two particular applet restrictions that affect database programmers:

The native call limitation affects programmers who need to use some sort of C library or operating-system-level library to design an applet. This is especially troublesome to applet writers who want to take advantage of a database-specific feature not supported outside of native calls. The mSQL JDBC driver does not use native libraries for database access, so this problem does not affect you if you are using mSQL. Some other drivers for the more common commercial database engines do make use of native library calls. You should refer to your JDBC driver documentation to see whether it is limited in this way.

To veteran client/server developers, however, the most troubling idea is probably that your Web server must be on the same machine to which your applet is connecting for database access. Specifically, most Java virtual machines restrict applets from connecting to any machine except the host that served the applet. The applet therefore cannot connect directly to any local or third-machine databases. As limiting as this particular restriction seems, a three-tier architecture provides a liberating solution.

The Power of Three Tiers

While the two-tier approach does try to centralize business processing on the server, the limitations of just two tiers tend to push a lot of processing onto the client machines. This architecture poses several problems:

With the guaranteed execution environment of the Java virtual machine and an easy-to-use Internet socket interface, Java is actually well suited to the implementation of three-tier systems. A three-tier application is one in which a third application layer exists between the client and server layers of the traditional two-tier client/server system. This middle layer has a wide variety of uses depending on the application in question.

The three-tier architecture uses the middle layer to separate business processing from the visual representation of data. This layer, called the application server, is responsible for knowing how to find and manipulate business data. The client evolves into a much leaner application responsible only for retrieving information from the application server and displaying it on the screen.

In addition to removing a huge processing burden from client machines, the application server can be used to consolidate enterprise-wide business rules. Where business rules have to be rewritten for each two-tier application thrust on the desktop, application servers process business rules in a single place for use by multiple applications. When the business rules change, a change to the application server takes care of that change for all the applications being run by the business.

Of specific interest to Java developers is the three-tier system's ability to hide any knowledge of the database server from the client. Because Internet clients view the applet or application as interfacing with a single application server, you can use that application server to determine such things as where the data really exists. Additionally, this back-end independence enables applications to scale much easier across CPUs. Figure 43.4 shows a three-tier architecture.

Figure 43.4: A three-tier Java applet or application.

It would be overkill to build the guest book applet as a three-tier system. If your application has a complex object model or a need to scale across multiple machines over time, however, a three-tier application is definitely the solution.

Communication among the Tiers

With any three-tier architecture, one of the greatest programming challenges is getting the three layers to communicate with one another. JDBC or some similar set of database access classes should handle the application-server-to-database-server communication in a manner transparent to the application developer. Communication between the client and application server is still undefined.

The two best methods for providing such communication in Java are Java sockets or distributed objects. Compared to sockets from other languages, Java sockets are quite simple to use. Sockets, however, force the developer to make esoteric decisions as to exactly what it is that is being communicated between client and application server, because method calls and object passing are better handled by the distributed objects solution. A socket solution generally best fits an application in which the scope of communication is limited and well defined. A bug-tracking system would be best implemented in this manner.

Distributed objects provide the more elegant solution. From the developer's point of view, the application server objects appear to be part of the same application as the client-they just reside on a central server and are available to other applications simultaneously. The developer handles communication simply through method calls.

Viewing the Data

The magic of any database application is the presentation of relational data in a form that is easy for the user to digest. The first part of the guest book applet provides a display of data in the comments table to potential users. Listing 43.2 shows GuestBookView.java, an applet that handles the viewing functionality of the guest book.


Listing 43.2. The GuestBookView applet that allows users to view comments made by visitors to your Web pages.

/**
 * GuestBookView.java
 * A look at all the comments listed in the guest book
 * through the GuestBook applet.
 */

import java.awt.*;
import java.applet.*;

public class GuestBookView extends Applet {
  private GridBagLayout applet_layout;
  private GridBagConstraints applet_constraints;
  private Button retrieve, next, previous;
  private Panel main_panel, comment_panel, status_panel;
  private TextArea comment;
  private TextField email, name;
  private Label status;
  int current_comment = 1;
  int total_comments = 0;

  /**
   * Initialize the appearance of the applet.
   * For the sake of speed, do not download comments until they
   * explicitly perform a retrieval.
   */
  public void init() {
    super.init();
    setLayout(applet_layout = new GridBagLayout());
    applet_constraints = createDefaultConstraints();
    addNotify();
    setBackground(Color.white);
    main_panel = new Panel();
    { // Set up the main panel
      GridBagConstraints constraints;
      GridBagLayout layout;
      Label tmp;

      main_panel.setLayout(layout = new GridBagLayout());
      constraints = createDefaultConstraints();
      constraints.insets = new Insets(2, 10, 2, 10);
      layout.setConstraints(tmp = new Label("Email"), constraints);
      main_panel.add(tmp);
      constraints.gridx = 1;
      layout.setConstraints(email = new TextField(30), constraints);
      main_panel.add(email);
      constraints.gridx = 2;
      layout.setConstraints(retrieve = new Button("Retrieve"), constraints);
      main_panel.add(retrieve);
      constraints.gridx = 0;
      constraints.gridy = 1;
      layout.setConstraints(tmp = new Label("Name"), constraints);
      main_panel.add(tmp);
      constraints.gridx = 1;
      layout.setConstraints(name = new TextField(30), constraints);
      main_panel.add(name);
    }
    applet_constraints.gridy = 1;
    applet_constraints.insets = new Insets(4, 2, 4, 2);
    applet_layout.setConstraints(main_panel, applet_constraints);
    add(main_panel);
    { // Set up the comments panel
      comment_panel = new Panel();
      comment_panel.setLayout(new CardLayout());
      comment_panel.add("1", getPanel(0, "", "", "", ""));
    }
    applet_constraints.gridy = 2;
    applet_layout.setConstraints(comment_panel, applet_constraints);
    add(comment_panel);
    { // Set up the status panel
      GridBagLayout layout;
      GridBagConstraints constraints;

      status_panel = new Panel();
      status_panel.setLayout(layout = new GridBagLayout());
      constraints = createDefaultConstraints();
      // Extra spaces in label for proper spacing in layout
      status = new Label("Ready.                                  " +
                         "                                              ");
      previous = new Button("<<");
      next = new Button(">>");
      previous.enable(false);
      next.enable(false);
      constraints.anchor = GridBagConstraints.SOUTHWEST;
      constraints.insets = new Insets(4, 2, 4, 2);
      layout.setConstraints(previous, constraints);
      status_panel.add(previous);
      constraints.gridx = 1;
      layout.setConstraints(next, constraints);
      status_panel.add(next);
      constraints.gridx = 2;
      layout.setConstraints(status, constraints);
      status_panel.add(status);
    }
    applet_constraints.gridy = 3;
    applet_constraints.anchor = GridBagConstraints.SOUTHWEST;
    applet_layout.setConstraints(status_panel, applet_constraints);
    add(status_panel);
    applet_constraints.anchor = GridBagConstraints.SOUTH;
    try {
      Class.forName("imaginary.sql.iMsqlDriver");
    }
    catch( Exception e ) {
      status.setText("An error occurred finding database drivers.");
    }
  }  

  /**
   * Handle the clicking on any of the buttons.
   * For retrieve, go to the database.
   * For previous, go to the previous comment.
   * For next, go to the next comment.
   * For previous or next, make sure to enable and disable as appropriate.
   */
  public boolean action(Event event, Object arg) {
    if( event.target instanceof Button ) {
      if( (Button)event.target == retrieve ) {
        retrieve();
        return true;
      }
      if( (Button)event.target == next ) {
        if( current_comment == total_comments ) {
          return super.action(event, arg);
        }
        ((CardLayout)comment_panel.getLayout()).next(comment_panel);
        current_comment++;
        if( current_comment == total_comments ) {
          next.enable(false);
        }
        if( current_comment > 1 ) {
          previous.enable(true);
        }
        resetStatus();
        return true;
      }
      if( (Button)event.target == previous ) {
        if( current_comment < 2 ) {
          return super.action(event, arg);
        }
        ((CardLayout)comment_panel.getLayout()).previous(comment_panel);
        current_comment--;
        if( current_comment < 2 ) {
          previous.enable(false);
        }
        if( current_comment < total_comments ) {
          next.enable(true);
        }
        resetStatus();
        return true;
      }     
    }
    return super.action(event, arg);
  }

  /**
   * Retrieve data from the database based on any selection
   * criteria.  A user can specify either email address or
   * name.
   */
  private synchronized void retrieve() {
    remove(comment_panel);
    comment_panel = new Panel();
    comment_panel.setLayout(new CardLayout());
    current_comment = 1;
    total_comments = 0;
    try {
      String url = "jdbc:msql://athens.imaginary.com:4333/db_web";
      java.sql.Connection connection;
      java.sql.Statement statement;
      java.sql.ResultSet result;
      String sql;
      int i = 0;

      status.setText("Retrieving from the database, " +
                     "this may take a minute...");
      connection = java.sql.DriverManager.getConnection(url, "borg", "");
      statement = connection.createStatement();
      sql = "SELECT comment_id, email, name, comment, date " +
            "FROM comments " + getWhere();
      result = statement.executeQuery(sql);
      // For each row, add to card layout a panel representing the row
      while(result.next()) {
        Panel tmp;
        
        i++;
        tmp = getPanel(result.getInt(1),
                       result.getString(2),
                       result.getString(3),
                       result.getString(4),
                       result.getString(5));
        comment_panel.add("" + i, tmp);
      }
      // redo the applet layout
      remove(status_panel);
      applet_constraints.gridy = 2;
      applet_layout.setConstraints(comment_panel, applet_constraints);
      add(comment_panel);
      applet_constraints.gridy = 3;
      applet_constraints.anchor = GridBagConstraints.SOUTHWEST;
      applet_layout.setConstraints(status_panel, applet_constraints);
      add(status_panel);
      applet_constraints.anchor = GridBagConstraints.SOUTH;
      total_comments = i;
      resetStatus();
    }
    catch( java.sql.SQLException e ) {
      remove(status);
      comment_panel = getPanel(0, "", "", "", "");
      applet_constraints.gridy = 2;
      applet_layout.setConstraints(comment_panel, applet_constraints);
      add(comment_panel);
      applet_constraints.gridy = 3;
      applet_constraints.anchor = GridBagConstraints.SOUTHWEST;
      applet_layout.setConstraints(status_panel, applet_constraints);
      add(status_panel);
      applet_constraints.anchor = GridBagConstraints.SOUTH;
      status.setText("A database error occurred: " + e.getMessage());
      total_comments = 0;
      current_comment = 1;
      next.enable(false);
      previous.enable(false);
    }
    validate();
  }

  /**
   * Provides a WHERE clause with an ORDER BY as an extra bonus
   * Needs to make sure user-entered fields do not have database
   * sensitive characters.
   */
  private String getWhere() {
    String e = email.getText().replace('\'', '"');
    String n = name.getText().replace('\'', '"');
    String where = "WHERE ";
    
    if( e.length() < 1 && n.length() < 1 ) return "ORDER BY comment_id";
    if( e.length() > 0 ) {
      where += "email = '" + e + "'";
      if( n.length() > 0 ) {
        where += " AND ";
      }
    }
    if( n.length() > 0 ) {
      where += "name = '" + n + "'";
    }
    return where + " ORDER BY comment_id";
  }
  
  /**
   * Creates a panel for database data.
   */
  private Panel getPanel(int id, String mail, String nom, String text,
  String day) {
    Panel panel =  new Panel();
    GridBagConstraints constraints;
    GridBagLayout layout;
    TextArea cmt = new TextArea(5, 50);
    TextField eml = new TextField(30);
    TextField nme = new TextField(30);
    
    panel.setLayout(layout = new GridBagLayout());
    constraints = createDefaultConstraints();
    constraints.gridx = 2;
    constraints.anchor = GridBagConstraints.NORTHWEST;
    constraints.insets = new Insets(2, 2, 2, 2);
    cmt.setText(text);
    eml.setText(mail);
    nme.setText(nom);
    cmt.setEditable(false);
    eml.setEditable(false);
    nme.setEditable(false);
    layout.setConstraints(eml, constraints);
    panel.add(eml);
    constraints.gridy = 1;
    layout.setConstraints(nme, constraints);
    panel.add(nme);
    constraints.gridy = 3;
    constraints.anchor = GridBagConstraints.CENTER;
    layout.setConstraints(cmt, constraints);
    panel.add(cmt);
    return panel;
  }

  /**
   * A way to avoid redoing the creation of constraints
   * being used everywhere in this applet.  This creates
   * a GridBagConstraints object and sets some defaults.
   */
  private GridBagConstraints createDefaultConstraints() {
    GridBagConstraints constraints = new GridBagConstraints();
    
    constraints.gridx = 0;
    constraints.gridy = 0;
    constraints.gridheight = 1;
    constraints.gridwidth = 1;
    constraints.weightx = 0.0;
    constraints.weighty = 0.0;
    constraints.fill = GridBagConstraints.NONE;
    constraints.anchor = GridBagConstraints.SOUTH;
    return constraints;
  }

  private void resetStatus() {
    // Extra spaces in setText() below for layout reasons
    if( total_comments == 0 ) {
      status.setText("No comments found.                     " +
                     "                                      ");
      next.enable(false);
    }
    else if( total_comments == 1 ) {
      status.setText("1 of 1 comment.                        " +
                     "                                       ");
      next.enable(false);
    }
    else {
      status.setText(current_comment + " of " + total_comments +
                     " comments.                             " +
                     "                      ");
      if( current_comment < total_comments ) {
        next.enable(true);
      }
      else {
        next.enable(false);
      }
    }
    if( current_comment < 2 ) {
      previous.enable(false);
    }
    else {
      previous.enable(true);
    }
  }
}

This example shows the greatest current disadvantage of Java as well as several strengths it has over CGI. Java's weakness lies in the AWT-specifically, the amount of code required to display relational data in a GUI. The applet itself is divided into three display panels:

The init() method sets up the default display for these three panels. Because the comment panel is actually made up of multiple panels designed to display rows from the database, I have separated the painting of that panel into a distinct method. The layout of all these panels (except the comment panel) depends heavily on the GridBagLayout layout manager. If you are not familiar with that layout manager, take some time to explore it now (refer to Chapter 16, "The Windowing (AWT) Package"). The complex layouts required for the dynamic display of database data often require the use of this difficult layout manager.

The comment panel uses the CardLayout layout manager to flip through comments. The CardLayout enables a panel to display a single component at a time and navigate back and forth among them. Each card of the comment panel's layout is thus a panel displaying one comment from the database.

The retrieval and display of database information occurs in the retrieve() method. When a user clicks the Retrieve button, the action() method triggers the retrieve() method which formulates a SQL statement using the getWhere() method. getWhere() puts together a WHERE clause for a SQL statement based on any data the user has entered into the text fields in the main panel. The retrieve() method then appends the WHERE clause onto the SELECT statement. For each row the applet finds in the database, it creates a panel and adds it to the CardLayout of the comment panel. During the entire process, status information is displayed to the user.

Data Entry

Among the more interesting things you can do in Java that you cannot do using any other means of displaying database information on the Web is the ability to validate data at the client level. Each time a user goes forward or backward through the list of comments, the applet can enable or disable buttons based on whether there are previous or additional comments to view. This ability is more important at data-entry time, where CGI requires users to wait until they have filled out an entire form and submit it before doing any validation. Listing 43.3 shows how we can prevent the user from submitting information until it has been validated.


Listing 43.3. The sibling GuestBook.java applet which handles the data-entry portion of the guest book applet.

/**
 * GuestBook.java
 * This applet allows people to enter comments
 * from their web browser and save them to my mSQL
 * database.
 */
import java.awt.*;
import java.applet.*;

public class GuestBook extends Applet {
  private TextField email, name;
  private Label label1, label2, label3, status;
  private TextArea comments;
  private Button save_button;
  private boolean error_disable;

  /**
   * Sets up the applet's look.
   */
  public void init() {
    super.init();
    setLayout(null);
    addNotify();
    resize(351,230);
    setBackground(new Color(16777215));
    email = new java.awt.TextField();
    email.reshape(67,4,157,22);
    add(email);
    label1 = new java.awt.Label("Email ");
    label1.reshape(11,7,45,15);
    add(label1);
    label2 = new java.awt.Label("Name");
    label2.reshape(11,40,48,15);
    add(label2);
    comments = new java.awt.TextArea();
    comments.reshape(11,87,325,109);
    add(comments);
    name = new java.awt.TextField();
    name.reshape(67,37,157,23);
    add(name);
    label3 = new java.awt.Label("Comments");
    label3.reshape(11,68,70,15);
    add(label3);
    save_button = new java.awt.Button("Save");
    save_button.reshape(252,4,87,26);
    add(save_button);
    status = new java.awt.Label("");
    status.reshape(12,202,322,19);
    add(status);
    checkButton();
    error_disable = false;
    try {
      Class.forName("imaginary.sql.iMsqlDriver");
    }
    catch( Exception e ) {
      status.setText("A Java error occurred.");
      error_disable = true;
    }
  }

  /**
   * Checks to make sure enough information exists to
   * allow saving to the database.  This is something
   * you cannot do in CGI-land.
   */
  private boolean validate_value(String str, boolean flag) {
    if( str.length() < 1 ) return false;
    if( flag ) {
      int i = str.indexOf("@");

      if( i == -1 ) return false;
      else return ((i != 0) && (i != str.length()-1));
    }
    return true;
  }

  /**
   * Performs the actual database save.
   */
  private void save() {
    status.setText("Saving to the database, this will take a minute...");
    try {
      String url = "jdbc:msql://athens.imaginary.com:4333/Testdb";
      java.sql.Connection connection;
      java.sql.Statement statement;
      java.sql.ResultSet result;
      int id = -1;

      connection = java.sql.DriverManager.getConnection(url, "borg", "");
      statement = connection.createStatement();
      result = statement.executeQuery("SELECT next_id FROM sys_gen " +
                                      "WHERE id = 'comment_id'");
      if( !result.next() ) {
        throw new java.sql.SQLException("Failed to generate id.");
      }
      id = result.getInt(1) + 1;
      result.close();
      statement.close();
      statement = connection.createStatement();
      statement.executeUpdate("UPDATE sys_gen SET next_id = " + id +
                              " WHERE id = 'comment_id'");
      statement.close();
      statement = connection.createStatement();
      statement.executeUpdate("INSERT into comments " +
                              "comment_id, email, name, comment, date) " +
                              "VALUES (" + id +", '" + getEmail() + "', '" +
                              getName() + "', '" + getComments() + "', '" +
                              (new java.util.Date()).toString() + "')");
      statement.close();
      connection.close();
      email.setText("");
      name.setText("");
      comments.setText("");
      checkButton();
      status.setText("Saved comment id " + id + ".");
    }
    catch( java.sql.SQLException e ) {
      status.setText("A database error occurred: " + e.getMessage());
      error_disable = true;
      checkButton();
    }
  }

  /**
   * Looks for key presses and other fun events.
   */
  public boolean handleEvent(Event event) {
    if( event.target == comments ||
        event.target == name || event.target == email ) {
      if( event.id == Event.KEY_PRESS ) {
        if( event.key == '\t' ) {
          if( event.target == email ) {
            name.requestFocus();
          }
          else if( event.target == name ) {
            comments.requestFocus();
          }
          else {
            email.requestFocus();
          }
          return true;
        }
        else {
          checkButton();
          return super.handleEvent(event);
        }
      }
      if( event.id == Event.ACTION_EVENT ) {
        if( !save_button.isEnabled() ) {
          return super.handleEvent(event);
        }
        save();
        return true;
      }
    }
    if (event.target == save_button && event.id == Event.ACTION_EVENT) {
      save();
    }
    return super.handleEvent(event);
  }

  /**
   * Enable or disable the save button as appropriate.
   */
  void checkButton() {
    if( error_disable ) {
      save_button.enable(false);
      return;
    }
    save_button.enable(validate_value(comments.getText(), false) &&
                       validate_value(email.getText(), true) &&
                       validate_value(name.getText(), false));
  }

  public String getEmail() {
    String str = email.getText();

    return str.replace('\'', '"');
  }

  public String getName() {
    String str = name.getText();

    return str.replace('\'', '"');
  }

  public String getComments() {
    String str = comments.getText();

    return str.replace('\'', '"');
  }
}

The Save button that triggers the database save remains disabled until the user has entered the following data:

When these criteria are met, the Save button becomes enabled and the user can choose to save the data to the database.

One final thing your data-entry processing should be concerned about is making sure that the user does not enter characters that will wreak havoc on your SQL statement. If, for example, a user puts an apostrophe into the comment, that creates a SQL statement with a misplaced single quote. In the guest book applet, I simply converted all instances of ' to ". A more proper way to handle the situation is to escape all instances of ' so that the database engine ignores it. Figure 43.5 shows the final look of the applet.

Figure 43.5: A screen shot of the completed guest book applet.

Summary

Database programming is a very complex task that requires several spheres of knowledge:

The focus of this chapter was understanding the mapping of relational entities into a graphical user interface. Unlike CGI, Java allows applications to process information dynamically on the browser. The trick to Java is creating a meaningful display of the information you retrieve from the database. In the guest book application developed in this chapter, we used a CardLayout layout manager and some navigational buttons to show visitor comments in a concise way that CGI is unable to handle. As an application grows more complex, however, you will want to map your relational data into business objects. Not only is it impossible for CGI to handle object-to-relational mapping, any attempt to do so produces an application that is impossible to maintain.