Duke's Bakery, Part II - A JDBC Order Entry Prototype - Continued

   
   

Articles Index

Order Entry Prototype - Continued

This article was developed using the Java 2 Platform, Standard Edition 1.3, Forte CE and Microsoft Access on Windows ME. For an overview of Forte CE usage, see Developing the Java 2D Art Applet Using Forte for Java Community Edition. Also see Duke's Bakery - A JDBC Order Entry Prototype - Part I, for general JDBC background and information on configuring Microsoft Access.

In Duke's Bakery-Part I, I created a rapid prototype to show Duke's Bakery owner Kate Cookie how Java and JDBC technologies could be used to build an order entry system. Since then, Kate has taken a Java programming class, and has decided to start writing Java code herself, in addition to running her business. So I agreed to create an architecture she could use as a springboard for future development. I made sure to use ResultSetMetaData to extract column names from the database, so that changes to the database will, in most cases, be reflected automatically in the code. I also built the Order Entry, and Order Display windows so that if products are added, or deleted, those changes will be reflected automatically in the JTable displays.

The software architecture separates the program logic from the Swing GUI generation code, so that logic changes need not significantly impact the GUI, and GUI changes need not ripple through the program logic. This type of separation is recommended by the Model-View-Controller design pattern. Design patterns can be of significant value in creating software that is reusable and maintainable. A good overview of these techniques is offered by James W. Cooper in his Java Design Patterns -A Tutorial. I have chosen to implement the most basic notion of separating program logic and GUI rendering. To do this, I've defined a small Controller class that kicks things off. The constructor and main method are listed below.

public Controller() {
        
  mod = new Model();
  dbm = new DBMaster( mod );
        
}//End Controller constructor
    
public static void main (String args[]) {
               
  new Controller() ;    
        
}//End main method

Model

The Model class contains all program logic, implemented as five inner classes that are used as arguments to the JTable.setModel method calls in various JFrame extended GUI classes, which establish JTable renderings for data display and data entry. Model also contains numerous database processing methods, which interact with the JTable infrastructure.

First, the database Connection object is created, then the five inner classes are instantiated. Here is the code:

/* acquire the database connection object */
 dbc = getConnectionObj
     ( "jdbc:odbc:BakeryBook" , 
     "sun.jdbc.odbc.JdbcOdbcDriver" );
        
 /* acquire the inner class objects */
 cqtm = new CustQueryTableModel ( dbc );
 cdtm = new CustDataTableModel  ( dbc );
 catm = new CustAddTableModel   ( dbc );
 cotm = new CustOrderTableModel ( dbc );
 chtm = new CustHistTableModel  ( dbc );
 cqtm.getDefaultResultsAddresses();
 cqtm.getDefaultResultsOrders();

The database Connection object dbc is passed to each of the inner class constructors. The Connection object is created by a method I wrote called getConnectionObj. Its code is listed below, and reflects standard operating procedure for creating this object, which is discussed in Duke's Bakery Part I.

public Connection 
  getConnectionObj( String url, String driver ) {
  try {
     Class.forName( driver );
     Connection db = 
       DriverManager.getConnection( url );
     connectionSuccess = true;
     return db;
   }
   catch ( ClassNotFoundException cnfex ) {
     /* process ClassNotFoundExceptions here */
     cnfex.printStackTrace();
     return null;
   }
   catch ( SQLException sqlex ) {
     /* process SQLExceptions here */
     sqlex.printStackTrace();
     return null;
   }
   catch ( Exception excp ) {
    /* process remaining Exceptions here */
     excp.printStackTrace();
     return null;
   }//End try-catch          
 
 }//End getConnectionObj method    


The connectionSuccess boolean is initialized false, and is set true only if control does not jump to one of the catch blocks.

Two method calls listed below, are very important to the functioning of this application.

cqtm.getDefaultResultsAddresses();
cqtm.getDefaultResultsOrders();

These calls establish ResultSetMetaData objects for the Addresses and Orders tables. These meta data objects are used throughout the application. Here is the code for getDefaultResultsAddresses, which is analogous to the getDefaultResultsOrders method.

public void getDefaultResultsAddresses() {
  try {
    statementAddresses = dbc.createStatement();
    rsAddresses = statementAddresses.executeQuery
      ("SELECT * FROM Addresses");
    rsAddressesMetaData = 
      rsAddresses.getMetaData();
   }//End try
   catch ( SQLException sqlex ) {
      jTextArea.append( sqlex.toString() ); 
   }//catch
   catch ( Exception excp ) {
      // process remaining Exceptions here
       jTextArea.append( excp.toString() ); 
 }//End try-catch  
}//End getDefaultResultsAddresses method

This method establishes the ResultSetMetaData object rsAddressesMetaData. The first step is to establish the ResultSet object rsAddresses, through a generic all columns SQL * select from the table Addresses. Then the ResultSetMetaData object is extracted using the rsAddresses.getMetaData method call. The rsAddressesMetaData object can be used later to extract column names and other useful information from the Addresses table. An analogous object rsOrdersMetaData is also created. Both are available throughout the application through use of the cqtm.getAddressesMetaData and cqtm.getOrdersMetaData method calls which return a ResultSetMetaData object. If an error is caught by the try-catch logic in getDefaultResultsAddresses, error information is written to the JTextArea object on the DBMaster window.

DBMaster

The Model object is then passed to the DBMaster class, which is the master Swing window, and jumping off point for all other functionalities. With this architecture, each Swing JFrame extended class has access to all program logic with a single Model object passed in as a constructor argument. The Model object is passed on to other JFrame extended classes, as button clicks bring up various other functions. The inner classes are extracted for use, as soon as the argument is retrieved by the GUI class constructors. Let's look at some of the DBMaster code beginning with the constructor.

