CISC474, Homework H08, Spring 2007
JDBC and MySQL

Introduction

In this assignment, you'll redo the product servlet you did in H07, changing the model to use a MySQL database

I've provided code for you for a new version of the Beer MVC Servlet from HFSJ, that builds on the H07 version by changing the Model to one that uses a MySQL database. We are using some (though not all) of the MySQL "best practices". Here's what we are doing that is right:

• Using a PreparedStatement properly to avoid SQL Injection
• Keeping all the SQL stuff isolated inside the Model, for the most part, so that if we were to swap out the SQL stuff for some other data access method, the rest of the Servlet is affected as little as possible.

Here are a few things we should be doing that we are not doing yet. We'll cover these in future homeworks if time permits.

• We should be using parameters from the deployment descriptor (web.xml) to set the JDBC URL, username, and password, instead of hard coding those inside the controller servlet.
• We should be using Connection Pooling to avoid performance bottlenecks caused by opening and closing connections to the database. (By the way, a good Wiki Tutorial on Connection Pooling would be a great Choice Point opportunity!)

An outline of how H08 is different from H07

• The model class uses a database connection to get the beers (or whatever your product is.
• A small change inside the build.xml file on the <javac> Ant task allows us to see the line numbers when we print a stack trace.

Assignments that must be completed first

• H04 (to understand basics of servlets)
• H05 (to have selected a product)
• H06 (this is an elaboration of work you did in H06)
• H07 (this is an elaboration of work you did in H07)

Overview

In this assignment, you will:

• Review yet another version of the Beer Advisor application, focusing on the new code in the Model class, the JUnit test, and the Controller class to support those changes.
• Based on this Beer Advisor application, you'll write a new version of your H07 "product" application, using all the same "best practices".

Step-by-Step Instructions

Step 1: Reading

The following reading is recommended:

• SQL,MySQL
  • CISC474 SQL wiki page
  • SQL_GettingStartedWithSQLInCISC474 wiki page
  • CISC474 MySQL wiki page
  • Sebesta, Chapter 14
• JDBC
  • CISC474 JDBC Wiki page
  • Sebesta, Chapter 14

Step 2: Unpack, Review, and deploy the beerAdvisorDB.tgz source

You can obtain the source either as:

• a tarball, or
• as a zipfile

You may also browse the source in the H08 directory.

Download this to your development directory. You can do this directly on porsche in the same way that you did it for H07 (see H07 for details).

Before you deploy it, follow the steps to set up your own database. You can use the file beerAdvisorDB/sql/create_BA_Beers.sql as a model, and use the procedure outlined on the SQL_GettingStartedWithSQLInCISC474 wiki page.

Step 3: Create your own product advisor web app, following the same model

Now, create your own app, using the same product you used in H05,H06 and H07.

Step 4: Prepare a static page (on copland) to document your work

You'll create a static HTML page http://copland.udel.edu/~jstudent/cisc474/H08 that contains

• a link to the running servlet (on porsche)
• a link to the Javadocs (which may be housed on http://www.ecl.udel.edu, i.e. on your ACAD account)
• a link for downloading the tarball or zip file containing your source code (you'll create that in Steps 5,6)

Step 5: Prepare a tarball (or zipfile) of your source distribution

Here is some advice from the wiki on creating a tarball and creating a zip file

Step 6: Upload the tarball (or zipfile) to your download directory, and submit to WebCT

Be sure that the link you created in Step 4 works properly for downloading your source distribution.

Be sure when you go into WebCT, that you both upload and submit.

(The reason to have you also submit on WebCT as well as putting the code on the web is that submitting on WebCT (a) creates a permanent archive, (b) establishes a submission time, and (c) enables the TA to give you a grade.)

Grading Rubric (100 pts total)

Due Date: Wed, May 02, 5:00pm