Articles
Java Platform, Standard Edition
Duke's Bakery, Part II - A JDBC 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
|
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:
- Create the following directory path (MS-DOS syntax used here).
-
C:\Development\meloan - Copy the
*.javafiles into the meloan directory. - Inside the meloan directory from the MS-DOS prompt, type:
-
javac *.javato compile all the Java source files.
- Change the directory back to the root, and type:
-
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
- JDBC API Tutorial and Reference, Second Edition: Universal Data Access for the Java 2 Platform, White, Fisher, Cattell, Hamilton and Hapner, Addison-Wesley
- The Java Tutorial, Second Edition: Object-Oriented Programming for the Internet (Book/CD) , Campione and Walrath, Addison-Wesley
- Advanced Java 2 Platform, How to Program, First Edition, Deitel and Deitel, Prentice Hall
- Java Design Patterns: A Tutorial with CD-ROM , Cooper, Addison-Wesley
Reference URLs
- JDBC Data Access API
- Forte Tools
- Duke's Bakery-A JDBC Order Entry Prototype--Part I
- Developing the Java 2D Art Applet Using Forte for Java Community Edition
- Threads and Swing
About the Author
Michael Meloan
| |
Have a question about programming? Use Java Online Support.
Printer View