In , "Employee Files," you set out to create an app that can create, update, and delete the employee files. In this chapter, you design and implement an app that takes this concept one step further. It not only extends the use of the infamous employee table, but it also allows the users of your intranet to make changes to their company benefits. This chapter follows the same format as the last chapter. This chapter covers the following topics in regard to the Benefits Maintenance app:
This four step format is used throughout all of the sample app chapters. Hopefully it provides you with valuable insight and ideas for creating your own intranet apps.
This app, not unlike the Employee Files, is semi-modeless. The user can flow through the program and only some options are available, depending on the state of the current row. The app prompts the user to store any unsaved changes he has made. This is done before any actions such as New or Choose are processed. Screenshot is the proposed user interface for the Benefits Maintenance program. Screenshot The benefits maintenance user interface. This app requires a parent row to exist in the employee table. This forces you to make the user choose an employee to work with. After choosing that employee, this app allows the user to manipulate four benefits-related data items:
This program also reuses the employee pick list. This pick list is stored in the jif.common package so that it can be reused easily.
This app is responsible for manipulating employee benefit rows. These rows should be stored in a single table. Because not all employees have benefits to track, this information belongs in its own table. The table used in this sample app is called the employee benefits table. The information stored in the employee benefits table corresponds to the information that is to be edited, as described earlier. Table 14.1 shows the columns needed to store in the benefits table.
Table 14.1. The employee benefits table layout.| Description | Column Name | Type | Can Be Null? | Default |
| Employee ID | emp_id | number(5) | N | None |
| Number of Exemptions | exemptions_nbr | number(2) | N | 0 |
| Married Indicator | married_ind | char(1) | N | 'N' |
| Plan Participant Indicator | plan_part_ind | char(1) | N | 'N' |
| Payroll Deduction Percentage | payroll_ded_pct | number(3) | N | 0 |
Screenshot shows the entity relationship diagram for the database as it stands in this chapter. As you get deeper into the sample apps, you see the entity relationship diagram grow to encompass all the tables. Screenshot : The entity relationship diagram including the benefits table
| Note |
Entity relationship diagrams are discussed in . |
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. This table is going to be a child of the employee table, which means that no rows can exist in this table unless there is a corresponding row in the employee table. Enforcing the parent-child relationship is called referential integrity. It ensures the integrity of the references in the database. Referential integrity is most often achieved through the use of foreign keys. A foreign key is a link from a child table back to a parent table. This link allows the database to impose restrictions on many database actions, such as the following:
| Tip |
There is one exception to the first foreign key restriction. Some databases allow you to perform what is called a delete cascade. This means that the deletion of a parent row cascades down and deletes all child rows. This can be very useful in complex database structures where tens or hundreds of child rows exist. However, with power comes danger and responsibility. You can do some serious harm to the database if this is done unwittingly. Be careful how you use the delete cascade |
Listing 14.1 shows the SQL commands to create the employee benefits table.
Listing 14.1. The employee benefits table creation SQL.
/* Create the table */
create table emp_benft_t
(
emp_id number( 5 ) not null,
exemptions_nbr number( 2 ) default 0 not null,
married_ind char( 1 ) default 'N' not null,
plan_part_ind char( 1 ) default 'N' not null,
payroll_ded_pct number( 3 ) default 0 not null
);
/* Create a primary key */
alter table emp_benft_t
add
(
primary key
(
emp_id
)
);
/* Create a foreign key */
alter table emp_benft_t
add
(
foreign key
(
emp_id
)
references emp_t
);
/* Grant access for the table to the user role */
grant select,insert,delete,update on emp_benft_t to ia_user_r ;
/* Drop any existing public synonym */
drop public synonym emp_benft ;
/* Create a public synonym for our table */
create public synonym emp_benft for emp_benft_t ;
| Note |
The SQL in Listing 14.1 is quite generic, but it might not work on every database. This particular SQL has been tested with Oracle. |
The first SQL clause creates the table emp_benft_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. The third SQL clause creates the foreign key. The foreign key in this table is the emp_id column. This column points back to, or references, the emp_id column in the emp_t table. Lastly, the public synonym emp_benft is created for the table emp_benft_t. After you create this table, you are ready to build the app.
| Caution |
You must create the emp_t (Employee) table before you can create the emp_benft_t (Employee Benefits) table. Otherwise, the emp_benft_t SQL fails! |
The rest of this chapter discusses the implementation of the Benefits Maintenance program. The first feature discussed is the user interface and how it is created. Secondly, the database access used in the program is discussed. Finally, some of the coding considerations that come up during the app construction are discussed. Each sample app in this tutorial uses a different approach to developing the user interface. This variety shows you 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 app is presented in a manner that is achieved through the use of a GridBagLayout. This is exactly the same approach that was taken in the Employee Files app. The difference here is that a Label variable for each label is not created. Listing 14.2 shows the user interface construction code for the Employee Benefits program.
Listing 14.2. The Employee Benefits interface construction source code.
//****************************************************************************
//* Members &nb sp; *
//****************************************************************************
JifTextField emp_id;
JifTextField full_name;
JifTextField exemptions_nbr;
JifCheckbox married_ind;
JifCheckbox plan_part_ind;
JifTextField payroll_ded_pct;
//****************************************************************************
//* Constructor ; *
//****************************************************************************
public
BenefitsUI( 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, 7 );
System.arraycopy( rc14_0, 0, gbl.columnWeights, 0, 17 );
System.arraycopy( rc14_0, 0, gbl.rowWeights, 0, 7 );
setLayout( gbl );
// Do the labels...
addWithConstraints( new Label( "Employee ID:", Label.RIGHT ),
"anchor=east;x=0;y=0" );
addWithConstraints( new Label( "Name:", Label.RIGHT ),
"anchor=east;x=0;y=1" );
addWithConstraints( new Label( "Exemptions:", Label.RIGHT ),
"anchor=east;x=0;y=3" );
addWithConstraints( new Label( "Married:", Label.RIGHT ),
"anchor=east;x=0;y=4" );
addWithConstraints( new Label( "Plan Participant:", Label.RIGHT ),
"anchor=east;x=0;y=5" );
addWithConstraints( new Label( "Payroll Deduction:", Label.RIGHT ),
"anchor=east;x=0;y=6" );
// Add some fields...
emp_id = new JifTextField( "", "emp_id", true );
emp_id.disable();
addWithConstraints( emp_id, "x=1;y=0;width=5;fill=horizontal" );
full_name = new JifTextField();
full_name.disable();
addWithConstraints( full_name, "x=1;y=1;width=13;fill=horizontal" );
exemptions_nbr = new JifTextField( "", "exemptions_nbr" );
addWithConstraints( exemptions_nbr, "x=1;y=3;width=7;fill=horizontal" );
married_ind = new JifCheckbox( "married_ind" );
addWithConstraints( married_ind, "x=1;y=4" );
plan_part_ind = new JifCheckbox( "plan_part_ind" );
addWithConstraints( plan_part_ind, "x=1;y=5" );
payroll_ded_pct = new JifTextField( "", "payroll_ded_pct" );
addWithConstraints( payroll_ded_pct, "x=1;y=6;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( deleteButton, "x=15;y=4;width=2;fill=horizontal" );
addWithConstraints( chooseButton, "x=15;y=6;width=2;fill=horizontal" );
// Tell which are numeric...
emp_id.setNumeric( true );
exemptions_nbr.setNumeric( true );
payroll_ded_pct.setNumeric( true );
// Set the focus to the first field...
setFocus( exemptions_nbr );
}
A notable item about this user interface (and other GridBagLayout jiflets in this tutorial) is that the grid settings are hard-coded. A permanent 12 row by 17 column grid is used for the user interface. Components are placed within the grid only after the grid is set. Also of note is the fact that you disable the employee ID and name JifTextField components:
emp_id.disable();
full_name.disable();
This forces them into a read-only mode. They accept no input and cannot be changed. In addition, their background color is shaded to indicate the disablement. You disable these fields because they are references from the parent row. This parent row is chosen by using the Choose button and the employee pick list.
The EmployeePickList class is developed for the Employee Files app in . However, it can be reused without modification in this app as well. The EmployeePickList object is created and displayed in the main program. When the user presses the Choose button-which is one of the JifMessages (JifMessage.chOOSE)-an ACTION_EVENT event is generated and sent to the parent of the panel. After it is received, you need to open up the employee pick list. Before you can switch employees though, you must store any changes the user has made to the current row. The following code snippet is from the action() method of the Benefits program:
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 );
}
}
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. Here's what this looks like:
// 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();
// Enable delete...
getUIPanel().deleteButton.enable();
// Display it on the screen...
EmployeeRecord er = epl.getRecord( rv );
// Get a benefits record...
BenefitsRecord br = getBenefitsRow( er );
// Set it in there...
setDBRecord( ( DBRecord )br );
getUIPanel().moveToScreen();
}
// Kill the dialog box...
epl.hide();
epl.dispose();
// Reset the focus...
getUIPanel().requestFocus();
// We handled it...
return( true );
}
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 row, 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.
Each SimpleDBUI derived class has a moveToScreen() method that moves the data from the instance variables to the screen. The BenefitsUI class is no different. However, there are characteristics of this user interface that require special programming:
Let's examine how you accomplish each of these coding tasks.
An indicator column is one that typically holds a Boolean value. Usually, this is represented by a Y or an N for yes or no, respectively. However, to make it easy for the user to interact with this format, a checkbox can be used.
The checkbox is a binary representation as well. It can be checked or not checked. You extend this use to say that if the checkbox is checked, it is a Y, or yes. If it is not checked, this is an N, or no.
So setting the checkbox to the right value is as simple as checking the value of the indicator. The code for checking the marriage indicator is as follows:
if ( er.married_ind != null )
married_ind.setState( er.married_ind.equalsIgnoreCase( "Y" ) );
else
married_ind.setState( false );
If the er.married_ind variable is not null, you set the state of the checkbox to checked if the married indicator is equal to Y. Otherwise, you set it to N.
You must distinguish this null value from N because a NullPointerException is thrown if it is null, and you try to compare its value with Y.
The second interesting coding technique used in this app is displaying information from a second database table.
As you know, your employee benefits table is a child table of the employee table. This means that no real employee information is stored in the benefits table. Because you want to display the name of the employee you're working with, you need to retrieve that employee's name from the employee table.
You can easily retrieve the employee name using the CodeLookerUpper class. This handy class accepts the following information in its constructor:
Armed with this information, the CodeLookerUpper concatenates it to build an SQL select statement. The select statement ends up like this:
select <string columns> from <table name> where <key column> = ?
The bracketed items are filled in with information from the constructor. The question mark (?) is filled in when the SQL is actually used. The CodeLookerUpper contains a single method called lookupCode(). This is where the SQL statement is completed and executed.
Therefore, the code to initialize a CodeLookerUpper is as follows:
CodeLookerUpper clu = new CodeLookerUpper( getJiflet().getConnector(),
"emp", "emp_id", "first_name || ' ' || last_name" );
You initialize it with the connector from your current jiflet, the employee table's synonym emp, the emp table's primary key emp_id, and the two columns you need to have concatenated at the server.
Then you ask the object to retrieve the data for you:
full_name.setText( clu.lookupCode( er.emp_id ) );
You pass the lookupCode() method the employee ID from your current EmployeeRecord. The result is moved directly into the full_name JifTextField.
| Caution |
The CodeLookerUpper expects that the first column returned by a query is a string column. If it is not, an SQLException is thrown. You can return multiple columns as you have done here. However, they must be concatenated at the database level. |
The Employee Benefits program communicates with the database through the use of an EmployeeBenefitsRecord object. This DBRecord derivation knows how to create, read, update, and delete rows from the employee benefits table. The following are the instance variables of this class:
//****************************************************************************
//* Constants & nbsp; *
//****************************************************************************
public final static String TABLE_NAME = "emp_benft";
//****************************************************************************
//* Members &nb sp; *
//****************************************************************************
// A variable for each table column...
public int emp_id = -1;
public int exemptions_nbr = 0;
public String married_ind = "N";
public String plan_part_ind = "N";
public int payroll_ded_pct = 0;
| Note |
The EmployeeBenefitsRecord and other database classes are reused in several other apps. 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 apps. |
Because the pick list returns an EmployeeRecord, you need to retrieve the BenefitsRecord if one exists for this employee. This is done like so:
//****************************************************************************
//* getBenefitsRow &n bsp; *
//****************************************************************************
public BenefitsRecord
getBenefitsRow( EmployeeRecord er )
{
BenefitsRecord br = new BenefitsRecord();
String sql = "";
sql = "select * from emp_benft where " +
"emp_id = " + Integer.toString( er.emp_id );
try
{
if ( getConnector().getStatement().execute( sql ) )
{
ResultSet rs = getConnector().getStatement().getResultSet();
if ( rs.next() )
br.parseResultSet( rs );
else
{
br.clear();
br.emp_id = er.emp_id;
}
}
}
catch ( SQLException e )
{
errorLog( sql + " generated: " + e.toString() );
return( null );
}
// Return the record...
return( br );
}
Given an EmployeeRecord, you search the table for an associated row in the employee benefits table. If it is found, you allow the BenefitsRecord to parse the results. Otherwise, the default values are returned.
This app builds upon your base of Employee Files and adds more functionality. It provides your intranet users with the ability to modify their benefits parameters at will. You again used the stock SimpleDBUI and SimpleDBJiflet classes as a base, allowing you to quickly put together this app. This app introduces the following Java intranet coding topics:
This chapter introduces you to the second sample app in the intranet app suite-the Benefits Maintenance app. This program is responsible for creating, updating, and deleting rows from the employee benefits table, and it is useful for normal employees who want to manage their own benefits.
In , "Conference Room Scheduling," you design and create an app that allows employees to schedule conference rooms for meetings well into the future.