The Sample Application
In this application, a client hits the application server by accessing a servlet. This servlet takes the client's request and returns a simple Microsoft Excel spreadsheet. The servlet retrieves the spreadsheet data from a database backend; I'm using IBM DB2 UDB Version 8.2. (download a trial version here
), but you can use any database you like as long as you have a JDBC driver and you define the data source properly for your particular database.
This DB2 back end provides the spreadsheet with data. Because the servlet creates the spreadsheet dynamically, when the database data changes, the spreadsheet that the servlet creates also changes to reflect the new data.
Setting Up the DB2 Backend
For demonstration purposes, the database contains statistics for some fictional basketball players. More specifically, the database table stores player data, including games played (G), field goals made (FG), free throws made (FT), and total points scored (P). Table 1 shows the columns in this abbreviated form. The Excel spreadsheets produced from the database data will relay these statistics to Web site visitors.
From the DB2 Command Line Processor, issue the following statement, which creates a database named balldb
db2 => create database balldb
Next, connect to the database with your user name and password. In my case, both username and password are db2admin:
db2=> connect to balldb user db2admin using db2admin
Next, create a table named STATS
to house the statistical basketball data:
db2=> create table stats (
firstname varchar(40) not null,
lastname varchar(40) not null,
team varchar(40) not null,
G int not null,
FG int not null,
FT int not null,
P int not null)
For this experiment, you need to populate the STATS
table with some sample data. For demonstration purposes Table 1 shows five fictional players and their associated fictional data:
Table 1. The STATS table with demo data.
||Ft Worth Computer Junkies
||Sunnyvale Flying Gutis
||Fremont Chip Pushers
||Tyson Ambulance Chasers
The following statement inserts the first row into the database.
insert into stats values ('Bicky','Bhogal',
'Ft Worth Computer Junkies',80,584,288,2652)
Go ahead and issue a similar statement for the other rows in the STATS
table, using the appropriate data, until you've entered all the data in Table 1.