public DBMaster( Model model ) {
        
  mod    = model;
  cqtm   = mod.getCustQueryTableModel();
  cdtm   = mod.getCustDataTableModel();
  cotm   = mod.getCustOrderTableModel();
  chtm   = mod.getCustHistTableModel();
  catm   = mod.getCustAddTableModel();
  rsMeta = cqtm.getAddressesMetaData();

First the Model object mod is established for the scope of the whole class. Then the mod object is used to extract each of the inner class objects through standard accessor methods. One of the accessors is listed below.

public CustQueryTableModel getCustQueryTableModel() {
  return cqtm;
}

This is done as a standard procedure, even if the object is not used, to support possible modification of functionality. The ResultSetMetaData object rsMeta from the Addresses table is also retrieved, to provide information about the Addresses table to be used later.

The inner class objects are defined in DBMaster as listed below.

private Model.CustQueryTableModel cqtm;
private Model.CustDataTableModel  cdtm;
private Model.CustOrderTableModel cotm;
private Model.CustHistTableModel  chtm;
private Model.CustAddTableModel   catm;

Next, the GUI is rendered by the following code.

SwingUtilities.invokeLater( new Runnable() {
  public void run() { 
                
  initComponents ();
 
  setSize ( 750, 600 );   
  setVisible( true );
  mod.setJTextArea(jTextArea1);
  if (mod.getConnectionSuccess()) 
     jTextArea1.append
     ("Database Connection Successful\n");
  else
     jTextArea1.append
     ("Database Connection Failed\n");
            
  }//End run 
});//End invokeLater anonymous inner class

The SwingUtilities.invokeLater method puts a request to execute a block of code on the event queue, then returns and continues execution. In this case, an anonymous inner class which extends Runnable is created so that the code can be executed inside its run method. This assures that the code is executed on the event-dispatching thread, and assures that the GUI rendering operations are "thread safe."

Firing the initComponents method executes all the setup code generated by Forte CE, using the GridBagLayout. The generally accepted way to use Forte CE is to do GUI design using AbsoluteLayout, then convert to GridBagLayout, for portable code generation. This works fairly well, but sometimes requires tweaking of the many complex attributes of GridBagLayout. Unless you have an in-depth understanding of GridBagLayout, it is generally easier to convert back and forth between GridBagLayout and AbsoluteLayout, manipulating the Swing components until you get the results needed. This process can usually be done fairly quickly.

The getConnectionSuccess method returns a boolean indicating whether or not the database connection has been established. If it has, a message is written to the jTextArea1 object, which is the central messaging center for the application. Various other windows have small message areas, but the JTextArea object jTextArea1 in DBMaster is the primary information repository.

DBMaster

The DBMaster window looks like this.

It offers two options: Customer Info and New Customer. The New Customer function is identical to a function which will occur as part of another window, so we will focus now on Customer Info. Here is the code that takes us to the Customer Info function. Customer Info is handled by the CustQuery object custQuery.

if ( custQuery != null ) {
   /* if CustQuery window is open with data */
   /* displayed, then reestablish it */
   /* with no data, and kill hide CustData */
   /* window, if open */
   custQuery.closeCustDataWindow();
   custQuery.setVisible(false);
   custQuery = new CustQuery( mod );
   cqtm.setQueryString( null );
   cqtm.setColString( getColumnName( 4 ) );
   cqtm.setQueryAll( false );
   cqtm.tableQuery();
   cqtm.fire();
 }//End if 
 else { 
    custQuery = new CustQuery( mod );
 }//End if-else      

If custQuery is not null, then a custQuery window is already active. The first code block shows a call to the custQuery.closeCustDataWindow method, which shuts down various previously invoked windows (if any) that may be cluttering up the user space. Then the existing CustQuery window is made invisible, and the custQuery object is reinstantiated with the Model object mod as a constructor argument. Next, set accessor methods are executed against the CustQueryTableModel object cqtm, which enters query variables for execution of the cqtm.tableQuery method. In this case, the intent is to generate an empty ResultSet object, so that the JTable display will appear empty upon rendering the CustQuery window. We will see the details of this table generation logic in the next section. After the cqtm.tableQuery method is fired, the table data is activated with a call to the cqtm.fire method.

CustQuery

Here is the CustQuery window with a JTable listing initiated by clicking the "Query All" button.

If a query is initiated, via the radio buttons, or clicking on the Query All button, a JTable is produced containing between 0 and a full listing of records, containing a subset of column data taken from all rows of the Addresses table. If the table is non-0, clicking on a row will automatically bring up another window ( CustData), featuring the full set of column values for that customer record, and offering various other processing options.

Here is the CustQuery tableQuery method, which controls the generation of this table data.

public void tableQuery() {
  try {
     if ( queryAll) {
         /* order by last name, first name */
         query = 
         "SELECT * FROM Addresses ORDER BY " +
            rsAddressesMetaData.getColumnName(
            3) + "," +
            rsAddressesMetaData.getColumnName(2);
      }//End if
      else {
         /* order by last name, first name */
         query = "SELECT * FROM Addresses WHERE " +
         colstring +
         " = " + "'" + qstring +
          "'" + " ORDER BY " +
         rsAddressesMetaData.getColumnName(
         3) + "," +
         rsAddressesMetaData.getColumnName(
         2);
      }//End if-else
   
      /* argument list below allows for use of */
      /* ResultSet absolute method */
      statement =
         dbc.createStatement
         (ResultSet.TYPE_SCROLL_SENSITIVE,
          ResultSet.CONCUR_READ_ONLY);
          
      rs = statement.executeQuery( query );
      rsMeta = rs.getMetaData();
      /* extract column names using
      ResultSetMetaData */
      /* last name, first name, primary phone */
      colheads[0] = rsMeta.getColumnName(2);
      colheads[1] = rsMeta.getColumnName(3);
      colheads[2] = rsMeta.getColumnName(4);
      colheads[3] = rsMeta.getColumnName(10);
                         
      jTextArea.append( "Sending query: " +
       query + "\n" );
             totalrows = new Vector();
      while ( rs.next() ) {
        String[] record =
        new String[ rsMeta.getColumnCount() - 1 ];
         
           record[0] = rs.getString( colheads[0] );
           record[1] = rs.getString( colheads[1] );
           record[2] = rs.getString( colheads[2] );
           record[3] = rs.getString( colheads[3] );

           totalrows.addElement( record );
      }//End while loop
      jTextArea.append( "Query successful\n" );

  }//End try
  catch ( SQLException sqlex ) {
    jTextArea.append( sqlex.toString() );
  }
  catch ( Exception excp ) {
    jTextArea.append( excp.toString() );
  }//End try-catch

}//End tableQuery method

If the queryAll boolean is set true, a SQL String variable is assigned to fetch all columns and rows from the Addresses table, ordered, or sorted, by last name, then by first name. The else block handles queries that result from clicking on one of the three radio buttons depicted on the screen below. The text input fields correspond to Last_Name, Primary_Phone, and Company_Name.

The text Strings ( qstring and colstring) retrieved from the radio button click are registered in this inner class ( cqtm) using set methods. The qstring variable is extracted from the one of the three JText Fields. The colstring variable contains the column name, extracted using a ResultSetMetaData getColumnName method call. The SQL string differs from the queryAll example only by the addition of the WHERE syntax which narrows the search to rows which satisfy the stated criteria (specified by the qstring and colstring selection values).

Next, the Statement object is created using the following syntax.

statement = 
   dbc.createStatement
   (ResultSet.TYPE_SCROLL_SENSITIVE,
    ResultSet.CONCUR_READ_ONLY);

The arguments passed to the createStatement method call enable the scrolling ResultSet feature. In this application, I am using the ResultSet method absolute, which allows access to a particular row in a ResultSet, specified by number, rather than having to scroll sequentially through the data to find a given row. This usage will be described later.

Then the Statement object is used to generate a ResultSet object based on the SQL String query, and the rs object is used to create a ResultSetMetaData object rsMeta.

rs = statement.executeQuery( query ); 
rsMeta = rs.getMetaData();

Using the ResultSetMetaData object rsMeta, the following code extracts the column names for annotation of the JTable. The array of String variables contain First_Name, Last_Name, Primary_Phone, and Company_Name. It should be noted again that if the Addresses column names are changed, the application will accept these changes without software modification, because the data is not hard coded.

colheads[0] = rsMeta.getColumnName(2);
colheads[1] = rsMeta.getColumnName(3);
colheads[2] = rsMeta.getColumnName(4);
colheads[3] = rsMeta.getColumnName(10);

The following while loop loads the data structure for JTable generation.

totalrows = new Vector();
while ( rs.next() ) {
  String[] record = 
  new String[ rsMeta.getColumnCount() - 1 ];
              
  record[0] = rs.getString( colheads[0] );
  record[1] = rs.getString( colheads[1] );
  record[2] = rs.getString( colheads[2] );
  record[3] = rs.getString( colheads[3] );
               
  totalrows.addElement( record );
 }//End while loop
 jTextArea.append( "Query successful\n" ); 

The while loop is predicated on the notion that a pointer is initially positioned before the beginning of the first record of a ResultSet. Each invocation of the next method returns a boolean true, if there is a record ready to be read. When the call returns false, the ResultSet has been exhausted. Once inside the while loop, a String array record is instantiated using ResultSetMetaData to indicate the number of columns retrieved, as indicated by the value rsMeta.getColumnCount() - 1. Since Java arrays are zero based, the value is decremented by 1. Then this String array (record) is loaded with the values from the ResultSet (rs) by the getString method calls. The column names contained in the colheads String array are used to extract the desired data from rs by column name. This record is then added to the Vector object totalrows with the addElement method call. So we're building a Vector with String arrays as elements. When the fire method call is executed, the JTable presentation infrastructure will automatically render the table data.

I've mentioned that ResultSetMetaData is used throughout the application. The CustQuery class uses this data to annotate certain data fields on the GUI. Using the Forte CE Component Inspector, the field names are generated from the execution of a method. The facility for setting this up is depicted below. The text button, under the Properties tab, allows user code to be entered for generation of the on-screen text. The method I have entered for this purpose is getColumnName.

The getColumnName method is contained in the CustQuery class, and is listed below. It basically just provides a convenient environment for the execution of rsMeta.getColumnName (i).

public String getColumnName( int i ) {
  try {
     return rsMeta.getColumnName( i );
  }//End try
  catch ( SQLException sqlex ) {
     jTextArea.append( sqlex.toString() );
     return null;
  }//catch
  catch ( Exception excp ) {
    // process remaining Exceptions here
    jTextArea.append( excp.toString() );
    return null;
  }//End try-catch
}//End getColumnName method

In using Forte CE with JTables, the data model object is usually established with the Component Inspector facility.

Under the Component Inspector's Properties tab for the JTable, click on model. Three dots appear to the right of the field. Clicking on the three dots brings up the window depicted in the image above. Under the Form Connection tab, click on the radio button labeled User Code, and enter the name of object representing the column model. In this case, it is one of Model's inner classes, cqtm, the CustQueryTableModel object.

In the CustQuery class, we also use the Component Inspector to establish a mouse click event handing method for the JTable. Clicking on the mouseClicked button, under the Events tab, produces a generated method name. The Component Inspector window is depicted below.

Hitting return causes Forte CE to generate an empty method in the CustQuery class for JTable mouse click handling. Here is the method, with my table click handling code.

private void 
    jTable2MouseClicked(
    java.awt.event.MouseEvent evt) {
 
    tablerow = jTable2.getSelectedRow();
    cdtm.setTableRow(tablerow);
        
    if (custData != null) {
        custData.setVisible(false);
        custData = new CustData( mod );
     }//End if
     else { 
        custData = new CustData( mod );
     }//End if-else
     
 }  

I use the JTable method getSelectedRow to extract the table row where the mouse click event originated. A set method is used to transfer this data to the CustDataTableModel inner class, for later use.

CustData

As in previous examples, if a CustData window is active, it is made invisible then reinstantiated. If there is no CustData window active, a new one is instantiated.

The CustData window is depicted below.

Now let's see what happens in inside the CustData class.

Most of the CustData constructor code is standard for all my JFrame extended GUI classes, as covered previously. There are three unique statements of interest.

cdtm.setJTextField( jTextField1 );
cdtm.tableQuery(); cdtm.fire();

The set statement transfers the JTextField object jTextField1 to the CustDataTableModel inner class, so that during processing, the query logic can write to the feedback field on the CustData's GUI.

The cdtm.tableQuery and cdtm.fire method calls build the CustData JTable. The tableQuery code is listed below.

public void tableQuery() {
       
  try {
     rs = cqtm.getResultSet();
     rsMeta = cqtm.getAddressesMetaData();
     totalrows = new Vector();
     /* point to the row corresponding */
     /* to the  
                   JTable click */
     rs.absolute(tblrow+1);
     /* get the autonumber index */
     index = rs.getString(1);
     colstring = rsMeta.getColumnName(1);
     /* create customer data table */
     for(int i=2;i<=
     rsMeta.getColumnCount();i++){
        String[] rec = new String[30];
        rec[0] = rsMeta.getColumnName(i);
        rec[1] = rs.getString(i);
        totalrows.addElement( rec );
     }//End for loop
       jTextArea.append( "Query successful\n" );
     }//End try
     catch ( SQLException sqlex ) {
        /* write to DBMaster msg area */
        jTextArea.append( sqlex.toString() );
     }//catch
     catch ( Exception excp ) {
       // process remaining Exceptions here
       /* write to DBMaster msg area */
       jTextArea.append( excp.toString() );
  }//End try-catch
}//End tableQuery method
                

The rs = cqtm.getResultSet; statement fetches the ResultSet object generated from clicking on a customer in the CustQuery JTable. This ResultSet will always contain just one row of data. But I use the column names from this row as the elements of column 1 in the CustData JTable display. And the data elements are used as column 2. This sounds confusing, but it will become clear as we look further into the code.

The second statement, rsMeta = cqtm.getAddressesMetaData;, fetches the ResultSetMetaData object from its previously established repository. In this case, the object could also have been generated from the execution of rs.getMetaData. Sometimes, in this application, we don't have easy access to a ResultSet object to execute this getMetaData method. That is the reason for the getAddressesMetaData method.

Next, the Vector object totalrows is instantiated. Then the ResultSet method rs.absolute(tblrow+1); is fired (new in JDBC 2.0). This moves the result set pointer to the record corresponding to the JTable row click. Note that the JTable row click is a 0 based index, and the ResultSet method is based on 1, so the value must be incremented by 1.

The AutoNumber index field (generated by the database software during an insert operation) is extracted with the index = rs.getString(1); statement. The column name for this field is interrogated with the following method call: colstring = rsMeta.getColumnName(1);

Then the table is built using the following code block.

for(int i=2;i<=rsMeta.getColumnCount();i++){
   String[] rec = new String[30];
   rec[0] = rsMeta.getColumnName(i);
   rec[1] = rs.getString(i);
   totalrows.addElement( rec );
}//End for loop

An array of strings called rec is built, containing data pairs, a column name, and a value. This pair is successively added to the Vector object totalrows. Once this data structure is built, it becomes available for automatic JTable generation, primarily through the getValueAt method, which is listed below.

public Object getValueAt(int row, int col) {
 return ((String[])totalrows.elementAt(
 row)) [col];
}

This method extracts first the integer row from the Vector object totalrows (a pair of string values) then the actual String element, based on the integer [col] which extracts a value from the array.

The CustData window is the primary window for the application's processing capabilities. It offers the following operations.

  • Update
  • Delete
  • New Customer
  • Place Order
  • Order History

CustData--Update

To update from within the CustData window, click inside a table field that has been made available for edit (the right hand column). The cursor is placed at the end of the existing string, if any. Existing text can then be replaced or added to, and the edited values are made available for interrogation by the getValueAt method, only after the enter key has been pressed, or the mouse has been used to click attention to another field. If this requirement is not satisfied, the data will not be entered, and the update will appear not to function properly, even if there is visible text in the table field. But it should be noted that JTables are very flexible, and can be configured to react to a wide variety of keyboard- and mouse-driven events.

Here is the code block that is activated by clicking the Update button.

/* update button clicked */
                            
cdtm.tableUpdate();

Then the CustDataTableModel cdtm.tableUpdate method is fired. The update code is listed below.

public void tableUpdate() {

  try {
     int i;
     strgBuffer = 
        getUpdateStatement
        ( rsMeta.getColumnCount(
        ), strgBuffer );
     String strgArg = strgBuffer.toString();
     pstatUpdate = dbc.prepareStatement(
      strgArg );
     for ( i=0; i < cdtm.getRowCount(
     ); i++ ) {
     String tableString;
       if ( i == 2 ) {
           tableString = stripOut
           ( (String)cdtm.getValueAt( i, 1 ) );
       } else {
         tableString = (String)cdtm.getValueAt(
          i, 1 );
       }//End if-else
       pstatUpdate.setString(
        i+1, tableString ); 
     }//End for loop
     pstatUpdate.setString( i+1, index );
    
     pstatUpdate.executeUpdate();
          
     jTextField.setText("Update successful");
     jTextArea.append("Update successful\n");    
     cqtm.tableQuery();
     cqtm.fire();
 
  }//End try
  catch ( SQLException sqlex ) {
    jTextField.setText(
    "SQL Error-see DBMaster");
    jTextArea.append( sqlex.toString() );
    return;
  }
  catch ( Exception excp ) {
      // process remaining Exceptions here
      jTextField.setText(
      "Error-see DBMaster");
      jTextArea.append( excp.toString() );
      return;
  }//End try-catch 
 
}//End method tableUpdate   

This tableUpdate method uses the PreparedStatement object. The main advantage of this technique is an increase in execution speed. In most cases, using this technique, the SQL statement will be sent to the DBMS right away where it will be compiled. As a result, the PreparedStatement object contains an SQL statement that has been precompiled. Values are then supplied to the PreparedStatement object via setXXX methods. In the context of this update operation, the string that will be passed as an argument to Connection prepareStatement method dbc. prepareStatement( strgArg ) has this form:

UPDATE Addresses SET  First_Name = ?,
 Last_Name = ?, ?  WHERE  AddrID = ?

This string is generated by a call to my getUpdateStatement method, which generates the SQL string using ResultSetMetaData to interrogate the column names. Here is the code for the getUpdateStatement method.

public StringBuffer 
  getUpdateStatement(
  int j, StringBuffer preparedSQL ){
  preparedSQL = new StringBuffer( 700 );
  preparedSQL.append("UPDATE Addresses SET  ");
  try {
     int i;
     for ( i=2; i < j ; i++ ) { 
        preparedSQL.append(
        rsMeta.getColumnName(i)+" = ?, ");
     }//End for loop
     preparedSQL.append(
     rsMeta.getColumnName(i)+" = ? WHERE " + 
     rsMeta.getColumnName(1) + " = ? " );
     }//End try
     catch ( SQLException sqlex ) {
        /* write to DBMaster msg area */
        jTextArea.append( sqlex.toString() );
        sqlex.printStackTrace();
        }//catch
        catch ( Exception excp ) {
          // process remaining Exceptions here
          /* write to DBMaster msg area */
          jTextArea.append( excp.toString() );
          excp.printStackTrace();
        }//End try-catch
       
        return preparedSQL;
   }//End getInsertStatement method

This method instantiates a StringBuffer object, then uses append method calls to successively build up the PreparedStatement SQL command string. All the columns in the Address table are used. The WHERE search criterion is used to locate the record to be updated by its AutoNumber field AddrID located in column 1 of Addresses. As always, in this application, column names are extracted using the ResultSetMetaData object. The getUpdateStatement method listed above returns the command string in a StringBuffer, which must then be converted to a String object and passed to the statement below.

String strgArg = strgBuffer.toString();
pstat = dbc.prepareStatement( strgArg );

The Connection method prepareStatement creates the PreparedStatement object pstat, which is then made ready for execution by the following for loop, which supplies values for the place holder question marks ? in the SQL string.

for ( i=0; i < cdtm.getRowCount(
); i++ ) {
String tableString;
   if ( i == 2 ) {
      tableString = stripOut
      ( (String)cdtm.getValueAt(
       i, 1 ) );
   } else {
      tableString = (String)cdtm.getValueAt(
       i, 1 );
   }//End if-else
   pstatUpdate.setString(
    i+1, tableString ); 
}//End for loop
pstatUpdate.setString(
 i+1, index );

The index string is created during execution of the CustQueryTableModel queryTable method. It is the unique AutoNumber field in column 1 of the Addresses table. The index string is used to identify the record to be updated. Its value is entered by the last statement in the above code block pstatUpdate.setString( i+1, index );.

Notice the method call stripOut in the above code. This is my method for removing all non-numeric characters. I use it only for Primary_Phone column values.

public String stripOut( String strg ) {
/* strip out non-numeric characters */
String numStrg = new String();
   for ( int i =
    0; i < strg.length(); i++ ) {
     if ( strg.charAt(i) >=
        '0' && strg.charAt(i) <= '9' ) {
         numStrg += strg.substring(i,i+1);
      }//End if
   }//End for loop
   return numStrg;
}//End stripOut method

This code returns a String value. Remember when using the String method substring, the second index must be incremented up by one, to extract one character, which is then iteratively added to the output String variable.

It is also important to note that the cdtm.getValueAt method calls are fetching values from the JTable fields, not from the database. All the JTable fields are taken, and any changes made by the user will be entered into the database when the following PreparedStatement method is executed.

pstat.executeUpdate();

CustData-Delete

When the user clicks the delete button on the CustData GUI, the following code is executed.

cdtm.custDelete();
setVisible( false );

After the delete method is fired, the CustData window is made invisible, so that the user can go back to the CustQuery GUI and select another operation. Here is the method that is fired in the CustDataTableModel inner class.

public void custDelete() { 
  
  try { 
     batchError = false; 
     statement = dbc.createStatement();
     statement.addBatch(
     "DELETE FROM Addresses WHERE " + 
        colstring + " = " + index );
     statement.addBatch(
     "DELETE FROM Orders WHERE " + 
        colstring + " = " + index ); 
     batchErrorCodes = 
     statement.executeBatch();
     cqtm.setQueryAll( false );
     cqtm.setQueryString( null );
     cqtm.setColString(
      rsMeta.getColumnName( 4 ) );
   }//End try
   catch ( SQLException sqlex ) {
     jTextArea.append( sqlex.toString() );
     jTextField.setText(
      "SQL Error-See DBMaster" );
     return;
   }//End catch
   catch ( Exception excp ) {
     // process remaining Exceptions here
     jTextArea.append( excp.toString(
     ) );
     jTextField.setText(
      "Error-See DBMaster" );
     return;
   }//End try-catch block
        
   batchError = false;
   for (int i=0; i < 
   batchErrorCodes.length; i++ ) {
            
      if ( batchErrorCodes[i] > 0 ) {
         jTextArea.append(
          "Delete Successful\n" );
       }//End if 
       else if (
        i == 1 && batchErrorCodes[i] == 0 ) {
          jTextArea.append(
          "No Orders Records to Delete\n" );
       } 
       else {
          jTextArea.append(
           "Delete Error\n" );
          jTextField.setText(
           "Delete Error" );
          batchError = true;
       }//End if-else
            
       if ( !batchError ) 
          jTextField.setText( 
          "Delete Successful" );
              
    
       tableClear();
       cqtm.tableQuery();
       cqtm.fire();
        
   }//End for loop

}//End custDelete method

Batch Update was one of the new features brought in with the JDBC 2.0 core API. Batch Updates can be more efficient, and can be particularly useful for transaction processing, where the failure of one operation in a group of transactions may require rolling back the whole group of transactions. I have not implemented this, but the code I have written can easily be adapted to this scheme, assuming the DBMS driver supports it. You can read more about this topic in JDBC API Tutorial and Reference, Second Edition by White, Fisher, et. al.

Let's take a look at how a Batch Update is put together.

batchError = false; 
statement = dbc.createStatement();

These lines define the boolean batchError, for error processing, then a Statement object is defined in the usual way, firing a createStatement method off the Connection object dbc. The following lines are then executed.

statement.addBatch("DELETE FROM Addresses WHERE " + 
   colstring + " = " + index );
statement.addBatch("DELETE FROM Orders WHERE " + 
   colstring + " = " + index ); 
batchErrorCodes = statement.executeBatch();

The SQL command strings are added to the batch queue, via the addBatch method calls. Then the executeBatch method completes the delete operations. In my table schema, the field AddrID is the unique AutoNumber field generated by the DBMS software when a new record is inserted into the Addresses table. I repeat that value in column 2 of the Orders table when an order is placed. I give it the same name, AddrID. So column 1 of Addresses is AddrID and column 2 of Orders is also AddrID. Multiple orders entered into the Orders table carry the AddrID tag derived from the Addresses table. Column 1 of the Orders table is called CustID. This scheme simplifies the syntax of the delete operations. The same SQL delete syntax ( DELETE FROM <Table> WHERE AddrID = n) works for both tables, and deletes all records associated with a given customer.

The remainder of the code is dedicated to processing any error conditions that might arise.

batchError = false;
for (int i=0; i < batchErrorCodes.length; i++ ) {

   if ( batchErrorCodes[i] > 0 ) {
       jTextArea.append( "Delete Successful\n" );
   }//End if
   else if ( i == 1 && batchErrorCodes[i] == 0 ) {
      jTextArea.append(
       "No Orders Records to Delete\n" );
   }
   else {
      jTextArea.append( "Delete Error\n" );
      jTextField.setText( "Delete Error" );
        batchError = true;
   }//End if-else

    if ( !batchError )
      jTextField.setText( "Delete Successful" );

   tableClear();
   cqtm.tableQuery();
   cqtm.fire();

       }//End for loop

The executeBatch method returns an array of int values corresponding to the number of operations executed. In this case, a value is returned for each of the delete operations (two values).

The expected return code from a successful delete operation is 1. A 0 is returned if there is no data to delete, which may be the case if a customer has placed no orders. After these values have been processed, table fields are cleared, message feedback is written to the local JtextField, and the JTextArea in DBMaster, then the tableQuery method is fired, with previously established null query parameters, to return a blank JTable on the CustQuery GUI. Then the CustData window is closed under control from the CustData class code, which of course nullifies sending data to the JTextField area, but I included it for completeness.

CustAdd

Clicking the New Cust. button on the CustData GUI executes the following code.

custAdd = new CustAdd( mod );
catm.tablePopulate();
catm.fire();

This instantiates the CustAdd class, brings up its window, and renders its JTable with the tablePopulate method call. Here is the code.

public void tablePopulate() {
  try {
       rsMeta = cqtm.getAddressesMetaData();
           totalrows = new Vector();
       colstring = rsMeta.getColumnName( 1 );
       for (int i = 2; i <=
        rsMeta.getColumnCount(); i++ )  {           
                String[] rec = new String[30];
                rec[0] = rsMeta.getColumnName(i);
                totalrows.addElement( rec );
       }//End for loop
     }//End try
     catch ( SQLException sqlex ) {
        jTextArea.append( sqlex.toString() );
     }//catch
     catch ( Exception excp ) {
        // process remaining Exceptions here
        jTextArea.append( excp.toString() );
   }//End try-catch
 }//End tableQuery method

This method builds a JTable for data input with the same structure as the CustData JTable. The for loop begins with 2, because the first column is the AutoNumber field AddrID, which is generated automatically by the DBMS software during an insert operation. Otherwise we are building the same Vector object filled with String array objects, as we have seen before. Except that the data column here is left blank. As you can see, only one String array element is added to the Vector each time through. That element is column name. The for loop is controlled by the getColumnCount method call.

Once the CustAdd window is active, values are registered in the fields by hitting return after entry, or changing focus to a new field with a mouse click. Then the Create Record button is clicked. Primary_Phone is the only required field for the CustAdd operation. The CustAdd GUI is pictured below.

Clicking on the Create Record button fires the following code.

catm.tableInsert();
catm.fire();

Let's look at the tableInsert method.

public void tableInsert() {
       
   try {
      /* my method to build prepared */ 
      /* statement string */
      strgBuffer = getInsertStatement( 
         rsMeta.getColumnCount(), strgBuffer );
           
      /* convert StringBuffer to String */
      pstrg = strgBuffer.toString();
      pstat = dbc.prepareStatement( pstrg );
      /* Statement stmt = 
      dbc.createStatement(); */
      for ( int i=0; i < getRowCount(); i++ ) {
         String strgVal = 
         (String)getValueAt( i, 1 );
         if( i == 2 ) {
             if ( strgVal == null ) {
                jTextField1.setText
                  ( "Primary_Phone required" );
                jTextArea.append
                  ( "Primary phone 
                  field required\n" );
                return;
              }
              pstat.setString(
              i+1, stripOut( strgVal ) );
          } else pstat.setString( i+1, strgVal ); 
      }//End for loop 
      pstat.executeUpdate();
      cqtm.tableQuery();
      cqtm.fire();
      jTextField1.setText("");
      jTextField1.setText(" Insert successful ");
      jTextArea.append
         (" Insert into Addresses successful\n");
  }
    catch ( SQLException sqlex ) {
     jTextArea.append( sqlex.toString() );
     jTextField1.setText
          ("SQL Error-see DBMaster window" );
  }
     catch ( Exception excp ) {
      // process remaining Exceptions here
      jTextArea.append( excp.toString() );
      jTextField1.setText(
       "Error-see DBMaster window" );
  }//End try-catch                 
   }//End method tableInsert  

This getInsertStatement method call returns an SQL string of the following form:

INSERT INTO Addresses (
First_Name, Last_Name, ?) VALUES (?, ?, ?)


strgBuffer = getInsertStatement( 
   rsMeta.getColumnCount(), strgBuffer );
           
/* convert StringBuffer to String */
pstrg = strgBuffer.toString();
pstat = dbc.prepareStatement( pstrg ); 

The StringBuffer is then converted to a String, and passed as an argument for the creation of the PreparedStatement object pstat. Then the for loop below fetches entered data from the table fields using the getValueAt method.

for ( int i=0; i < getRowCount(); i++ ) {
   String strgVal = (String)getValueAt( i, 1 );
   if( i == 2 ) {
       if ( strgVal == null ) {
          jTextField1.setText
            ( "Primary_Phone required" );
          jTextArea.append
            ( "Primary phone field required\n" );
          return;
        }
        pstat.setString(i+1, stripOut( strgVal ) );
    } else pstat.setString( i+1, strgVal ); 
}//End for loop 

And these question mark parameters are then set using the pstat.setString method calls. Notice the if( i == 2 ) block is used to require that a Primary_Phone field is entered. And the stripOut method call removes non-numeric values, as we have seen before.

The operation is finalized with the following code.

pstat.executeUpdate();
cqtm.tableQuery();
cqtm.fire();
jTextField1.setText("");
jTextField1.setText(" Insert successful ");
jTextArea.append
(" Insert into Addresses successful\n");

The PreparedStatement method pstat.executeUpdate enters the new data into the database. The query table is refreshed, to reflect the new data, and messages are written to both the local JTextField, and the JTextArea located on DBMaster.

CustOrder

The CustOrder window is instantiated by clicking on the Place Order radio button at the bottom of the CustData window. Clicking this button fires the following code within CustData.

if (custOrder != null) {
    custOrder.setVisible(false);
    custOrder = new CustOrder( mod );
 }
 else { 
    custOrder = new CustOrder( mod );
 }//End if-else

The CustOrder window is rendered, using techniques that should be familiar by now. The GUI is pictured below.

These statements are executed by the CustOrder constructor.

cotm.tablePopulate();
cotm.fire();

In this case, tablePopulate fills out a two column JTable corresponding to the column names of the Orders table. Let's look at the code.

public void tablePopulate() {
       
  try {
     rsMeta = cqtm.getOrdersMetaData();

        totalrows = new Vector();

     colstring = rsMeta.getColumnName(1);
     for (int i = 4;i <= 
           rsMeta.getColumnCount();i++){
        String[] rec = 
          new String[ rsMeta.getColumnCount(
          ) - 3 ];
        rec[0] = rsMeta.getColumnName( i );
        totalrows.addElement( rec );  
     }//End for loop
    }//End try
    catch ( SQLException sqlex ) {
       jTextArea.append( sqlex.toString() ); 
    }//catch
    catch ( Exception excp ) {
        // process remaining Exceptions here
        jTextArea.append( excp.toString() ); 
   }//End try-catch
 }//End tablePopulate method


This tablePopulate method is similar to the one we examined previously, but notice that this time we are rendering JTable fields for input corresponding to the Orders table, beginning with column 4 of Orders, and proceeding to the last column, controlled by the rsMeta.getColumnCount method call. Orders columns 1-3 contain CustID, AddrID, and Order_Date, which are not applicable for user order input.

When the CustOrder Place Order button is clicked, the following code is executed.

cotm.tableInsert();
cotm.fire();

Here is the tableInsert code listing.

public void tableInsert() {

 /* get AutoNumber field from Addresses table */
 index = cdtm.getIndex();

 try {
   /* fire method to build prepared */
   /* statement string */
    preparedSQL = getInsertStatement(
       rsMeta.getColumnCount(), preparedSQL );
   /* convert StringBuffer to String */
    String stringSQL = preparedSQL.toString();
    pstat = dbc.prepareStatement( stringSQL );
   /* get current date in SimpleDataFormat */
    java.util.Date date = new java.util.Date();
    SimpleDateFormat fmt =
      new SimpleDateFormat("yyyy.MM.dd-HH:mm z");
    String dateString = fmt.format( date );
   /* AutoNumber index field from Addresses table */
    pstat.setString( 1 , index );
    pstat.setString( 2, dateString );
    /* fill in product values */
    for ( int i=0; i < getRowCount(); i++ ) {
      pstat.setString(i+3,(String)getValueAt(i,1));
    }//End for loop
    pstat.executeUpdate();
    jTextArea.append(
     "Order Successfully Placed\n" );
    jTextField.setText( "Order Placed" );
   }//End try
   catch ( SQLException sqlex ) {
   jTextArea.append( sqlex.toString() );
   jTextField.setText( "SQL Error-See DBMaster" );
   }
   catch ( Exception excp ) {
     // process remaining Exceptions here
     jTextArea.append( excp.toString() );
     jTextField.setText( "Error-See DBMaster" );
 }//End try-catch
}//End method tableUpdate

This code is also similar to code we have seen before. The SQL string, built by the method call getInsertStatement, has the following form:

INSERT INTO Orders (
 AddrID, Order_Date, ?) VALUES ( ?, ?, ?.)

The following code handles generation of the Date String.

java.util.Date date = new java.util.Date();
SimpleDateFormat fmt = 
  new SimpleDateFormat("yyyy.MM.dd-HH:mm z");
String dateString = fmt.format( date );

Firing the SimpleDateFormat's format method with the java.util.Date object as an argument, yields a date string that looks like this.

2001.06.21-10:35 PDT

The SimpleDateFormat argument yyyy.MM.dd-HH:mm z designates the form of the date string.

The yyyy string corresponds to a four digit year field, separated by a ".". MM, refers to a two digit month field, dd indicates a two digit day in the month, separated by a "-", then HH denotes a two digit 0-23 hour field, and mm shows a minutes field, and finally z indicates the time zone.

I constructed the date field in this way for convenient sorting during JTable data presentation.

CustOrderHist

The CustOrderHist window is instantiated by clicking on the Order History radio button at the bottom of the CustData window. Clicking this button fires the following code within CustData.

if (custOrderHist != null) {
      custOrderHist.setVisible(false);
      custOrderHist = new CustOrderHist( mod );
    }
    else { 
       custOrderHist = new CustOrderHist( mod );
    }//End if-else

The CustOrderHist constructor is standard for this application, but contains a few unique aspects.

chtm.tableQuery();
chtm.fire(); 
TableColumn tcol =
  jTable2.getColumnModel().getColumn(0);
tcol.setPreferredWidth(125);

First, the CustHistTableModel tableQuery method and its fire method are executed.

Then the CustHistTableModel JTable is reconfigured using the setPreferredWidth method, to expand the width of the first column to accommodate the date string. The width is set to 125 pixels.

Here is the CustOrderHist window.

The upper JTable is again a rendering of the CustDataTableModel object cdtm, originally used in the CustData class. The lower JTable is original to this class. It is sorted by date, and shows orders for this customer, with the products listed as column headings, extracted from the Orders table. Again, if the products list is expanded, this table will automatically accept those changes, because ResultSetMetaData is used to generate the table and the product names.

There is no action possible with this GUI, the tables are rendered upon creation of the window. Here is the CustHistTableModel tableQuery method.

public void tableQuery() {
  
    rsMeta = cqtm.getOrdersMetaData();
    index = cdtm.getIndex();

    try {
        String strg = "SELECT * FROM Orders " +
            " WHERE " + rsMeta.getColumnName(2) +
            " = "+ index + " ORDER BY " +
        rsMeta.getColumnName(3);
        rs = statement.executeQuery( strg );
          totalrows = new Vector();

        while ( rs.next() ) {
            String[] rec =
               new String[rsMeta.getColumnCount()-2];

            int j = 0;
            for (int i=0;i<=
            rsMeta.getColumnCount();i++) {
                if ( i>2 ) {
                    rec[j]=rs.getString
                        ( rsMeta.getColumnName(i));
                    j++;
                }//End if block

            }//End for loop
         totalrows.addElement( rec );

         }//End while loop
         jTextArea.append(
          "CustHist Query successful\n" );
      }//End try
      catch ( SQLException sqlex ) {
         jTextArea.append( sqlex.toString() );
      }//catch
      catch ( Exception excp ) {
         // process remaining Exceptions here
         jTextArea.append( excp.toString() );
   }//End try-catch

}//End tableQuery method

The first step is to fetch the ResultSetMetaData object, and the AutoNumber field value for this customer from the Addresses table.

rsMeta = cqtm.getOrdersMetaData();
index = cdtm.getIndex();

Then the SQL statement is constructed.

String strg = "SELECT * FROM Orders " +
 " WHERE " + rsMeta.getColumnName(2) +
 " = "+ index + " ORDER BY " + 
 rsMeta.getColumnName(3);

The column 2 field name being accessed here by rsMeta.getColumnName(2) is AddrID, which corresponds to the AutoNumber field of the same name in the Addresses table. This statement will select all columns and all records that contain the customer's AutoNumber field from the Addresses table. The result may be null, if the customer has ordered nothing.

The code block below finishes off the method.

rs = statement.executeQuery( strg );
     totalrows = new Vector(); 
            
while ( rs.next() ) {
  String[] rec = 
     new String[rsMeta.getColumnCount()-2];    
                
  for (int i=0;i<=
     rsMeta.getColumnCount();i++) {
     if ( i >2 ) {
          rec[i-3]=rs.getString
          ( rsMeta.getColumnName(i));
      }//End if block
   }//End for loop
totalrows.addElement( rec );
                
}//End while loop
jTextArea.append( "CustHist Query successful\n" ); 

The SQL string is executed by the executeQuery method, then the result set is iteratively interrogated using the while(rs.next()) syntax, which we have seen before. When the result set is empty, the next method returns false.

The rest of the code is standard procedure for building the Vector data structure for JTable rendering. The only wrinkle is the manipulation of the index variable i, to skip over the first two columns of Orders, which contain, respectively, the AutoNumber field, and the AddrID field which relates the records in Orders to a customer record in Addresses.

Conclusion

This application offers Duke's Bakery proprietor Kate Cookie an overview of Java and JDBC technologies that will be useful to her in adding to, and customizing the order entry software to suit her needs. It also offers an architecture that separates GUI generation from processing code, which makes maintenance and modification much easier. Now that she has seen the application in action, and studied the software, she's eager to do some programming. She told me she's planning to implement a delete/update capability for the Orders table. And she has some other ideas percolating. Go forth and code.

Code Listing

Link to download (right-click your mouse to "Save As")

Application Execution Footnote

To run the application without using Forte CE:

  1. Create the following directory path (MS-DOS syntax used here).
  2. C:\Development\meloan

  3. Copy the *.java files into the meloan directory.
  4. Inside the meloan directory from the MS-DOS prompt, type:
  5. javac *.java

    to compile all the Java source files.

  6. Change the directory back to the root, and type:
  7. C:\>java Development.meloan.Controller

Remember that you must have Microsoft Access installed, and you must set it up for use with the included BakeryBook.mdb database file. For information on this setup process, consult the Microsoft Access section of Duke's Bakery-Part I.

Reference Texts

Reference URLs

About the Author

Michael Meloan



Have a question about programming? Use Java Online Support.