Now that you've learned the foundation for creating intranet applications, let's get cooking. This chapter and the next several chapters guide you through some sample intranet applications. These applications are real-world examples. You don't find any animated buttons or scrolling text classes sprinkled in either. This is hardcore corporate database programming, but because you've developed such an excellent set of foundation classes, it isn't difficult at all to create the applications.
This chapter covers the following topics in regard to the Employee Files application:
This four-step format is used throughout the sample application chapters. Hopefully it provides you with valuable insight and ideas for creating your own intranet applications.
This is the first sample application, and it is quite simple. It is the Employee Files. This application enables you to maintain records on the employees in your company. This application is sort of the "big toe" for the rest of the programs, because many of the other programs use information that is stored in the employee table. Programs such as the online phone book or the in/out board use this data.
Figure 13.1 is the proposed user interface for the Employee Files program.
Figure 13.1 : The Employee files user interface.
This application is semi-modeless, which means that it has no operating mode. You don't have to inform the program that you are going to be adding new records or removing records. You can flow through the program, and it determines what is done.
The basic functionality of this application is to create, read, update, and delete employee records. These records are stored in a database. You will learn about database design later in this chapter.
Think of this application as a pointer into the employee table. When the pointer is situated on a record, it is the current record. This current record is displayed to the user and the user can do whatever he chooses with it. The user can also insert records into the table at the end.
The user requires a method of moving this pointer from employee to employee. The best way to present this information to the user is through the use of a pick list.
The pick list is a selection of all the records in the employee table. This selection includes the first and last name of each employee. The user is allowed to select one name from the displayed list. After the selection is made, the chosen record is fully retrieved and displayed.
To select a record, double-click the list item or single-click and then press the OK button.
Figure 13.2 shows the concept of the employee pick list.
Figure 13.2 : The employee pick list.
This pick list is opened when the user presses the Choose button, a standard SimpleDBUI button.
Now that you have a sense of the application design, let's look at the database.
This application is responsible for manipulating employee records. These records are stored in a single table. Currently, there is no need to extend the scope of the employee data to a second table.
The information stored in the employee table is basic information.
Table 13.1 shows the columns needed in the employee table.
| Description | Column Name | Type | ||
| Employee ID | emp_id | number(5) | ||
| First Name | first_name | char(40) | ||
| Middle Name | mid_name | char(40) | ||
| Last Name | last_name | char(40) | ||
| Social Security Number | ssn | char(15) | ||
| Address Line 1 | addr_line_1 | char(80) | ||
| Address Line 2 | addr_line_2 | char(80) | ||
| City | city | char(80) | ||
| State | state | char(80) | ||
| Zip Code | zip_code | char(20) | ||
| Salary | salary | number(7,2) | ||
| Home Phone Number | home_phone_nbr | char(20) | ||
| Work Extension Number | work_ext_nbr | char(20) | ||
| In/Out Indicator | in_out_ind | char(1) |
The entire data model is laid out into an entity relationship diagram or ERD. An ERD represents all the tables in your data model as an entity. The relationship between each entity is then shown. There are many types of relationships between entities: one-to-one, one-to-many, zero-or-more-to-one, zero-or-more-to-many, and so on.
Figure 13.3 shows the entity relationship diagram for the database as it stands in this chapter. As you get deeper into the sample applications, you see the entity relationship diagram grow to encompass all the tables.
Figure 13.3 : The employee entity.
In addition to creating a table, you create a database synonym for the table. This allows everyone to access the table with the same name and not have to worry about the schema in which the table resides.
Before you see the SQL for creating the table, there is a small matter of users and schemas. The data model for the sample applications in this book relies on no particular user or schema. However, the same model is used in the development of the applications.
All of the tables in this book are created by the master user
for the database. Because you use Oracle for developing the database,
this user is system. You
also create an Oracle role and a user for the database. Full access
to all of the tables is granted to the role. The user is granted
access to the role.
| Note |
Although this book uses Oracle as a database, the table definitions are easily converted to other database management systems. After the tables are created in your own DBMS, this application and the others that follow will run just fine. |
| What's a Role? |
A role is like a user group. It can have specific rights granted to it. These rights can be table access or possible system administration capabilities. After a role is created, you can grant users access to the role. When a user is granted access to a role, that user can perform all of the functions granted to the role. Users can also be granted to several roles at one time. Many users can also be granted to a single role. This flexible structure allows complex database security schemes to be implemented without much work. |
The role that you created is called ia_user,
for "intranet application" user. The user that you created
shares the same name, ia_user.
The user is granted to the role. Therefore, all of the granting
that is done in the creation SQL is only to the role.
| Note |
The SQL code to create the user and role is on the CD-ROM in the examples directory for this chapter. It is in a file called user.sql. This file is Oracle-specific, but should work on other databases with little or no modifications. |
Finally, Listing 13.1 is the SQL commands to create the database.
Listing 13.1. The Employee table creation SQL.
/* Create the table */
create table emp_t
(
emp_id number( 5 ) not null,
first_name char( 40 ) not null,
mid_name char( 40 ),
last_name char( 40 ) not null,
ssn char( 15 ),
addr_line_1 char( 80 ),
addr_line_2 char( 80 ),
city char( 80 ),
state char( 80 ),
zip_code char( 20 ),
salary number( 7,2 ) not null,
home_phone_nbr char( 20 ),
work_ext_nbr char( 20 ),
in_out_ind char( 1 ) default 'N' not null
);
/* Create a primary key */
alter table emp_t
add
(
primary key
(
emp_id
)
);
/* Create the synonym */
create public synonym emp for emp_t ;
| Note |
The SQL in Listing 13.1 is quite generic, but it might not work on every database. This particular SQL has been tested with Oracle. |
| Caution |
The code in Listing 13.1 does not work with ODBC. |
The first SQL clause creates the table emp_t. The second clause creates a primary key using the emp_id column. Making this the primary key ensures that the values in the column are unique across all rows. Lastly, the public synonym emp is created for the table emp_t.
After you create the table, you are ready to build the application.
The rest of this chapter discusses the implementation of the Employee Files program. The first feature discussed is the user interface and how it is created. Second, the database access used in the program is discussed. Finally, you learn about any programming pitfalls that came up during the application construction.
Each sample application in this book uses a different approach to developing the user interface. This variety shows the different ways you can do your own interfaces. Hopefully, you get a cross-section of many different styles and can choose the one that suits you the best.
The screen layout for this application is presented in a nice
manner that is achieved through the use of a GridBagLayout.
This ogre of a layout manager is difficult to work with, but when
it is tamed it can provide wonderful layout capabilities.
| Tip |
The JifPanel class provides a method called addWithConstraints() that allows you to specify GridBagLayout constraints in a simple manner and add them with the component to the layout. It is used throughout many of the applications in this chapter. |
Listing 13.2 gives the user interface construction code for the Employee program.
Listing 13.2. Employee files interface construction source code.
//****************************************************************************
//* Members &nb sp; *
//****************************************************************************
Label l_emp_id;
Label l_fn;
Label l_mn;
Label l_ssn;
Label l_ln;
Label l_address;
Label l_city;
Label l_state;
Label l_zc;
Label l_salary;
Label l_home_phone_nbr;
Label l_work_ext_nbr;
Label l_in_out_ind;
JifTextField emp_id;
JifTextField first_name;
JifTextField mid_name;
JifTextField ssn;
JifTextField last_name;
JifTextField addr_line_1;
JifTextField addr_line_2;
JifTextField city;
JifTextField state;
JifTextField zip_code;
JifTextField salary;
JifTextField home_phone_nbr;
JifTextField work_ext_nbr;
JifCheckbox in_out_ind;
//****************************************************************************
//* Constructor ; *
//****************************************************************************
public
EmployeeUI( 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, 14, 14, 14 }; // 12
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[ 12 ];
gbl.columnWeights = new double[ 17 ];
gbl.rowWeights = new double[ 12 ];
System.arraycopy( cw, 0, gbl.columnWidths, 0, 17 );
System.arraycopy( cw, 0, gbl.rowHeights, 0, 12 );
System.arraycopy( rc14_0, 0, gbl.columnWeights, 0, 17 );
System.arraycopy( rc14_0, 0, gbl.rowWeights, 0, 12 );
setLayout( gbl );
l_emp_id = new Label( "Employee ID:", Label.RIGHT );
addWithConstraints( l_emp_id, "anchor=east;x=0;y=0" );
l_ssn = new Label( "SSN:", Label.RIGHT );
addWithConstraints( l_ssn, "anchor=east;x=6;y=0" );
l_fn = new Label( "First Name:", Label.RIGHT );
addWithConstraints( l_fn, "anchor=east;x=0;y=1" );
l_mn = new Label( "Middle Name:", Label.RIGHT );
addWithConstraints( l_mn, "anchor=east;x=0;y=2" );
l_ln = new Label( "Last Name:", Label.RIGHT );
addWithConstraints( l_ln, "anchor=east;x=0;y=3" );
l_address = new Label( "Address:", Label.RIGHT );
addWithConstraints( l_address, "anchor=east;x=0;y=4" );
l_city = new Label( "City:", Label.RIGHT );
addWithConstraints( l_city, "anchor=east;x=0;y=6" );
l_state = new Label( "State:", Label.RIGHT );
addWithConstraints( l_state, "anchor=east;x=0;y=7" );
l_zc = new Label( "Zip Code:", Label.RIGHT );
addWithConstraints( l_zc, "anchor=east;x=6;y=7" );
l_salary = new Label( "Salary:", Label.RIGHT );
addWithConstraints( l_salary, "anchor=east;x=0;y=9" );
l_home_phone_nbr = new Label( "Home Phone:", Label.RIGHT );
addWithConstraints( l_home_phone_nbr, "anchor=east;x=0;y=10" );
l_work_ext_nbr = new Label( "Work Ext:", Label.RIGHT );
addWithConstraints( l_work_ext_nbr, "anchor=east;x=6;y=10" );
l_in_out_ind = new Label( "In/Out:", Label.RIGHT );
addWithConstraints( l_in_out_ind, "anchor=east;x=0;y=12" );
emp_id = new JifTextField( "", "emp_id", true );
addWithConstraints( emp_id, "x=1;y=0;width=5;fill=horizontal" );
ssn = new JifTextField( "", "ssn" );
addWithConstraints( ssn, "x=7;y=0;width=7;fill=horizontal" );
first_name = new JifTextField( "", "first_name" );
addWithConstraints( first_name, "x=1;y=1;width=13;fill=horizontal" );
mid_name = new JifTextField( "", "mid_name" );
addWithConstraints( mid_name, "x=1;y=2;width=13;fill=horizontal" );
last_name = new JifTextField( "", "last_name" );
addWithConstraints( last_name, "x=1;y=3;width=13;fill=horizontal" );
addr_line_1 = new JifTextField( "", "addr_line_1" );
addWithConstraints( addr_line_1, "x=1;y=4;width=13;fill=horizontal" );
addr_line_2 = new JifTextField( "", "addr_line_2" );
addWithConstraints( addr_line_2, "x=1;y=5;width=13;fill=horizontal" );
city = new JifTextField( "", "city" );
addWithConstraints( city, "x=1;y=6;width=13;fill=horizontal" );
state = new JifTextField( "", "state" );
state.setStyle( JifTextField.UPPER );
addWithConstraints( state, "x=1;y=7;width=5;fill=horizontal" );
zip_code = new JifTextField( "", "zip_code" );
zip_code.setStyle( JifTextField.NUMERIC );
addWithConstraints( zip_code, "x=7;y=7;width=7;fill=horizontal" );
salary = new JifTextField( "", "salary" );
addWithConstraints( salary, "x=1;y=9;width=5;fill=horizontal" );
home_phone_nbr = new JifTextField( "", "home_phone_nbr" );
addWithConstraints( home_phone_nbr, "x=1;y=10;width=5;fill=horizontal" );
work_ext_nbr = new JifTextField( "", "work_ext_nbr" );
addWithConstraints( work_ext_nbr, "x=7;y=10;width=7;fill=horizontal" );
in_out_ind = new JifCheckbox( "in_out_ind" );
addWithConstraints( in_out_ind, "x=1;y=12;width=7;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 numeric...
emp_id.setNumeric( true );
salary.setNumeric( true );
// Set the focus to the first field...
setFocus( emp_id );
}
A notable item about this user interface (and other GridBagLayout
jiflets in this book) is that the grid settings are hard-coded.
A permanent 12 row by 17 column grid is used for the user interface.
The components are placed within the grid only after the grid
is set.
| Tip |
In this user interface, the Labels all have instance variables associated with them. Unless you plan to change the text of a label during the program life-cycle, this is completely unnecessary. It is done here for clarity only. |
Another class has been developed for this application. This is the EmployeePickList class. This class derives from the PickList class of jif.awt package (as discussed in Chapter 11, "User Interface Classes") and presents the user with a selection of employees. When one is chosen, the object stores the selection and waits for someone to ask who has been chosen. Let's take a look at some of the source code.
//****************************************************************************
//* EmployeePickList *
//****************************************************************************
public class
EmployeePickList
extends PickList
As stated earlier, you extend the PickList class. In order to do this, you must supply an init() method. The following is this method:
//****************************************************************************
//* init *
//****************************************************************************
public void
init()
{
int rows = retrieveEmployees();
if ( rows > 0 && getParent() instanceof Jiflet )
( ( Jiflet )getParent() ).verboseLog( "Retrieved " +
Integer.toString( rows ) + " Employees" );
}
This method calls the retrieveEmployees() method. Also, if this pick list is used with a jiflet and verbose mode is turned on, the number of employees that is retrieved is written to the log file.
The retrieveEmployees() method shown in Listing 13.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 13.3. The retrieveEmployees() method.
//****************************************************************************
//* retrieveEmployees ; *
//****************************************************************************
int
retrieveEmployees()
{
String sql;
boolean rv = false;
int rows = 0;
sql = "select * from emp order by last_name, first_name";
try
{
rv = myConnection.getStatement().execute( sql );
}
catch ( SQLException e )
{
System.out.println( "Error during retrieve: " + e.toString() );
// No employees 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() )
{
EmployeeRecord er = new EmployeeRecord( rs );
// Add to list...
if ( er.emp_id != -1 )
{
myList.addItem( er.nice_name );
// 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 employee row is stored in another class called EmployeeRecord. 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.
So as you walk through the results returned by the SQL statement, you create a new EmployeeRecord. You store these records in a Vector for later use.
At the end, you return the number of rows that are retrieved and added to the pick list. If there is an error, you return -1.
The reason you store each record is for easy access. When the
user selects the employee from the list, you ask the pick list
to give you a copy of the record that it already retrieved. This
is done in the getRecord()
method:
//****************************************************************************
//* getRecord & nbsp; *
//****************************************************************************
public EmployeeRecord
getRecord( int where )
{
return( ( EmployeeRecord )rowMap.elementAt( where ) );
}
The pick list returns the index of the item selected. This class uses a neat trick to keep track of what row is where in the List. A Vector called rowMap is created. 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 an EmployeeRecord from the pick list, instead of rereading the data from the database, you retrieve the row from the Vector. This is done in the getRecord() method. You see this used quite a bit in various programs.
The EmployeePickList object is created and displayed in the main program when the user presses the Choose button. Listing 13.4 shows how it is done.
Listing 13.4. The action() and chooseEmployee() methods.
//****************************************************************************
//* action &nbs p; *
//****************************************************************************
/**
* My child panel may start up picklists. It is my responsibility to
* handle them. That is done here.
*/
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
chooseEmployee();
return( true );
}
}
// Handle picklist events...
if ( event.target instanceof EmployeePickList )
{
int rv = ( ( Integer )arg ).intValue();
EmployeePickList epl = ( EmployeePickList )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 ) );
}
//****************************************************************************
//* chooseEmployee &n bsp; *
//****************************************************************************
public void
chooseEmployee()
{
startWait();
EmployeePickList epl = new EmployeePickList( this, getConnector() );
epl.center( true );
epl.show();
endWait();
}
When the Choose button is clicked, a JifMessage is sent to the parent. This is received in the action() event handler method. At this point, you need to check whether any changes have been made to the currently displayed record. If so, you ask the user whether he wants to save them.
If there are no changes to save, the method chooseEmployee() is called. This method creates and displays an EmployeePickList object.
When the user selects a pick list item or closes the pick list window, it generates an ACTION_EVENT event. You capture this event and act accordingly.
If the pick list returns a -1 value, you know that the user has canceled his selection. Otherwise, the value returned is the row number that is selected. You retrieve the EmployeeRecord at that row, make it the current record, and request that the user interface display it.
Finally, a little cleanup is in order. You hide() and dispose() of the pick list window and then reset the focus back to your window.
The Employee program communicates with the database through the use of an EmployeeRecord object. This DBRecord derivation knows how to create, read, update, and delete records from the employee table. The following are the instance variables of this class:
//****************************************************************************
//* Constants & nbsp; *
//****************************************************************************
public final static String TABLE_NAME = "emp";
//****************************************************************************
//* Members &nb sp; *
//****************************************************************************
// A variable for each table column...
public int emp_id = -1;
public String first_name = "";
public String mid_name = "";
public String last_name = "";
public String ssn = "";
public String addr_line_1 = "";
public String addr_line_2 = "";
public String city = "";
public String state = "";
public String zip_code = "";
public int salary = 0;
public String home_phone_nbr = "";
public String work_ext_nbr = "";
public String in_out_ind = "N";
// A computed column...
public String nice_name;
| Note |
The EmployeeRecord, EmployeePickList, and other database classes are reused in several other applications. They are 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 application is quite routine. Because you use the stock SimpleDBUI and SimpleDBJiflet classes as a base, not much extra work is required.
The one interesting programming consideration that comes up during the creation of this application is the caching of data in the pick list. This class purposely stores a copy of each row retrieved simply for the convenience of the calling program. When the user selects an item from the pick list, it does not have to be re-retrieved from the database because it has been cached.
This chapter introduces you to the first sample application in the intranet application suite-the Employee Files. This program is responsible for creating, updating, and deleting rows from the employee table, which is useful for human resources employees. Also, it can be modified for the employees to update their own information.
In Chapter 14, "Human Resources: Benefits Maintenance," you design and create an Employee Benefits Maintenance application. This program allows employees to change the parameters of their company-provided benefits such as 401K contributions and even W-4 exemptions.