This assignment is designed to guide your understanding of the various technologies you will use in the course project as well as help to fill in any gaps you might have. All code code submitted must be fully functional and working. The course staff will be executing all code. Failure to be able to execute code will cost points.

Summary: For this assignment you will be implementing a simplified CD purchasing system. The user first is presented with a screen that asks them for their user name and then shows a list of CDs for purchase. The user can then select the option to purchase a CD and is shown a success/failure message. The user interface must be in Swing, the information handled by servlets, and the data stored in an Oracle database. You do not need to create a user login system for this application. Any text that the user enters for their user login (other than a blank string) is fine. There should be an error message returned by the application server if their are no quantities of the CD selected available.

1. Database Layer: Design a database schema to store CDs and orders. Each CD should have appropriate attributes such as genre, price, quantity available, etc. The CDs should be stored in a 'denormalized' manner; that is, all the information about a CD is in the table (e.g., you do not need to lookup genre information in a genre table). For the orders, you need such features as a CD id, user name, and time of purchase. As previously mentioned, you do not need a user login system, so there is no need for a users table; just inserting a user name or id in an order record is sufficient.

What you should hand in: your database schema implemented as table create statements in Oracle that should include proper primary keys, Oracle sequences for appropriate auto-generated Ids, and at least 5 attributes per table. In addition, write 10 insert statements that insert 10 CDs of your own choosing. All the information should be stored in a text file called db.sql.

2. Middle-tier Layer: Implement 2 servlet calls and 3 DAO methods that handle the display of a list of CDs to the customer and the purchase of a CD. The DAO methods must be implemented using JDBC. The general behavior of the servlets and methods are defined below. Keep in mind you may test these methods in a web browser just by typing in the appropriate URLs to http://localhost:8080/...

• CDDAO.getListOfCDs() Queries the database using JDBC for all CDs in the system and returns them as list of CDs. You may want to implement a CD class that stores the information for each CD in an easy to access POJO (Recall this is a Java class with simple objects and their getters/setters).
• CDDAO.isCDAvailable(int cdId) Determines if there are any CDs available for purchase given a CD id. The JDBC code can return the quantity number; then your Java code can return a boolean that tells whether or not this number is greater than zero.
• OrderDAO.placeOrder(String username, int cdId) Given a username and a CD id, insert a CD into the order table. Note the method may insert other information not passed to it, such as the time of creation or a system id. Your method should also perform an update in the CDs table that decrements the available quantity of the CD.
• CDViewServlet Returns a list of CDs from the database separated by some specified delimiter character. You do not need to be strict in how you separate data (e.g., commas and semi-colons are fine). Make sure that none of the CD names that you inserted in the previous part contain the delimiter character.
• PurchaseCDServlet Checks if a quantity of CDs is available and if so, places an order and returns a success message, else returns an error message. Please also verify manually in SQLPlus that the database order was placed.

3. Presentation Layer: Implement a front end in Swing that first asks the user for their name and then displays a list of CDs in the system that are available for purchase. Recall that the user name entered does not need to be verified in the database, so no servlet calls are required, but it should be stored in some variable to be used later when purchases are made (this is referred to as session information). The list of CDs cannot be text output from the servlet; instead it must show the CDs in a neat, easy-to-read format. Each CD should have a purchase button that executes the purchase servlet and returns a success/error message either as a popup or a new screen.

Some Tips:

• Always pass the bare minimum data between each layer. For example, you do not need to pass the text of a success/failure message for purchasing CDs but rather a 0 or 1. The Swing application then can read the 0 or 1 and display its own output message based on this information. Often times 0 indicates success since other values like 1,2,3,4... may be used for multiple (different) types of errors.
• There are plenty of online resources for creating Oracle tables such as link 1 or link 2. Also keep in mind that in creating tables, you need to decide a data type for each column that could vary based on its usage.
• Write as much reusable code as possible. For example, you only need to write code that connects to the database once.
• The only difference between a normal jar and an executable jar is the existence of a file containing the main class information. If you export the jar in Eclipse, it has the capability of creating this file for you and inserting it into the jar.
• Although it is possible for your swing application to check quantities of CDs ahead of time to prevent the purchase CDs that are not available, it is not suggested for this assignment. Consider the situation where a user displays a list of CDs, waits 10 minutes before making a purchase, and during that time the CD they wanted was purchased by someone else. The swing application was never notified the CD is no longer available, therefore the system always needs a way to check quantity available and return an error regardless of whether or not the swing application verified this ahead of time.

4. Extra Credit: Implement a user parameter in the Swing application, to be passed to the servlet and the database tier, that specifies an ordering for the CDs returned to the user. The parameter can be hard-coded in the Swing application and represents a CD type. Then, when the query to retrieve the CDs is executed in the DAO class, an "ORDER BY type" clause is added and type is replaced with the text string for the column, based on the user's preference.

If you have questions about this assignment, please contact Scott Selikoff (selikoff@cs.rutgers.edu).