![]() ![]() ![]() |
|
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
Articles
Index
Duke's Bakery - A JDBCTM Order Entry Prototype - Part IBy Michael Meloan (December 1999)
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 JavaTM 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 JDBCTM 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 EnvironmentThe 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
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 BridgeThe 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 classsun.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 Two-Tier and Three-Tier ModelsIn 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 98Kate 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.
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 WalkthroughI developed the prototype GUI using the JavaTM 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:
The code segment below is from the primary class called 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 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 screenvarPanel.setLayout( new GridLayout( 10, 1 ) );, 10 JTextFieldobjects 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,
At the end of the class, the statement
add( labelPanel ); add( screenvarPanel );When the DataPanel object is instantiated, it is put inside
a JScrollPane
object and added to the Container c .
Database Setup// 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
The remaining catch statements process
The // 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 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
The
Because these classes both extend
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
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 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 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, 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 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 OrderAt 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 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 HistoryIf 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 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 .
AbstractTableModelThis class implements many of the methods in theTableModel 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
The
The
The boolean method
These methods, in conjunction with the
The SQL code is contained inside the class 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 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 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 Total OrdersIf 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));
ConclusionKate 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
Code ListingDukeBakery.javaReference TextsThe 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 AuthorMichael Meloan, a frequent contributor to the Java Developer ConnectionSM, 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. Reader FeedbackTell us what you think of this article.
|