CMSC 10200 Lab — Spring 2006
Lab Assignment 7
Lab is due Sunday at 11:59pm. There is Extra Credit.

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 ResultTable 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

  1. Throrough testing of the java classes you write, and the JSP and HTML pages you use for the client.
  2. Clear documentation of your public code through Javadoc.
  3. Simplicity of your JSP response page to the users request. This means minimal java code, nothing more than calling constructors and methods.
  4. Quality of your database webpage for allowing clients to select data from a baseball database.
  5. 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

  1. Open Project.
  2. Enter Project -> Properties
  3. Select Java Build Path
  4. Select Add External Jars... in the Java Build Path Wizard
  5. Browse your directories to find your new Driver mysql-connector-java-3.1.12-bin.jar.
  6. 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.

  1. Move to
          /stage/tomcat-cmsc/username/webapps/WEB-INF/classes
       
  2. Download the package com.tgz, and unarchive
    	tar xfz com.tgz
       
  3. 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 SELECT queries. The commandline is

	mysql cs102 -h danryan -u cs102 -P 34463 -p
and 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) Hopefully, you were prevented from performing the last command!

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, playerID. This uniquely identifies each player and will be used next week to cross-reference the Master table with all other tables.

SQL SELECT Command

You will be using a more robust SELECT command than last week:

     SELECT columns FROM Master
     WHERE where_conditions
     LIMIT offset, count
where You can read more about these commands at Intro to SQL. Next week, we will add a richer set of commands for 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 ResultTable will be placed in a new package database, so you will have to make the following modifications:

  1. Change the package
    	package database;
        
  2. Add import
    	import sql;
        
This package will also contain the Connect class and the class you will write today, ResultTableJSP.

Download the new sql package, and the database package and unarchive it by

	tar xfz sql.tgz
	tar xfz database.tgz
These will produce directories called sql and database. The first contains the new java classes to implement the more robust SELECT SQL command for this week's lab. Your ResultTable does not depend upon the actual details of SelectQuery, so it will work without modification. Next week, we will change the SelectQuery again to add new details for a richer SQL SELECT command. ResultTable should not depend on this, and will remain unchanged. The second directory contains a new Connect class, 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. Import your html package, the new sql package, and the new database. Import your ResultTable class into the database 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 SelectQuery. 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 compiled .class files in their package directories under Tomcat. Move your three packages to

	/stage/tomcat-cmsc/username/webapps/WEB-INF/classes
and 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 <FORM>, and these must be converted into an object of the java class SelectQuery, which ResultTable can use to request data from MySQL. ResultTable returns a Table object, 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 ResultTableJSP. This class is very similar to PizzaJSP from lab5, it turns raw parameter information collected from the HTML <FORM> on the webpage into a SelectQuery which can be passed on to ResultTable. It should also return an HTML table that your JSP can use to display the response.

Requirements for your JSP response

  1. 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.
  2. The first row of your HTML table must be a row of headers, containing the name of each column requested by the client.
  3. 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.
  4. You must NEVER display the playerID column. This is for internal use only. If the client requests all columns, this means all but playerID.
  5. The client must be able to modify the search by string matching on the following columns
    last first nickname college
    Additionally, 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.
  6. 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 Table class. Here is a simple example based on the query
    	SELECT last,first,nickname FROM Master 
    	WHERE nickname LIKE '%big%' AND last LIKE 't%';
        
    lastfirstnicknamedebut
    ThomasFrankBig Hurt8/2/1990
    ThompsonSamBig Sam1885-07-02
    TippleDanRusty,Big Dan9/18/1915
    TaylorAaronBig Country9/9/2002
    Note that Aaron Taylor is out of order, but debuted in 2002. He really does appear after the other players in the database.

Extra Credit

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/year
where 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.

Handin

Your lab is due Sunday at 11:59pm. See handin.


Kenneth Harris