pro duct \prod'ukt\ n:something produced by natural, human, or mechanical effort
Welcome to the penultimate sample intranet app. This, the Product Maintenance app, isn't as simple as the last few have been. It is more in line with your first app, 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 app 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 app-Customer Support Maintenance. This app 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 app:
This four-step format just outlined is used throughout all of the sample app chapters, and it should provide you with valuable insight and ideas for creating your own intranet apps.
This app 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 apps in this tutorial. 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 apps 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 apps, they will be able to better use their combined knowledge for support calls.
This app is quite similar to the Employee Files app, and it has many of the same qualities. At the core, it's a database row manipulation app. 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. Screenshot : The Product maintenance user interface. This app 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 app 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 app 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. Screenshot represents the concept of the product pick list. Screenshot : The product pick list. This pick list should be opened in response to the user pressing the Choose button, a standard SimpleDBUI button.
This app is responsible for manipulating news rows. These rows should be stored in a single table. The table used in this sample app 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.
Table 19.1. The product table layout.| Description | Column Name | Type | Can Be Null?
|
Default
|
| Product ID | prod_id | number( 5 ) | No
|
None
|
| Description | desc_text | char( 80 ) | Yes
|
None
|
| Quantity On Hand | qty_on_hand | number( 10 ) | No
|
None
|
| Quantity On Order | qty_on_order | number( 10 ) | No
|
None
|
| Last Received Date | last_rcv_date | date | Yes
|
Null
|
| Comment | comment_text | char( 80 ) | Yes
|
None
|
Screenshot shows the entity relationship diagram for the database as it stands in this chapter. In , "Customer Support Maintenance," you see the final entity relationship diagram. It will represent all of the tables that have developed for the apps in this tutorial. Screenshot : The entity relationship diagram, including the new product table.
| Note |
Entity relationship diagrams are discussed in , "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 coding pitfalls that came up during the app 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 app. 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 tutorial) 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 app, the ProductPickList class. This class derives from the PickList class of the jif.awt package (as discussed in , "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 apps in this tutorial.
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 apps. 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 apps. This and all of the other source code is on the DVD that accompanies this tutorial. |
This app 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 apps. 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 apps. This and all of the other source code is on the DVD that accompanies this tutorial. |
This simple app presented no unusual coding considerations. It is very similar to the Employee Files app. Both have pick lists to select the current row. The rows can be manipulated by the app in a normal fashion. To recap, this app introduced the following Java intranet coding topics:
This chapter introduced you to the seventh sample app in the intranet app 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 , you are introduced to an app that enables you to track problems with your product line.