|
Articles Index
Order Entry Prototype - Part I
By Michael Meloan
December 1999
Expanding upon the article
"JDK 1.2 Roadmap: Putting It All Together" by Monica Pawlan, this article
tells the story behind a rapid prototype project that uses SQL
commands and the JDBC API. This article covers the definition of database tables, use of Swing components, and gives a detailed code walkthrough with plenty of screen
captures.
In the article "
JDK 1.2 Roadmap: Putting It All Together" we met Kate Cookie, owner and operator of "Duke's Bakery". Kate had hired
programmer Madhavi Rao to design a software architecture for Duke's Bakery.
After completing that job, Madhavi was recruited as part of a large scale
Java development project for a web-based retailer.
When Kate called, requesting a prototype system, Madhavi referred the job to me.
After a number of meetings with Kate, where she described her business and
her goals, we settled on an approach. I would put together a rapid prototype
so that she could begin using the application to provide a more refined set of
requirements as her needs evolved.
Kate said she wanted to focus initially on a few specialty items from her
thriving local shop. These items would be sent to customers via express
mail. Specifically, she chose organic wheat loaves, small carrot cakes,
naan bread, and bagels. She wanted an application that would allow her to
enter customer data, and when that customer's data was entered, she could bring
up that information for follow-on orders by entering the customer's phone
number simply by clicking a Find button. She also told me that she was
using a Windows PC with Microsoft Access. I reminded her the platform was
of little consequence, due to Java technology's inherent portability, and with
the JDBC API, we would be able to use standard
SQL commands to access the
data. If at a later time we wanted to use another database product in place
of Microsoft Access, we could easily do it, with minimal changes to the
database setup statements. Kate was excited, and I went to work!
Defining the Database Environment
The first step
was to define the database environment. I decided on two
tables within a database called BakeryBook. The tables are
Addresses and Orders. The tables are logically linked by
an ID field, which is the primary key in the Addresses table,
and a secondary field called LinkAddrTbl in the
Orders table. When orders are made, the ID field
from the Addresses table is included in the Orders table
so that order data can be connected to the customer data. The tables
are described in the Microsoft Access panels below.
Addresses table - field descriptions ID is primary key
All table
fields are text except ID, which is Autonumber long integer.
Autonumber fields generate an automatic unique number for every record
written. For this reason Autonumber fields are a natural choice for
primary key, if no other design considerations override this decision.
The tiny key icon indicates that ID is the primary key of the
Addresses table.
The Addresses table will be the target when Kate enters customer data
during phone-in orders, the primary way she anticipates using the software.
She plans to ship her fresh breads via overnight airfreight. The prototype
software uses a phone number as the search key to pull up customers that
have already been added to the database. Once the name, address and other
information are up on the screen, Kate will be able to store an order by
clicking a radio button to generate a pop-up window, and entering a number
of items for each of the four categories. Such as wheat loaves or bagels.
The Orders table fields are described below.
Orders are written out to the Orders table.
CustID is primary key.
The LinkAddrTbl pointer is a long integer that points back to master
record
in the Addresses table. The wheat, cake, naan and bagel fields
are long integers
and store the orders entered after a customer is located in the Addresses
table.
JDBC-ODBC (Open Database Connectivity)
ODBC is an API defined by Microsoft. Prior to Sun's development of the
Java JDBC API, ODBC was the most widely used programming interface for
accessing relational databases. It offers the ability to connect with
virtually all databases and platforms. But ODBC uses a C interface, which
presents a number of drawbacks in terms of security, implementation, robustness
and portability. A literal translation of the ODBC C-based API into a
Java API would not be feasible due to the extensive use of pointers.
ODBC can be used from within the Java platform, which is best done with the
help of the JDBC API, in the form of the JDBC-ODBC Bridge.
The Bridge
The JDBC API is the preferred interface for working with SQL. It is easy
to use because the programmer does not need to worry about either memory
management or byte alignment. JDBC builds on ODBC rather than starting
from scratch. The JDBC-ODBC Bridge is itself a JDBC driver defined in
the class sun.jdbc.odbc.JdbcOdbcDriver. The Bridge defines
the JDBC
subprotocol odbc. In the future, development of pure Java JDBC Drivers
will make the JDBC-ODBC Bridge unnecessary, but in this application, we
need it to connect with Microsoft Access.
Microsoft has introduced new APIs beyond ODBC, such as OLE (Object Linking
and Embedding) DB, ADO (Active X Data Objects), and RDS (Remote Data Service).
OLE DB and ADO are also object-oriented interfaces to databases that
can execute SQL commands. OLE DB, however, is a low-level interface
designed for tools rather than developers. ADO is newer and more like
the JDBC API, but it is not pure Java. RDS provides functionality
similar to the JDBC API's RowSet facility, but RDS is
not written
in the Java programming language, and is not portable.
Two-Tier and Three-Tier Models
In the two-tier model, a Java applet or application talks directly to the data
source. This requires a JDBC driver that can communicate with the data source,
such as our Access database. A user's commands are delivered to the database
or other data source, and the results of those statements are sent back to the
user.
The data source might be located on another machine to which the user is
connected via a network. In this configuration, the user machine is the
client, and the machine hosting the data source is the server. The network
can be an intranet or the Internet.
In the three-tier model, commands are sent to a "middle tier" of services,
which then sends the commands to the data source. The data source processes
the commands and sends the results back to the middle tier, which then sends
them to the user. The three-tier model offers greater control over the kinds
of updates that can be made, and it simplifies the deployment of applications.
In many cases, the three-tier model can also offer performance advantages.
For Duke's Bakery, however, the two-tier model will do the job quite nicely.
Setting up Microsoft Access Under Windows 98
Kate Cookie is using a Windows 98 machine, which already has Microsoft
Access installed, so we need to follow a few setup steps to communicate
through the JDBC-ODBC bridge, and connect to the BakeryBook database.
Inside the Windows Control Panel, double click ODBC
Data Sources.
The following window will appear.
The Duke's Bakery database is called BakeryBook.mdb. Select that entry,
then click the Add button. Another window labeled Create New Data
Source will
appear. Select the Microsoft Access Driver entry, then click the
Finish
button.
The window below will appear. The Duke's Bakery database name is
BakeryBook. To find the directory
path of the database location, click the Select button, which allows a
directory search. After establishing the database name, path, and the
description, click the Advanced button.
The following window will appear
For demonstration purposes, the default
Login name "anonymous" has
been
entered, with a password "guest." Click OK to dismiss this
dialog and
click OK on all remaining ODBC windows. This completes the setup procedure.
Now we're ready to start looking at some code!
Code Walkthrough
I developed the prototype GUI using the Java
Foundation Classes (JFC). They
consist of the Abstract Window Toolkit (AWT), the Accessibility API,
the 2D API, enhanced support for drag-and-drop capability, and Swing.
Swing components are often referred to as lightweight components. Since
they are written completely in Java programming, they are generally not
"weighted down" by the complex GUI considerations imposed by the host
platform. Heavyweight components are undesirable for two reasons:
- Equivalent components
on different platforms do not necessarily function
in the same way.
- The look-and-feel of
each component is tied to the host operating system.
Here is a quick review of the important features distinguishing Swing from
older AWT components.
- Swing offers a wide
variety of new components, such as tables, trees,
sliders, progress bars, internal frames, and text components.
- Swing components can
have ToolTips placed over them. A ToolTip is a
textual popup that momentarily appears when the mouse cursor rests inside
the component's painting region. ToolTips offer additional information.
- Keyboard events can be
bound to components, defining how they will react
to various keystrokes.
- There is additional
debugging support for rendering your own lightweight
Swing components.
Swing comes with a default look-and-feel (L&Fs) called "Metal," which combines
some of the best graphical elements of today's major L&Fs. The Java programming language makes
it easy to implement other L&Fs like Windows or Motif, but Metal will be
perfect for this application.
The code segment below is from the primary class called DukeBakery. Here
you see the creation of the basic GUI components.
public class DukeBakery extends JFrame {
private DataPanel screenvar;
private JTextArea msgout;
private Connection dbconn;
public DukeBakery() {
super( "DUKE'S BAKERY" );
// Set up GUI environment
Container p = getContentPane();
screenvar = new DataPanel();
msgout = new JTextArea( 8, 40 );
p.setLayout( new FlowLayout() );
p.add( new JScrollPane(
screenvar ) );
p.add( new JScrollPane(msgout) );
|
The call to super( "DUKE'S BAKERY" ); refers to the superclass
JFrame's constructor, and places the string "DUKE'S BAKERY" in
the window's
title bar. Then we obtain a Container object p, which establishes the
content pane, a place to attach components for viewing. Next a DataPanel
object is instantiated. That's a class I've defined, and we'll look at the
code in the next section. Also, a JtextArea with 8-character rows and a
40-character column width is instantiated. The statement p.setLayout( new
FlowLayout() ); defines the content pane's layout scheme. There are many
layout schemes, such as BoxLayout, BorderLayout, GridLayout, and so on. They
each have particular strengths depending on the mix of components and design
considerations. FlowLayout arranges components from upper left to right and
proceeds down the pane. In the final two statements of this segment,
p.add( new JScrollPane(
screenvar ) );
p.add( new JScrollPane(
msgout ) );
|
we use the Container method add to put the
screenvar and msgout objects
on the content pane. Both objects are housed in a JScrollPane
object,
which automatically enables both vertical and horizontal scrolling if the
output exceeds the defined bounds. In the case of msgout, this is important,
because it is used as a message window, and will receive status information
during the course of taking orders in the bakery. Kate can check this area
to verify that transactions are unfolding as expected.
Now let's take a look at the DataPanel class.
class DataPanel extends JPanel {
JTextField id, first, last,
address, home, city, state,
zip,
country, email, fax;
JLabel lfirst, llast, laddress,
lhome, lcity, lstate, lzip,
lcountry, lemail, lfax;
public DataPanel() {
// Label panel
JPanel labelPanel =
new JPanel();
labelPanel.setLayout(
new GridLayout( 10, 1 ) );
lfirst = new JLabel(
"First Name:", 0 );
labelPanel.add( lfirst);
llast = new JLabel(
"Last Name:", 0 );
labelPanel.add( llast);
lhome = new JLabel(
"Phone:", 0 );
labelPanel.add( lhome);
laddress = new JLabel(
"Address:", 0 );
labelPanel.add(
laddress);
lcity = new JLabel(
"City:", 0 );
labelPanel.add( lcity);
lstate = new JLabel(
"State:", 0 );
labelPanel.add( lstate);
lzip = new JLabel(
"Zip Code:", 0 );
labelPanel.add(
lzip);
lcountry = new JLabel(
"Country:", 0 );
labelPanel.add( lcountry);
lemail = new JLabel(
"Email:", 0 );
labelPanel.add( lemail);
lfax = new JLabel(
"Fax Number:", 0 );
labelPanel.add( lfax);
// TextField panel
JPanel screenvarPanel =
new JPanel();
screenvarPanel.setLayout(
new GridLayout( 10, 1 ) );
id = new JTextField( 20) ;
first = new JTextField( 20 );
screenvarPanel.add( first );
last = new JTextField( 20 );
screenvarPanel.add( last );
home = new JTextField(
"Enter
number-click Find", 20);
screenvarPanel.add( home );
address =
new JTextField( 20 );
screenvarPanel.add( address );
city = new JTextField( 20 );
screenvarPanel.add( city );
state = new JTextField( 20 );
screenvarPanel.add( state );
zip = new JTextField( 20 );
screenvarPanel.add( zip );
country = new JTextField( 20 );
screenvarPanel.add( country );
email = new JTextField( 20 );
screenvarPanel.add( email );
fax = new JTextField( 20 );
screenvarPanel.add( fax );
// Accessibility Section -
//relate labels and text
// fields for use by assistive
//technologies
lfirst.setLabelFor( first );
llast.setLabelFor( last );
lhome.setLabelFor( home );
laddress.setLabelFor( address );
lcity.setLabelFor( city );
lstate.setLabelFor( state );
lzip.setLabelFor( zip );
lcountry.setLabelFor( country );
lemail.setLabelFor( email );
lfax.setLabelFor( fax );
setLayout( new GridLayout( 1, 2 ) );
add( labelPanel );
add( screenvarPanel );
}
}
|
This class has a large number of statements, but it's really very simple.
JLabels are instantiated for each of the data input labels,
such as "First
Name:", "Last Name:", "Phone:, and so on. The "0"
second argument centers the text field. Then the objects are added to
JPanel
object labelPanel. The
labelPanel.setLayout(new GridLayout( 10, 1 ) ); statement arrays
the centered labels down the panel in 10 rows and 1 column.
In the second section of the class, another JPanel object called
screenvarPanel is defined. After the statement
screenvarPanel.setLayout(
new GridLayout( 10, 1 ) );,
10 JTextField
|
objects are defined corresponding to the previous 10 JLabel objects.
They are added to the screenvarPanel object in 10 rows and 1 column, for
data input and display.
In the third section, setLabelFor methods are called to relate the
JLabel objects to the JTextField objects. This method allows assistive
technologies, like audio output screen readers for the blind, to interpret
the relationships between GUI components.
At the end of the class, the statement
setLayout( new GridLayout( 1, 2 ) );
establishes a new grid layout with 1 row and 2 columns. This corresponds to
the two panels containing the 10 rows of labels and 10 rows of text fields.
The panels are treated as separate entities, and are added to the
DataPanel
object that extends JPanel with the following statements:
add( labelPanel );
add( screenvarPanel );
|
When the DataPanel object is instantiated, it is put inside
a JScrollPane
object and added to the Container c.
// Set up database connection
try {
String url = "jdbc:odbc:BakeryBook";
Class.forName( "
sun.jdbc.odbc.JdbcOdbcDriver" );
dbconn =
DriverManager.getConnection( url );
msgout.append(
"Connection successful\n" );
}
catch (
ClassNotFoundException cnfex ) {
// process ClassNotFoundExceptions
//here
cnfex.printStackTrace();
msgout.append( "
Connection unsuccessful\n" +
cnfex.toString() );
}
catch ( SQLException sqlex ) {
// process SQLExceptions here
sqlex.printStackTrace();
msgout.append( "
Connection unsuccessful\n" +
sqlex.toString() );
}
catch ( Exception excp ) {
// process remaining Exceptions
//here
excp.printStackTrace();
msgout.append( excp.toString() );
}
|
The string url is defined with the name of database,
BakeryBook.
Then the
JDBC-ODBC Bridge driver is loaded with the following line of code:
Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver" );
|
Next we connect the JDBC-ODBC driver to the BakeryBook database
with the following statement:
dbconn =
DriverManager.getConnection( url );
|
This method call produces the Connection object dbconn, which will be used
throughout the application to access the database. If we had defined a user
login name and password other than "anonymous" and "guest"
as established
during the Microsoft Access ODBC Data Source registration process described
earlier, we would enter those as the second and third arguments to the
getConnection method of DriverManager. The call to
msgout.append( "Connection successful\n" ); writes a success message
to the JTextArea object msgout.
The remainder of the code catches various exceptions, such as the
ClassNotFoundException, which indicates that the
statement Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
failed to establish the JDBC-ODBC driver, indicating a system
configuration problem.
The remaining catch statements process SQLExceptions and general
Exceptions. It is possible to extract even more information from
these catch
constructs, consult the
"JDBC API Tutorial and Reference 2nd Edition" by White,
Fisher, et al. for more information.
The DukeBakery class is wrapped up by completing the GUI.
The following statements take care of this.
// Complete GUI
ButtonPanel controls =
new ButtonPanel(
dbconn, screenvar, msgout);
p.add( controls );
RadioButtons rb =
new RadioButtons(
dbconn, screenvar, msgout );
p.add ( rb );
setSize( 500, 475 );
show();
|
This code instantiates the ButtonPanel, which is a bank of buttons
to command Find, Add, Update and Clear operations.
Then the ButtonPanel
object is added to the content pane p. Next the
RadioButtons object is
instantiated and added to the content pane. The size of the window is
established by the setSize method with x and
y values in pixels. Finally
the show() method puts it all up on the screen.
Let's take a quick look at the code making up the ButtonPanel
and RadioButtons classes.
class ButtonPanel extends JPanel {
public ButtonPanel(
Connection dbc, DataPanel scv,
JTextArea msg ) {
setLayout(
new GridLayout( 1, 4 ) );
JButton findName =
new JButton( "Find" );
findName.addActionListener
(new FindRecord(
dbc, scv, msg));
add( findName );
JButton addName =
new JButton( "Add" );
addName.addActionListener
(new AddRecord(
dbc, scv, msg ) );
add( addName );
JButton updateName =
new JButton(
"Update" );
updateName.addActionListener(
new UpdateRecord(
dbc, scv, msg ) );
add( updateName );
JButton clear =
new JButton( "Clear" );
clear.addActionListener(
new Clearscreenvar(
scv ) );
add( clear );
}
}
class RadioButtons extends JPanel {
public RadioButtons(
Connection dbc, DataPanel scv,
JTextArea msg ) {
JRadioButton place =
new JRadioButton(
"Place Order",false );
DukeOrder dkord =
new DukeOrder( dbc, scv, msg );
place.addActionListener(
dkord );
add( place );
OrderHist ohist =
new OrderHist(
dbc, scv, msg );
JRadioButton review =
new JRadioButton(
"Order History", false );
review.addActionListener( ohist );
add( review );
TotalHist tothist =
new TotalHist(
dbc, scv, msg );
JRadioButton stats =
new JRadioButton(
"Total Orders", false );
stats.addActionListener(
tothist );
add( stats );
ButtonGroup radioGrp =
new ButtonGroup();
radioGrp.add( place );
radioGrp.add( review );
radioGrp.add( stats );
}
}
|
The Connection (database), DataPanel
(screen variables) and JtextArea
(message window) objects are passed to the class constructors, so they
can pass the objects on for processing when the buttons are clicked.
In ButtonPanel, JButton objects are instantiated for each of the
four
buttons (Find, Add, Update, Clear). An action listener method is
fired
to establish processing when a button is clicked. We'll take a look at
the code for processing a button push event in the next section.
The RadioButtons class is very similar to the
ButtonPanel class. The only
real difference being the use of JRadioButton rather than
JButton. In
addition, I used the ButtonGroup class to establish that the three buttons
act in a mutually exclusive way. Specifically, if one button is pushed, the
other buttons are de-activated.
Because these classes both extend JPanel, the add(...) method calls
go to the
JPanel, which is then added to the content
pane p in DukeBakery.
Now it's time to see what the main window looks like when we bring up the
application. I've done a Find, so we'll have some data in
the JTextFields.
I typed (111) 111-1111 into the Phone field, to Find the above predefined
test data. Non-numeric characters are automatically stripped out of the
phone field, to maintain data integrity.
Let's take a look at the FindRecord class, which implements
interface ActionListener.
class FindRecord
implements ActionListener {
private DataPanel screenvar;
private JTextArea msgout;
private Connection dbconn;
public FindRecord(
Connection dbc, DataPanel scv,
JTextArea msg ) {
dbconn = dbc;
screenvar = scv;
msgout = msg;
}
public void actionPerformed(
ActionEvent e ) {
try {
// strip out non-numerics
//from home phone
String numstrg = new String();
for (int i = 0;
i < screenvar.home.gettext().length(
); i++){
if ( screenvar.home.gettext(
).charat(i)>='0' &&
screenvar.home.getText(
).charAt(i)<='9') {
numstrg +=
screenvar.home.gettext(
).substring(i,i+1);
}
}
if ( !numstrg.equals( "" ) ) {
statement statement =
dbconn.createstatement();
string query = "SELECT *
FROM addresses " +
"WHERE homephone = '" +
numstrg + "'";
msgout.append(
"\nSending query: " +
dbconn.nativesql(
query ) + "\n" );
resultset rs =
statement.executequery(
query );
display( rs );
statement.close();
}
else
screenvar.home.settext(
"Enter home phone
then press Find" );
}
catch ( sqlexception sqlex ) {
msgout.append( sqlex.tostring()
+sqlex.getmessage() );
}
}
// display results of query
public void display(
resultset rs ) {
try {
rs.next();
int recordnumber = rs.getint( 1 );
if ( recordnumber != 0 ) {
screenvar.id.settext(
string.valueof(recordnumber) );
screenvar.first.settext(
rs.getstring( 2 ) );
screenvar.last.settext(
rs.getstring( 3 ) );
screenvar.address.settext(
rs.getstring( 4 ) );
screenvar.city.settext(
rs.getstring( 5 ) );
screenvar.state.settext(
rs.getstring( 6 ) );
screenvar.zip.settext(
rs.getstring( 7 ) );
screenvar.country.settext(
rs.getstring( 8 ) );
screenvar.email.settext(
rs.getstring( 9 ) );
screenvar.home.settext(
rs.getstring( 10 ) );
screenvar.fax.settext(
rs.getstring( 11 ) );
}
else
msgout.append(
"\nNo record found\n" );
catch ( sqlexception sqlex ) {
msgout.append(
"\n*** Phone Number" +
"Not In Database ***\n" );
}
}
}
|
When the Find button is clicked on the GUI, program control jumps to the
actionPerformed method. The first operation is the stripping out
of non-numeric data from the phone number string. Let's take a look at the
code.
String numstrg = new String();
for (int i = 0;
i < screenvar.home.gettext(
).length(); i++){
if (
screenvar.home.gettext(
).charat(i)>='0' &&
screenvar.home.getText(
).charAt(i)<='9') {
numstrg +=
screenvar.home.gettext(
).substring(i,i+1);
}
}
|
The key method is charAt, which is used to screen out anything except
characters in the range 0 to 9. Then a text string called numstring is
iteratively built by adding single characters in succession using the
substring method. Notice that the substring method requires the second
argument to be i+1, to pick up the character at the i position.
Now let's take a look at some SQL code.
Statement statement =
dbconn.createStatement();
String query = "
SELECT * FROM addresses " +
"WHERE homephone = '" +
numstrg + "'";
msgout.append(
"\nSending query: " +
dbconn.nativeSQL(
query ) + "\n" );
ResultSet rs =
statement.executeQuery(
query );
display( rs );
statement.close();
|
A text string called query is built containing the SQL query,
which in our
example has the form
SELECT * FROM addresses
WHERE homephone = '1111111111'
|
The * means all columns from the table "addresses" are selected
where the
homephone field equals the indicated single-quoted string. Remember our
database called BakeryBook contains two tables,
addresses and orders.
After the query is built, it is executed by the executeQuery method,
resulting in the ResultSet object rs. Now let's look at how rs is used in
my display method.
public void display( ResultSet rs ) {
try {
rs.next();
int recordNumber = rs.getInt( 1 );
if ( recordNumber != 0 ) {
screenvar.id.setText
(String.valueOf(
recordNumber) );
screenvar.first.setText(
rs.getString( 2 ) );
screenvar.last.setText(
rs.getString( 3 ) );
screenvar.address.setText(
rs.getString( 4 ) );
screenvar.city.setText(
rs.getString( 5 ) );
screenvar.state.setText(
rs.getString( 6 ) );
screenvar.zip.setText(
rs.getString( 7 ) );
screenvar.country.setText(
rs.getString( 8 ) );
screenvar.email.setText(
rs.getString( 9 ) );
screenvar.home.setText(
rs.getString( 10 ) );
screenvar.fax.setText(
rs.getString( 11 ) );
}
else
msgout.append(
"\nNo record found\n" );
}
catch ( SQLException sqlex ) {
msgout.append
( "\n*** Phone Number" +
"Not In Database ***\n" );
}
}
|
The ResultSet next() method is very important to processing
table data.
After the query is executed, the cursor is pointing above the first row
in the resulting data collection. In this case, since phone numbers are
guaranteed to be unique (as specified during database definition), we will
only have one resulting record. After testing that the record ID is not 0,
we extract the data from the resulting row with the statements of the form
screenvar.first.setText( rs.getString( 2 ) ); This extracts the
2nd column
from the database, which is the first name field, and deposits it in the
JTextField first defined in DataPanel when we instantiated it as screenvar.
In this way, all the database columns are extracted and placed in their
corresponding JTextFields.
The other major top-level classes, AddRecord and UpdateRecord, are very
similar in structure to FindRecord. The major differences are their SQL
command strings. Let's look at the string for the AddRecord class.
String query = "INSERT INTO addresses (" +
"firstname,
lastname, address, city, " +
"state, postalcode, country, " +
"emailaddress, homephone, faxnumber" +
") VALUES ('" +
screenvar.first.getText() + "', '" +
screenvar.last.getText() + "', '" +
screenvar.address.getText() + "', '" +
screenvar.city.getText() + "', '" +
screenvar.state.getText() + "', '" +
screenvar.zip.getText() + "', '" +
screenvar.country.getText() + "', '" +
screenvar.email.getText() + "', '" +
numstrg + "', '" +
screenvar.fax.getText() + "')";
|
This SQL command string lists the database column names, such as firstname,
lastname, corresponding to the column names defined in
the Microsoft Access addresses table. These are not necessarily the same names defined
in the Java class DataPanel, as you can see by the
various getText() calls
that follow. The single quoted values are text strings that are pulled
from the user supplied data entry fields. They are resolved as text strings
by the getText() method calls.
Now take a look at the SQL string from the class UpdateRecord.
String query =
"UPDATE addresses SET " +
"firstname='" +
screenvar.first.getText() +
"', lastname='" +
screenvar.last.getText() +
"', address='" +
screenvar.address.getText() +
"', city='" + screenvar.city.getText() +
"', state='" +
screenvar.state.getText() +
"', postalcode='" +
screenvar.zip.getText() +
"', country='" +
screenvar.country.getText() +
"', emailaddress='" +
screenvar.email.getText() +
"', homephone='" + numstrg +
"', faxnumber='" +
screenvar.fax.getText() +
' WHERE id=" + screenvar.id.getText();
|
This syntax equates addresses table names with values entered from the GUI,
and keys the transaction to the addresses table's id field which is the
previously read into the screenvar.id data item during a
Find operation.
Place Order
At this point, we can click the Place Order radio button at the bottom of the main window.
The following new window pops up.
Simply enter the desired number of each item, and click Order. The
interface is designed so that you must enter a valid numeric for each field,
even if it is 0. A blank will not be accepted. Non-numeric entries are not
accepted, and present an error message in the data input field.
The class below is called by the Order button click from a simple class
called DukeOrder (see complete code listing at end of article).
The obox
object is a JPanel that establishes the JTextFields
for data entry.
class PlaceOrder
implements ActionListener {
private DataPanel screenvar;
private JTextArea msgout;
private Connection dbconn;
private OrderBox obox;
public PlaceOrder(
Connection dbc, DataPanel scv,
JTextArea msg, OrderBox ob ) {
dbconn = dbc;
screenvar = scv;
msgout = msg;
obox = ob;
}
public void actionPerformed(
ActionEvent e ) {
boolean inputerror = false;
int iwheat=0; int icake=0;
int inaan=0; int ibagel=0;
try {
Statement statement =
dbconn.createStatement();
try {
iwheat=Integer.parseInt(
obox.inwheat.getText());
}
catch(
NumberFormatException nfe ) {
inputerror = true;
obox.inwheat.setText
("Error--enter integer value");
}
try {
icake=Integer.parseInt(
obox.incake.getText() );
}
catch(
NumberFormatException nfe ) {
inputerror = true;
obox.incake.setText
("
Error--enter integer value");
}
try {
inaan=Integer.parseInt(
obox.innaan.getText() );
}
catch(
NumberFormatException nfe ) {
inputerror = true;
obox.innaan.setText
("Error--enter integer value");
}
try {
ibagel=Integer.parseInt(
obox.inbagel.getText());
}
catch(
NumberFormatException nfe ) {
inputerror = true;
obox.inbagel.setText
("Error --
enter integer value");
}
java.util.Date date =
new java.util.Date();
SimpleDateFormat fmt =
new SimpleDateFormat (
"yyyy.MM.dd-HH:mm z");
String dtstr = fmt.format(date);
if( !screenvar.id.getText(
).equals("") ) {
if ( !inputerror &&
(iwheat != 0 ||
icake != 0 ||
inaan != 0 ||
ibagel != 0) ) {
String query =
"INSERT INTO orders " +
"(LinkAddrTbl,
OrderDate,wheat,cake,naan,bagel)" +
"VALUES ("+
screenvar.id.getText() + "," +
"'" + dtstr + "'," +
String.valueOf(iwheat) + "," +
String.valueOf(icake) + "," +
String.valueOf(inaan) + "," +
String.valueOf(ibagel) + ")";
msgout.append(
"\nSending query: " +
bconn.nativeSQL( query )
+ "\n" );
int result =
statement.executeUpdate(
query );
if ( result == 1 )
msgout.append(
"\nOrder Placed\n" );
else {
msgout.append(
"\nInsertion failed\n" );
screenvar.first.setText( "" );
screenvar.last.setText( "" );
}
}
else
msgout.append
( "\nEnter at least one numeric value " +
", then press Order\n" );
}
else
msgout.append(
"\n *** Find data before
issuing order ***\n");
statement.close();
}
catch ( SQLException sqlex ) {
msgout.append(
sqlex.toString() );
}
// clear out order input boxes
//after database write
if (!inputerror) {
obox.inwheat.setText("");
obox.incake.setText("");
obox.innaan.setText("");
obox.inbagel.setText("");
}
}
}
|
The inputs are screened as valid integers by code of the following type:
try {
iwheat=Integer.parseInt(
obox.inwheat.getText(
));
}
catch(
NumberFormatException nfe ) {
inputerror = true;
obox.inwheat.setText
(
"Error--enter integer value");
}
|
The order input is parsed by the Integer method parseInt. If it creates an
error, the catch block intercepts it, and puts an error message in the input
data field, asking the user to try again.
Before the data is written to the secondary Orders table, the system is
interrogated for the date and time by the following code:
java.util.Date date =
new java.util.Date();
SimpleDateFormat fmt =
new SimpleDateFormat (
"yyyy.MM.dd-HH:mm z");
String dtstr = fmt.format(date);
|
This date format generates a date and time string of the form
"1999.11.20-23:22 PST." The Java programming language can also produce a much more
verbose form of
date and time, but this compact form is what I wanted for inclusion as a
database entry, and for table output.
The following logic,
if ( !inputerror &&
(iwheat != 0 ||
icake != 0 ||
inaan != 0 ||
ibagel != 0) ) {
|
specifies that if there are no data input errors, and the data is not all
0, then proceed with the SQL Insert operation. The SQL details are very
much like the AddRecord operation that we've previously analyzed. There
is additional logic to prevent issuance of an order without first doing a
find on a valid master record from the Addresses table.
Order History
If Kate had a repeat customer on the line, she would enter the phone number
and click Find to bring up an addresses record. Then she
would click Place Order to enter the number of items. After that, she might
want to take a look at the customer's order history. To do that, she would click the
Order History radio button. A window would pop up that looks like this.
This table was generated using the JTable class. Let's check out the
code.
class OrderHist
extends JFrame implements
ActionListener {
private Connection dbconn;
private DataPanel screenvar;
private JTextArea msgout;
private boolean firsttime = true;
private Container c;
private JScrollPane jspane;
private QueryTableModel qtbl;
private JTable jtbl;
public OrderHist(
Connection dbc,
DataPanel scv,
JTextArea msg ) {
super(
"DUKE'S BAKERY --
ORDER HISTORY" );
dbconn = dbc;
screenvar = scv;
msgout = msg;
}
public void actionPerformed(
ctionEvent e) {
if (
!screenvar.id.getText().equals("") ) {
// Set up Table GUI
if ( firsttime ) {
c = getContentPane();
c.setLayout( new FlowLayout() );
qtbl = new
QueryTableModel(
dbconn, screenvar, msgout );
qtbl.query();
jtbl = new JTable( qtbl );
TableColumn tcol =
jtbl.getColumnModel(
).getColumn(0);
tcol.setPreferredWidth(125);
jspane = new JScrollPane( jtbl );
c.add( jspane );
setSize( 500, 500 );
firsttime = false;
}
else {
qtbl.query();
qtbl.fire();
TableColumn tcol =
jtbl.getColumnModel(
).getColumn(0);
tcol.setPreferredWidth(125);
}
show();
}
else
msgout.append(
"\n ***Find data before " +
"generating Order History***\n");
}
}
class QueryTableModel extends
AbstractTableModel {
Connection dbconn;
DataPanel screenvar;
JTextArea msgout;
Vector totalrows;
String[] colheads =
{"Date & Time", "Wheat Loaf",
"Carrot Cake", "Naan Bread",
"Bagel"};
public QueryTableModel(Connection dbc,
DataPanel scv, JTextArea msg ){
dbconn = dbc;
screenvar = scv;
msgout = msg;
totalrows = new Vector();
}
public String getColumnName(int i)
{ return colheads[i]; }
public int getColumnCount(
) { return 5; }
public int getRowCount() {
return totalrows.size(
); }
public Object getValueAt(
int row, int col) {
return ((
String[
])totalrows.elementAt(
row))[col];
}
public boolean isCellEditable(
int row, int col) {
return false;
}
public void fire() {
fireTableChanged(null);
}
public void query() {
try {
Statement statement =
dbconn.createStatement();
String query =
"SELECT * FROM Orders " +
"WHERE LinkAddrTbl =" +
screenvar.id.getText() +
" ORDER BY OrderDate";
msgout.append(
"\nSending query: " +
dbconn.nativeSQL(
query ) + "\n" );
ResultSet rs =
statement.executeQuery(
query );
totalrows = new Vector();
while ( rs.next() ) {
String[
] record =
new String[5];
for( int i = 0; i <
5; i++ ) {
record[i] =
rs.getstring(
i + 3 );
}
totalrows.addelement(
record );
}
msgout.append(
"\nQuery successful\n" );
statement.close();
}
catch ( sqlexception sqlex ) {
msgout.append(
sqlex.tostring() );
}
}
}
|
There are two particularly powerful tools at work in these classes. The
first is the use of JTable for data output, and second is the use of
AbstractTableModel to describe and update the data contained
in the JTable.
AbstractTableModel
This class implements many of the methods in the TableModel interface.
The crucial methods for describing the data in your table must be implemented
by the programmer. They are:
* public int getRowCount();
* public int getColumnCount();
* public Object getValueAt(
int row, int col);
|
Additional methods are implemented to describe column headers, and decide
whether or not the table cells are editable. In this program, I do the
database access from inside QueryTableModel, which
extends AbstractTableModel. The description methods are as follows:
public String getColumnName(int i)
{ return colheads[i]; }
public int getColumnCount() {
return 5; }
public int getRowCount() {
return totalrows.size(); }
public Object getValueAt(
int row, int col) {
return ((String[
])totalrows.elementAt(row))[col];
}
public boolean isCellEditable(
int row, int col) {
return false;
}
public void fire() {
fireTableChanged(null);
}
|
The getColumnName method extracts the column heading strings
from the String
array colheads.
The column count, extracted via getColumnCount, is set
explicitly to 5
because I don't want to display every column in the table. If I did, I could
use the JDBC meta data method getColumnCount to extract an
integer that would
be returned in the programmer-defined getColumnCount method.
The Vector totalrows contains String arrays of
column data obtained from the
SQL database query. The individual rows consist of a String array, with
elements [0] through [4] corresponding to the 5 columns of data. Then these
String arrays, which will represent one row of the
JTable output, are added
to the Vector totalrows. The size of totalrows,
returned by the getRowCount
method, reflects the number of rows that will be output to the JTable.
The getValueAt method allows QueryTableModel to
generate the individual
elements or "cells" for the JTable. The
totalrows.elementAt(row) method
extracts a particular row from the Vector totalrows, which is
then cast as
a string array, allowing for a particular column in that row to be extracted
by the [col] index.
The boolean method isCellEditable, returns the value false to all
inquiries,
ensuring that the table cannot be changed by user interaction, which is
what we want for a query table display.
These methods, in conjunction with the JTable class, allow the
table to be
painted up and also allow for automatic updating of the table after the fire
method is executed. The fire method is executed after the table changes as a
result of a database query.
The SQL code is contained inside the class QueryTableModel's query method.
This query string contains something new. Let's examine the syntax.
String query = "
SELECT * FROM Orders " +
"WHERE LinkAddrTbl =" +
screenvar.id.getText() +
" ORDER BY OrderDate";
|
It's a straightforward SELECT command, asking for all table columns with
the "*", but now we're sorting the resulting records by the
OrderDate field,
which is constructed with year first, then month, then day, then 24 hour time.
The ORDER BY syntax assures that output data ascends by date
on the display panel.
Let's take a look at the while loop that loads the Vector totalrows with data.
totalrows = new Vector();
while ( rs.next() ) {
String[] record = new String[5];
for( int i = 0; i < 5; i++ ) {
record[i] = rs.getstring(
i + 3 );
}
totalrows.addelement( record );
}
|
Column data from the database is loaded into the String array
record from
rs.getString locations 3 through 7. Notice that database column
numbering
system begins with 1, in contrast to the 0-based Java programming standard.
After a
complete row of data is loaded into record, the String array is then added
to the Vector totalrows using the addElement method.
Now we'll see how a QueryTableModel object is used to build
the JTable:
if ( firsttime ) {
c = getContentPane();
c.setLayout( new FlowLayout() );
qtbl = new
QueryTableModel(
dbconn, screenvar, msgout );
qtbl.query();
jtbl = new JTable( qtbl );
TableColumn tcol =
jtbl.getColumnModel(
).getColumn(0);
tcol.setPreferredWidth(125);
jspane = new JScrollPane(
jtbl );
c.add( jspane );
setSize( 500, 500 );
firsttime = false;
}
|
The QueryTableModel is instantiated, sending it the objects for database
connect, screen variables, and message output. The QueryTableModel method
query is then executed, filling the Vector totalrows with data.
The
QueryTableModel object is used as an argument during instantiation of a
JTable object. A TableColumn object is then used in
conjunction with the
JTable object to set the first column to a width of 125 pixels.
Afterward,
the JTable object is wrapped in a JScrollPane and added to the content pane.
On subsequent passes through this code, only the query, and fire methods need
to be executed. The JTable is automatically updated with new data upon
execution of the fire method, as seen in the following code.
else {
qtbl.query();
qtbl.fire();
TableColumn tcol =
jtbl.getColumnModel(
).getColumn(0);
tcol.setPreferredWidth(125);
}
show();
|
The first column is re-established as being 125 pixels wide, which
automatically shrinks the other columns to accommodate the increase in width.
Notice that there is a Boolean switch controlling a code block for first-time
execution and subsequent passes. This occurs whenever program windows are
brought up via the radio buttons. In the case of the master DukeBakery
window, the GUI stays on screen whenever the application is running, and
when the window is closed, the whole application terminates. The radio button
controlled windows are meant to be closed when their operation is finished,
and the application continues to run. When a radio button is clicked again,
the window reappears.
When components are added to a content pane, they persist even after the
window is closed. In general, when the window is brought up again, it is
only necessary to execute the show() method. In this class, we
have some
extra work to do regarding the query() and fire()
methods.
Total Orders
If Kate decides she wants to generate a Total Orders report for each item
across all customers in the Orders table, she can click the third radio
button, which pops up a small window containing the following information.
And here is the class that generates this data:
class TotalHist extends JFrame
implements ActionListener {
private Connection dbconn;
private DataPanel screenvar;
private JScrollPane orderpane;
private JTextArea msgout;
private JTextField outwheat,
outcake, outnaan, outbagel;
private boolean firsttime = true;
public TotalHist(
Connection dbc, DataPanel scv,
JTextArea msg ) {
super(
"DUKE'S BAKERY --
TOTAL ORDERS" );
dbconn = dbc;
screenvar = scv;
msgout = msg;
}
public void actionPerformed(
ActionEvent e) {
// Set up GUI environment
if ( firsttime ) {
Container c = getContentPane();
c.setLayout(new GridLayout(4,2) );
JLabel pwheat = new JLabel(
"Total number of
Wheat Loaves ordered:" );
c.add( pwheat );
outwheat = new JTextField( 10 );
c.add( outwheat );
pwheat.setLabelFor( outwheat );
JLabel pcake = new JLabel(
"Total number of Carrot
Cakes ordered:");
c.add( pcake);
outcake = new JTextField( 10 );
c.add( outcake );
pcake.setLabelFor( outcake );
JLabel pnaan = new JLabel(
"Total number of Naan
Bread ordered:" );
c.add( pnaan );
outnaan = new JTextField( 10 );
c.add( outnaan );
pnaan.setLabelFor( outnaan );
JLabel pbagel = new JLabel(
"Total number of
Bagels ordered:" );
c.add( pbagel );
outbagel = new JTextField( 10 );
c.add( outbagel );
pbagel.setLabelFor( outbagel );
setSize( 550, 130 );
firsttime = false;
}
show();
try {
Statement statement =
dbconn.createStatement();
String query =
"SELECT * FROM Orders";
msgout.append(
"\nSending query: " +
dbconn.nativeSQL(
query ) + "\n" );
ResultSet rs =
statement.executeQuery( query );
display( rs );
msgout.append(
"\nQuery successful\n" );
statement.close();
}
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
}
}
// Display results of query
public void display( ResultSet rs ) {
int accwheat=0; int acccake=0;
int accnaan=0; int accbagel=0;
try {
while ( rs.next() ) {
// compute totals date,
//wheat,carrot,
// naan,bagel from database
accwheat += rs.getInt(4);
acccake += rs.getInt(5);
accnaan += rs.getInt(6);
accbagel += rs.getInt(7);
}
}
catch ( SQLException sqlex ) {
msgout.append(
sqlex.toString() );
}
outwheat.setText(
String.valueOf(accwheat));
outcake.setText(
String.valueOf(acccake));
outnaan.setText(
String.valueOf(accnaan));
outbagel.setText(
String.valueOf(accbagel));
}
}
|
The GUI is constructed using the GridLayout manager. The
statement
c.setLayout(new GridLayout(4,2) );
establishes that the components will be arranged on the JFrame in 4 rows
and 2 columns. The left-hand column consists of JLabel items, and the
right-hand column handles the corresponding JTextField items where the
totals data are listed.
The important processing loop is,
while ( rs.next() ) {
// compute totals date,wheat,carrot,
// naan,bagel from database
accwheat += rs.getInt(4);
acccake += rs.getInt(5);
accnaan += rs.getInt(6);
accbagel += rs.getInt(7);
}
|
which runs through the ResultSet rs, which includes every record in the
Orders table. The data values are added to integer accumulators, then
those values are inserted into the JTextFields with statements of
the form
outbagel.setText(String.valueOf(accbagel));
Conclusion
Kate Cookie will have a chance to take the application out for a spin to see
how she likes the user interface and the basic database design. She has some
customer data to input, so when a call comes in, she can either create a new
record, or bring up the data by using the phone number as the unique key field.
And on return calls, she can immediately take a look at that customer's order
history just by clicking a radio button.
I'm already working on some new capabilities for her app. She'll need a
query by last name, which may not be unique and must have the ability to
scan multiple records. She will also need to be able to delete records,
and if a master record is deleted, all the order data needs to be deleted
as well. In addition, she will probably need a status field on each order
record indicating whether the order is pending or shipped.
In the next iteration of the application, which will be covered in Duke's
Bakery Part 2, I will make use of more advanced SQL concepts, like Join.
And the efficiency of execution will be optimized through the use of
SwingWorker, which implements a background thread to perform time-consuming
operations behind the scenes. Kate and I might also include some digitized
photos of her products. We've got a lot of APIs to explore on our way to
the ultimate Duke's Bakery application.
Code Listing
DukeBakery.java
Reference Texts
The architecture of this program is inspired by the AddressBook.java
example in "Java-How to Program-3rd Edition," by
Deitel & Deitel, Prentice Hall 1999
Eckstein, Loy & Wood, Java Swing," O'Reilly 1998
Lemay & Cadenhead, "Java 2 in 21 Days," Sams 1999
White, Fisher, Cattell, Hamilton, Hapner, "JDBC API Tutorial and
Reference, 2nd Edition," Addison-Wesley, 1999
About the Author
Michael Meloan, a frequent contributor to the
Java Developer Connection,
began his professional career writing IBM mainframe and DEC PDP-11 assembly languages.
He went on to code in PL/I, APL and C. In addition, his fiction has appeared in WIRED,
BUZZ, Chic, L.A. Weekly, and on National Public Radio.
|
|
Print-friendly Version
