A Baseball Database Server: PartI, the Master list
In the next two labs you will build a database server for a baseball
database. This database has a wide range of baseball statistics for
almost 17,000 players, spread-out over a couple dozen different tables.
This week you will write a database server which accesses only one
table, the Master table, which contains personal data on
each of the players and managers ever involved in the game.
The challenge for you is to turn the selection of a client
through your webpage into a SQL
SELECT query that you
can pass to a MySQL database through your
class you wrote last lab. This data will be displayed in an HTML
table for the client of your webpage.
Your lab will be judged on several features
- Throrough testing of the java classes you write, and the JSP and HTML pages you use for the client.
- Clear documentation of your public code through Javadoc.
- Simplicity of your JSP response page to the users request. This means minimal java code, nothing more than calling constructors and methods.
- Quality of your database webpage for allowing clients to select data from a baseball database.
- Quality of the response page for the client's requested data.
Set-up: New MySQL Driver installation
You will need to download and install a new MySQL Driver Class, version 3.1.12. This replaces the Driver (version 3.0.06) you downloaded last week. Remove the old Driver. You will use the new Driver to communicate with the MySQL database. This Driver is used in Eclipse by
- Open Project.
- Enter Project -> Properties
- Select Java Build Path
- Select Add External Jars... in the Java Build Path Wizard
- Browse your directories to find your new Driver
- This will add the Driver library jar to the Project directory
You will be using Tomcat, so you need to put the Driver class in a location Tomcat can access as well.
- Move to
- Download the package com.tgz, and unarchive
tar xfz com.tgz
- This will create a directory
com, which you will have to reset permissions
chmod -R 755 com
New MySQL Database Server
Everyone will access the same database on MySQL. This database is on
a MySQL server running on a local machine danryan, not
dbserver, and you will access it as an anonymous user,
cs102, with access limited only to making
queries. The commandline is
mysql cs102 -h danryan -u cs102 -P 34463 -pand the password is cs102 (easy to remember!!). Try the following commands (there is no need to enter
use cs102, since the database to use was passed on the commandline)
SHOW COLUMNS FROM Master
SELECT last,first,debut,final FROM Master WHERE debut LIKE '9/%/1965';(the "%" is a wild card that matches all combinations of characters, here any day in September of 1965 where a player made his debut.)
DROP TABLE Master;
You can use the
SHOW COLUMNS FROM Master to see all the
column data and data types from the Master table. It should
be self-explanatory, except for the first column,
This uniquely identifies each player and will be used next week
to cross-reference the Master table with all other
You will be using a more robust
SELECT command than last
SELECT columns FROM Master WHERE where_conditions LIMIT offset, countwhere
- columns is a list of column names or the wild card asterisk ('*')
- where_conditions are a joining (using the SQL
AND) of string matching blocks
column LIKE 'regex'where regex is a string with possible wild card characters '%', which match any sequence of characters. Try the following
SELECT last,first,nickname,final FROM Master WHERE final LIKE '9/22/%' AND last LIKE 'W%';This matches any player whose final game was September 22 of any year, and whose last name begins with 'W'. ("No Neck" was a South-sider. Do you think "The People's Cherce" may be a typo?)
- offset and count are non-negative
integers. offset refers to the first record to be
displayed, offset from 0; count refers to the
maximum number of records to be displayed. Try the following
SELECT last, first, college FROM Master LIMIT 10000, 25;The offset is optional, in which case it is implicitly assumed to be 0:
SELECT last, first, college FROM Master LIMIT 25;
SELECT, including more where_conditions appropriate for numeric data and
ORDER BY, to display the data in some order different from the order the database is stored (which is mainly alphabetic by last and first names in Master, except for players who started after 2002.)
Java Class code
You will supply your
html package and
ResultTable class code for the next two labs. These
classes must be absolutely correct, so you will need to fix errors
before you can begin this week's lab. The
will be placed in a new package
database, so you
will have to make the following modifications:
- Change the package
- Add import
Connectclass and the class you will write today,
tar xfz sql.tgz tar xfz database.tgzThese will produce directories called
database. The first contains the new java classes to implement the more robust
SELECTSQL command for this week's lab. Your
ResultTabledoes not depend upon the actual details of
SelectQuery, so it will work without modification. Next week, we will change the
SelectQueryagain to add new details for a richer SQL
ResultTableshould not depend on this, and will remain unchanged. The second directory contains a new
Connectclass, since we will be connecting to a new MySQL database.
Create your lab7 project.
Remember to set your compliance to 5.0, and make sure you
create separate folders for source code and compiled class code.
html package, the
sql package, and the new
ResultTable class into the
package. Before continuing rewrite some tests to make
simple queries to the database in Eclipse.
Your old tests will not work, since you are connecting to a new
database; the old
SelectQuery constructor works, but will return 17,000 records,
unless the query is modified using the additions to
You will need to learn about the new
SelectQuery class code
and try the new type of queries. It is up to you to read the
documentation and learn how to create queries. You need to make
sure everything is working before you try to write new code.
When you have finished writing and testing your code, you can deploy the
.class files in their package directories
under Tomcat. Move your three packages to
/stage/tomcat-cmsc/username/webapps/WEB-INF/classesand make sure the permissions are set correctly
chmod -R 755 classes
Your Baseball Database Server
I have written a skeletal HTML form to give you an idea of the range of queries a client needs to be able make through your database server. The layout is only a suggestion, and you will need to create a nicer client friendly webpage. Your challenge is to turn the client request for data into a SQL query for selecting records from the Master database, and displaying these records in a nice HTML response table.
The HTML page returns parameters from the
and these must be converted into
an object of the java class
ResultTable can use to request data from MySQL.
ResultTable returns a
which is easily converted into an HTML table for display.
The challenge is that your JSP response page must not
contain any java code, except calling constructors and methods
from a java class you will write
This class is very similar to
PizzaJSP from lab5,
it turns raw parameter information collected from the HTML
<FORM> on the webpage into a
which can be passed on to
ResultTable. It should
also return an HTML table that your JSP can use to display the
Requirements for your JSP response
- All records must contain the last name then the first name, as the first two columns. The user specifies the rest of the columns to be displayed. The order of the remaining columns is not important.
- The first row of your HTML table must be a row of headers, containing the name of each column requested by the client.
- All records, this week, are displayed in the order they appear in the database. Most players are alphabetized by last name and first name. Players who began after 2002 are placed at the end of the database, alphabetic, by year of debut. There is an example of this below.
- You must NEVER display the playerID column. This is for internal use only. If the client requests all columns, this means all but playerID.
- The client must be able to modify the search by string matching
on the following columns
last first nickname collegeAdditionally, the client should be able to modify the search based on the bats and throws column of a player. These fields can take one of three values: 'R', 'L' or 'B'. It is not necessary that the user choose to display any of nickname, college, bats or throws to modify the search.
- The HTML table returned must be readable and attractive. It
should probably contain a border to help separate data. This may mean
modifying your HTML
Tableclass. Here is a simple example based on the query
SELECT last,first,nickname FROM Master WHERE nickname LIKE '%big%' AND last LIKE 't%';
last first nickname debut Thomas Frank Big Hurt 8/2/1990 Thompson Sam Big Sam 1885-07-02 Tipple Dan Rusty,Big Dan 9/18/1915 Taylor Aaron Big Country 9/9/2002
Allow the user to modify the query by selecting from the debut and final game columns. These fields are strings of the form
month/day/yearwhere month is a number from 1 to 12 of one or two digits, day consists of one or two digits and year consists of four digits; for example, '4/1/2005' or '9/22/1965'. Think carefully how you will present choices to a client: they should be able to choose any of these three values to search on and should not have to know about the correct format of a date.
If you were paying attention you will notice that Big Sam Thompson debuted in '1885-07-02'. The Baseball Archive is a conglomeration of several databases, and there are inconsistencies. In this case, it only effects the Old Timers like Big Sam, so you can ignore these inconsistencies and search only using the format I have given above.
Your lab is due Sunday at 11:59pm. See handin.