All Categories :
Java
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.
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.
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
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:
- Limited access to native calls
- Limited network access
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:
- Client-side resource requirements balloon
with the extra processing needs. It is not uncommon to find business
applications requiring Pentiums with 32M of RAM.
- User interface and business processing
tend to get rolled together, especially with the rapid application
development tools on the market. With the user interface so closely
tied to business processing, changes to one end up having a direct
impact on the other, making maintenance a headache.
- With all this redundant processing occurring
on many client machines rather than in a central location, new
applications are forced to reinvent the wheel when dealing with
the same business processing.
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.
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:
- A main panel that allows
a user to specify filter criteria and initiate a search.
- A comment panel that displays
one comment at a time from the list of comments retrieved from
the database.
- A status panel that shows
status information to the user. In addition, navigation buttons
are placed here to flip through the comments being displayed on
the comment panel.
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.
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:
- An e-mail address in the form of user@host.
The application does not validate that the user or host is actually
real.
- A name of one character or more.
- A comment of one character or more.
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.
Database programming is a very complex task that requires several
spheres of knowledge:
- Understanding relational databases and
your choice of DBMS.
- Understanding how objects can map to relational
entities.
- Understanding the Java programming language.
- Understanding how JDBC allows Java applications
to access relational databases.
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.
Contact
reference@developer.com with questions or comments.
Copyright 1998
EarthWeb Inc., All rights reserved.
PLEASE READ THE ACCEPTABLE USAGE STATEMENT.
Copyright 1998 Macmillan Computer Publishing. All rights reserved.