Java Technology Home Page
A-Z Index

Java Developer Connection(SM)
Technical Articles

Downloads, APIs, Documentation
Java Developer Connection
Tutorials, Tech Articles, Training
Online Support
Community Discussion
News & Events from Everywhere
Products from Everywhere
How Java Technology is Used Worldwide
Print Button
 
Articles Index

Duke's Bakery - A JDBCTM 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 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 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 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:

  • 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.

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 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

coffeecup

About the Author

Michael 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 Feedback

Tell us what you think of this article.

Duke

Very worth reading Worth reading Not worth reading

If you have other comments or ideas for future articles, please type them here:


Print Button
[ This page was updated: 22-Sep-2000 ]
Products & APIs | Developer Connection | Docs & Training | Online Support
Community Discussion | Industry News | Solutions Marketplace | Case Studies
Glossary | Feedback | A-Z Index
For more information on Java technology
and other software from Sun Microsystems, call:
(800) 786-7638
Outside the U.S. and Canada, dial your country's AT&T Direct Access Number first.
Sun Microsystems, Inc.
Copyright © 1995-2000 Sun Microsystems, Inc.
All Rights Reserved. Terms of Use. Privacy Policy.