pro duct \prod'ukt\ n: something produced by natural, human, or mechanical effort
Welcome to the penultimate sample intranet application. This, the Product Maintenance application, isn't as simple as the last few have been. It is more in line with your first application, Employee Files, and should give you a good understanding of reading and writing database rows.
Most companies in business for profit have something to sell. Whether it is services or a tangible item, it is a product. This application enables you to manage information about these products. When you have your products under control, you can track certain aspects. One such aspect is customer support, which is explored in your final application-Customer Support Maintenance.
This application enables the user to create, update, and delete rows in the product table. This new table holds rows that describe products that you or your company sell, manufacture, distribute, and so on. The product is what you output.
This chapter covers the following topics in regard to the Product Maintenance application:
This four-step format just outlined is used throughout all of the sample application chapters, and it should provide you with valuable insight and ideas for creating your own intranet applications.
This application is useful for companies that sell products. To better clarify this, let's take a look at one such fictitious company: Johnston, Ulysses, Norman, and Kaiser.
Johnston, Ulysses, Norman, and Kaiser (JUNK) is a fictitious corporation that will serve as a model for the product and customer support sample applications in this book. They are an average-sized company and sell a variety of odd and obscure products throughout the world. These sales are through large chain-stores, smaller mom-and-pop stores, and mail-order catalogs. Their products are bought from small manufacturers and distributors around the United States.
The catalogs are small, presenting hundreds of items to the consumer, and they are one of JUNK's biggest sales tools. Generally, all the items in the catalog are under $5.00. JUNK makes its money on the shipping and handling charges, and the fact that consumers often buy 10 items from the catalog because items are so inexpensive.
By now you're wondering what kind of products JUNK sells. I was going to have them sell widgets. However, in the last 10 years or so, widgets, in relation to software construction, have taken on a new connotation. Widgets now refer to software components more than a generic product line.
So JUNK sells the types of products that you don't go out and buy. That is to say, 99 percent of its product line is impulse-buy material. JUNK sells the kind of stuff you see at check-out counters in stores, such as red rubber snakes or cartoon character figurines that you stick on the end of your finger. My favorite JUNK product is the electric lollipop. This is a large sucker on a motorized stick. Press the button and it spins the lollipop. All you need to do is stick out your tongue!
This is the type of company that can use the product maintenance and customer support applications on its intranet. This company constantly gets calls from customers with problems. These problems are answered by customer support people on a daily basis. However, they currently have no central repository, or knowledge base, that holds all of the answers. By using the product maintenance and customer support applications, they will be able to better use their combined knowledge for support calls.
This application is quite similar to the Employee Files application, and it has many of the same qualities. At the core, it's a database row manipulation application. With it you can create, update, and delete rows in the product table.
The proposed interface is similar to the Employee Files as well. Figure 19.1 is the proposed user interface for the Product Maintenance program.
Figure 19.1 : The Product maintenance user interface.
This application is semi-modeless, which means that it has no operating mode. You shouldn't have to inform the program that you're going to be adding new records or removing records. You can just flow through the program and it determines what should be done.
The basic function of this application is to create, read, update, and delete product records. These records are stored in a database. The database design is discussed in detail later in this chapter.
Think of this application as a pointer into the product table. The record that the pointer is situated upon is the current record. This current record is displayed to the user, and the user can do with it what he or she will. The user can also insert records into the table.
The user needs a method of moving this pointer from product to product. The best way to present this information to the user is through the use of a pick list.
The pick list, as previously discussed, is a selection of all the records in the product table. This selection should include the name of each product. The user should be allowed to select one name from the displayed list. After the selection is made, the chosen record should be fully retrieved and displayed.
The selection of a record can be accomplished by either a double-click on the list item, or a single-click followed by the user pressing the OK button.
Figure 19.2 represents the concept of the product pick list.
Figure 19.2 : The product pick list.
This pick list should be opened in response to the user pressing the Choose button, a standard SimpleDBUI button.
This application is responsible for manipulating news rows. These rows should be stored in a single table. The table used in this sample application is called the News table.
The information stored in the News table corresponds to the information
that is to be edited as described earlier. Table 19.1 shows the
columns that need to be stored in the product table.
| Description | Column Name | Type | ||
| Product ID | prod_id | number( 5 ) | ||
| Description | desc_text | char( 80 ) | ||
| Quantity On Hand | qty_on_hand | number( 10 ) | ||
| Quantity On Order | qty_on_order | number( 10 ) | ||
| Last Received Date | last_rcv_date | date | ||
| Comment | comment_text | char( 80 ) |
Figure 19.3 shows the entity relationship diagram for the database as it stands in this chapter. In Chapter 20, "Customer Support Maintenance," you see the final entity relationship diagram. It will represent all of the tables that have developed for the applications in this book.
Figure 19.3 : The entity relationship diagram, including the new product table.
| Note |
Entity relationship diagrams are discussed in Chapter 13, "Employee Files." |
In addition to creating a table, a database synonym for the table is created as well. This enables everyone to access the table with the same name, without having to worry about the schema in which the table resides.
Listing 19.1 is the list of SQL commands used to create the product table and synonym.
Listing 19.1. The product table creation SQL.
/* Create the table */
create table prod_t
(
prod_id number( 5 ) not null,
desc_text char( 80 ),
qty_on_hand number( 10 ) not null,
qty_on_order number( 10 ) not null,
last_rcv_date date default null,
comment_text char( 80 )
);
/* Create a primary key */
alter table prod_t
add
(
primary key
(
prod_id
)
);
/* Grant access for the table to the user role */
grant select,insert,delete,update on prod_t to ia_user_r ;
/* Drop any existing public synonym */
drop public synonym prod ;
/* Create a public synonym for our table */
create public synonym prod for prod_t ;
| Note |
The preceding SQL is quite generic, but it still might not work on every database. This particular SQL has been tested with Oracle. |
The first SQL clause creates the table prod_t. The second clause creates a primary key using the prod_id column. Making this the primary key ensures that the values in the column are unique across all rows. Lastly, the public synonym prod is created for the table prod_t.
The rest of this chapter discusses the implementation of the Product Maintenance program. The first item discussed is the user interface and how it was created. Secondly, the database access used in the program is covered. Finally, any programming pitfalls that came up during the application construction are examined.
To achieve the design goal presented earlier, you need no special user-interface components. The GridBagLayout layout manager is used for this application. It uses the same hard-coded row and column heights as the Employee Files program.
Listing 19.2 shows the user-interface construction code for the Product Maintenance program.
Listing 19.2. The Product Maintenance interface construction source code.
//****************************************************************************
//* Members &nb sp; *
//****************************************************************************
JifTextField prod_id;
JifTextField desc_text;
JifTextField qty_on_hand;
JifTextField qty_on_order;
JifTextField last_rcv_date;
JifTextField comment_text;
//****************************************************************************
//* Constructor ; *
//****************************************************************************
public
ProductUI( SimpleDBJiflet jiflet )
{
super( jiflet );
GridBagLayout gbl = new GridBagLayout();
int cw[] = { 14, 14, 14, 14, 14, 14, 14, 14, 14, 14, 14, 14, 14, 14,
14, 14, 14 }; // 17
int rh[] = { 14, 14, 14, 14, 14, 14, 14, 14, 14 }; // 9
double rc14_0[] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
gbl.columnWidths = new int[ 17 ];
gbl.rowHeights = new int[ 9 ];
gbl.columnWeights = new double[ 17 ];
gbl.rowWeights = new double[ 9 ];
System.arraycopy( cw, 0, gbl.columnWidths, 0, 17 );
System.arraycopy( cw, 0, gbl.rowHeights, 0, 9 );
System.arraycopy( rc14_0, 0, gbl.columnWeights, 0, 17 );
System.arraycopy( rc14_0, 0, gbl.rowWeights, 0, 9 );
setLayout( gbl );
addWithConstraints( new Label( "Product ID:", Label.RIGHT ),
"anchor=east;x=0;y=0" );
addWithConstraints( new Label( "Description:", Label.RIGHT ),
"anchor=east;x=0;y=1" );
addWithConstraints( new Label( "Quantity On Hand:", Label.RIGHT ),
"anchor=east;x=0;y=3" );
addWithConstraints( new Label( "Quantity On Order:", Label.RIGHT ),
"anchor=east;x=0;y=4" );
addWithConstraints( new Label( "Last Received Date:", Label.RIGHT ),
"anchor=east;x=0;y=6" );
addWithConstraints( new Label( "Comments:", Label.RIGHT ),
"anchor=east;x=0;y=8" );
prod_id = new JifTextField( "", "prod_id", true );
prod_id.setStyle( JifTextField.NUMERIC );
addWithConstraints( prod_id, "x=1;y=0;width=5;fill=horizontal" );
desc_text = new JifTextField( "", "desc_text" );
addWithConstraints( desc_text, "x=1;y=1;width=13;fill=horizontal" );
qty_on_hand = new JifTextField( "", "qty_on_hand" );
qty_on_hand.setStyle( JifTextField.NUMERIC );
addWithConstraints( qty_on_hand, "x=1;y=3;width=13;fill=horizontal" );
qty_on_order = new JifTextField( "", "qty_on_order" );
qty_on_order.setStyle( JifTextField.NUMERIC );
addWithConstraints( qty_on_order, "x=1;y=4;width=13;fill=horizontal" );
last_rcv_date = new JifTextField( "", "last_rcv_date" );
addWithConstraints( last_rcv_date, "x=1;y=6;width=13;fill=horizontal" );
comment_text = new JifTextField( "", "comment_text" );
addWithConstraints( comment_text, "x=1;y=8;width=13;fill=horizontal" );
// Disable buttons...
saveButton.disable();
chooseButton.disable();
deleteButton.disable();
// Add the buttons...
addWithConstraints( newButton, "x=15;y=0;width=2;fill=horizontal" );
addWithConstraints( saveButton, "x=15;y=2;width=2;fill=horizontal" );
addWithConstraints( chooseButton, "x=15;y=4;width=2;fill=horizontal" );
// Tell which are which...
prod_id.setPrimaryKey( true );
prod_id.setNumeric( true );
qty_on_hand.setNumeric( true );
qty_on_order.setNumeric( true );
last_rcv_date.setDate( true );
// Set the focus to the first field...
clearScreen();
}
A notable item about this user interface (and other GridBagLayout jiflets in this book) is that the grid settings are hard-coded. A permanent 9 row by 17 column grid was used for the user interface. Only after the grid has been set are components placed within the grid.
First the layout to a new GridBagLayout is set. Then the labels that go into the layout next to where the text areas will go are created and placed. Next the JifTextFields that represent the columns are placed into the layout.
The New, Save, and Choose buttons are added to the layout as well. These are first disabled because they aren't active until certain events occur.
Finally the screen is cleared of any values, and defaults are placed in the correct columns.
Another class was developed for this application, the ProductPickList class. This class derives from the PickList class of the jif.awt package (as discussed in Chapter 11, "User Interface Classes") and presents the user with a selection of products. When one is chosen, the object stores the selection and waits for someone to ask who was chosen. Now take a look at some of the source code.
//****************************************************************************
//* ProductPickList & nbsp; *
//****************************************************************************
public class
ProductPickList
extends PickList
As stated, the PickList class is extended. When doing this, an init() method must be supplied. The following is that method:
//****************************************************************************
//* init *
//****************************************************************************
public void
init()
{
int rows = retrieveProducts();
if ( rows > 0 && getParent() instanceof Jiflet )
( ( Jiflet )getParent() ).verboseLog( "Retrieved " +
Integer.toString( rows ) + " Products" );
}
This method calls the retrieveProducts() method. Also, if this pick list is used with a jiflet and verbose mode is turned on, the number of products that were retrieved is written to the log file.
The retrieveProducts() method, shown in Listing 19.3, is the meat of this class. It performs an SQL SELECT statement from the database, parses the results, and places them in the pick list for the user to select from.
Listing 19.3. The retrieveProducts() method.
//****************************************************************************
//* retrieveProducts *
//****************************************************************************
int
retrieveProducts()
{
String sql;
boolean rv = false;
int rows = 0;
sql = "select * from prod order by desc_text";
try
{
rv = myConnection.getStatement().execute( sql );
}
catch ( SQLException e )
{
myConnection.errorLog( e.toString() );
// No products to return...
return( 0 );
}
// Is this a result set?
if ( rv )
{
try
{
ResultSet rs = myConnection.getStatement().getResultSet();
// Spin through the results and add them to the list...
while ( rs.next() )
{
ProductRecord er = new ProductRecord( rs );
// Add to list...
if ( er.prod_id != -1 )
{
myList.addItem( er.desc_text );
// Add to row mapper...
rowMap.insertElementAt( er, rows );
// Increment row counter...
rows++;
}
}
}
catch ( SQLException e )
{
// Indicate an error!
rows = -1;
}
}
// We're done!
return( rows );
}
The interesting twist here is that each product row is stored in another class called ProductRecord. This class has a corresponding instance variable for each column in the employee table. The class is smart and knows how to read a row out of a JDBC ResultSet object.
As the results are returned by the SQL statement, new ProductRecords are created. These records are stored in a Vector for later use.
At the end, the number of rows that were retrieved are returned and added to the pick list. If there was an error, a -1 is returned.
The reason each record is stored is for easy access. When the user selects the product he or she wants from the list, you simply ask the pick list to produce a copy of the record that it already retrieved. This is done in the getRecord() method:
//****************************************************************************
//* getRecord & nbsp; *
//****************************************************************************
public ProductRecord
getRecord( int where )
{
return( ( ProductRecord )rowMap.elementAt( where ) );
}
The pick list returns the index of the selected item. This class uses a neat trick to keep track of what row is where in the List. A Vector is created called rowMap. As a row of data is retrieved from the database and placed into the pick list's List, it is also stored in the Vector object at the same index level.
Later, when you need a ProductRecord from the pick list, instead of rereading the data from the database, you simply retrieve the row from the Vector. This is done in the preceding getRecord() method. This trick has been used in several applications in this book.
The ProductPickList object is created and displayed in the main program when the user presses the Choose button. Listing 19.4 shows how it is done.
Listing 19.4. The action() and chooseProduct() methods.
//****************************************************************************
//* action &nbs p; *
//****************************************************************************
public boolean
action( Event event, Object arg )
{
if ( event.target == getUIPanel() )
{
switch ( ( ( Integer )arg ).intValue() )
{
case JifMessage.chOOSE:
if ( getDBRecord().didDataChange() )
{
chgDlg = new ResponseDialog( this,
"Data Change",
"The record has changed.\n" +
"Do you wish to save your changes?",
"Yes,No,Cancel" );
chgDlg.show();
}
else
chooseProduct();
return( true );
}
}
// Handle picklist events...
if ( event.target instanceof ProductPickList )
{
int rv = ( ( Integer )arg ).intValue();
ProductPickList epl = ( ProductPickList )event.target;
if ( rv != -1 )
{
// Disable save on choose...
getUIPanel().saveButton.disable();
// Display it on the screen...
setDBRecord( ( DBRecord )epl.getRecord( rv ) );
getUIPanel().moveToScreen();
}
// Kill the dialog box...
epl.hide();
epl.dispose();
// Reset the focus...
getUIPanel().requestFocus();
// We handled it...
return( true );
}
// Not handled...
return( super.action( event, arg ) );
}
//****************************************************************************
//* chooseProduct &nb sp; *
//****************************************************************************
public void
chooseProduct()
{
startWait();
ProductPickList epl = new ProductPickList( this, getConnector() );
epl.center( true );
epl.show();
endWait();
}
When the Choose button is clicked, a JifMessage.chOOSE is sent to the parent. This is received in the action() event handler method. At this point, you need to see whether any changes have been made to the currently displayed record. If so, the user is asked whether he or she wants to save them.
If there are no changes to save, the method chooseProduct() is called. This method creates and displays a ProductPickList object.
When the user selects a pick list item or closes the pick list window, it generates an ACTION_EVENT event. This event is captured and appropriate action is taken.
If the pick list returns a -1 value, you know the user canceled the selection. Otherwise, the value returned is the selected row number. The ProductRecord is then retrieved at that row, made the current record, and the user interface displays it.
Finally, a little cleanup is in order. hide()
and dispose() of the pick
list window, and then reset the focus back to the window.
| Note |
The ProductPickList and other database classes are reused in several other applications. They have been placed in their own package along with other shared code. This package is called jif.common. It contains all the common classes between all the applications. This and all of the other source code is on the CD-ROM that accompanies this book. |
This application communicates with the database through the use of a ProductRecord object. This DBRecord derivation knows how to create, read, update, and delete records from the News table. The following are the instance variables of this class:
//****************************************************************************
//* Constants & nbsp; *
//****************************************************************************
public final static String TABLE_NAME = "prod";
//****************************************************************************
//* Members &nb sp; *
//****************************************************************************
// A variable for each table column...
public int prod_id = -1;
public String desc_text = "";
public int qty_on_hand = 0;
public int qty_on_order = 0;
public Date last_rcv_date = null;
public String comment_text = "";
As you can see, each column is represented by an instance variable.
| Note |
The ProductRecord and other database classes are reused in several other applications. They have been placed in their own package along with other shared code. This package is called jif.common. It contains all the common classes between all the applications. This and all of the other source code is on the CD-ROM that accompanies this book. |
This simple application presented no unusual programming considerations. It is very similar to the Employee Files application. Both have pick lists to select the current row. The rows can be manipulated by the application in a normal fashion.
To recap, this application introduced the following Java intranet programming topics:
This chapter introduced you to the seventh sample application in the intranet application suite-Product Maintenance. This program is responsible for creating and maintaining the product table, and it will be useful for some of the employees. It enables them to view the current product line and stocking levels. It also can be used by telemarketers or sales persons when taking orders.
In Chapter 20, you are introduced to an application that enables you to track problems with your product line.