YCP Logo Assignment 6: Web Application

Due: Thursday, Apr 29th by 11:59 PM

Getting Started

Download CS350_Assign6.zip. Import it into your Eclipse workspace (File->Import...->General->Existing projects into workspace->Archive File).

Note that you will see two projects:

  • CS350_JettyHSQLDB, which contains the HyperSQL Database and Jetty web application server
  • CS350_Assign6, which is the project containing the actual code of the web application

Import both projects into your Eclipse workspace.

Use of java.util

In this assignment only, you may use any class in the java.util package.

Your Task

This assignment builds on the web application you modified in Lab 5.

Your task is to extend the web application to support the following features:

  1. New authors can be added to the database. You should add a servlet that takes the last name and first name of an author as parameters, and adds the author to the authors relation.
  2. New books can be added to the database. Add a servlet that takes the first name and last name of an author, a title, and an ISBN, and adds a new tuple to the books relation. Note: your servlet may reject the request if the author last name and first name do not correspond to any author currently in the database.
  3. Books can be searched by author last name, or by both first name and last name. Add a servlet that takes first and last names as parameters, and returns all books that match those names. If the first name is not specified, then the servlet should only search on last name.

Add links to each servlet from the root document (generated by RootServlet).

Generated Author IDs

The database webapp.db in the CS350_Assign6 project has its author_id attribute declared as type IDENTITY. This is a special HyperSQL datatype which generates a unique integer id for each tuple inserted into the relation. The idea is that to insert a new author, we specify only the values of the author_lname and author_fname attributes, and the database will automatically generate a value for author_id that is distinct from any existing tuple.

HyperSQL allows your application to determine the last automatically-generated ID using the call identity() statement.

For example, let's say we add a new author using the following insert statement:

insert into authors (author_lname, author_fname) values ('Pelc', 'Ladislav');

The generated author_id value can now be determined by executing the query

call identity();

See the HyperSQL User Manual for more information about IDENTITY columns.

Hints

Note that when executing an insert statement using a JDBC PreparedStatement object, you should use the execute method to execute the statement rather than the executeQuery method. In general, executeQuery should be used only for queries (e.g., select statements) which return tuples.

Grading

Your grade will be determined as follows:

  • Add author: 35%
  • Add book: 35%
  • Search by author first/last name: 30%

Submitting

Important: you should submit only the CS350_Assign6 project. Do not submit the CS350_JettyHSQLDB project.

Export your finished project to a zip file (right-click on the CS350_Assign6 project, then Export...->Archive File). Upload the zip file to the marmoset server as project assign6. The server URL is

https://camel.ycp.edu:8443/

IMPORTANT: after uploading, you should download a copy of your submission and double-check it to make sure that it contains the correct files. You are responsible for making sure your submission is correct. You may receive a grade of 0 for an incorrectly submitted assignment.