Project assignment 2: a web interface to a database

NEW! Intermediate report: On Tuesday, March 5th , before 1pm, please submit a short note about the state of your work: what you have finished, what are you currently working on, you may also mention main difficulties that you have encountered. You can send it to me by e-mail (cc all project members) or as a brief paper note in class.

This assignment specifies the list of features that you must implement, it also makes suggestions about other things that you might want to implement. Each project design is different, your project may require features which are not listed here or it may not need something that's listed as mandatory. If that's the case with your project, please get in touch with me to discuss implementation of your design.

This is one of the key parts of the project. In this part you will need to:

• Part 0. Create the database that you have designed for the first assignment.
• Part 1. Write a Java program that populates your database by data stored in files, use this program to populate the database (see below).
• Part 2. Write code for Java applet that performs searches as a search engine on your web site.
• Part 3. Write another applet that processes an order form, i.e. checks if the order can be filled, and if yes, enters all the necessary data into the database.
• Part 4. If your site has customer registration, you will also need to write an applet that processes the registration form.

Technical details

1. Solutions for parts 1 to 4 must use JDBC to connect to the database.
2. All data should be entered and modified via SQL queries.
3. Parts 2-4 should come with a GUI which will allow the user to choose search options or fill forms and will display the results. The GUI must be done using the swing package.
4. The solution for part 1 may be either an applet or an application. The solutions for parts 2-4 must be applets. In particular, they should be declared as (for instance):

   
   public class MySearchEngine extends JApplet implements Runnable()
   

   The reason for this is that the applets will be attached to web pages.
5. Since your searches are likely to be repeated many times with different parameters, you should use PreparedStatement.
6. To process an order form you are likely to need several updates and selects (to check the quantity of each product, decrement these quantities if the order can be filled, update the order table, and so on). Use transactions to guarantee that the consistency of data in the database.

Part 0 (or see an alternative in part 1). Creating the database

Alternatively you may create the tables automatically in the program that populates the database (see below).

Part 1. Writing a program to populate the database

Besides just saving time, there is another good reason to populate the database by data from files: no database is 100% reliable, and ours is not an exception! Having a program that enters data from files means that you don't need to retype it by hand every time the database crashes or you accidently have changed the information in a way from which it's not easy to recover.

What do you need to do for this program: write a Java application or an applet that inserts data contained in a file or files into the database via JDBC queries. The files may contain parts of information (s.a. a file with just names, a separate file with dates of birth, and so on) or the entire rows of data. Your program does not have to be general: it should just handle your files and your database tables.

When the program is done, use it to populate the database. You may, if you want, create the tables right in the program, or you may use the McKoi Query Tool to create the tables.

Please make sure to keep all of your data files, it's very likely that you will need to reenter the database data at some point.

Part 2. Writing a search engine for the user

The user probably will have a choice between several categories (for instance, Fiction/Non-fiction in an online bookstore), and have several search options (s.a. by author, by title, or by subject). You need to implement these choices in your applet, probably as check buttons (s.a. JCheckButton or JRadioButton) or as a list (s.a. JList). After the user makes a selection, he/she enters the search word in a text field and presses "Return" or clicks on a button. The applet then displays the results of the search.

As an additional feature, you might want to offer to narrow the results of a query based on another selection, which effectively will allow the user to search on the subject and the author (again, using a bookstore example). This feature is not required, however.

Processing request for information about an order is similar, except for you need the user to identify him/herself uniquely by user ID or to enter the order number. Under no condition should your database display order information for more than one user at a time! You might also think of a way of generating order numbers so that they are not sequential integers, because a curious user with an order number 123 might also want to check the order number 124. The order number should be generated by the order processing form (see below).

The order information should display, at least, the shipment date and other related information. You might not want to display the contents of the order for security reasons. Think carefully about other security concerns when processing order status requests. While encryption is not part of this project assignment, you should start making a list of data transfers (between your web site and the user) that you think should be encrypted.

Since all your queries will be very similar, please use PreparedStatement class so that your queries can be precompiled on the database server.

Ideally your search engine should be such that if a query takes too long (which could be due to a busy connection to the database server), the user should be able to stop the query by clicking on a button. This would require that your query runs as a separate thread. You are not required to implement this feature, but it's something worth trying!

Part 3. Processing order and registration forms

Processing an order form will require you to do the following:

1. check customer information, i.e. that the customer has supplied his/her name, address, credit card number, and all these fields make sense (no letters in the credit card number, no digits in a name, and so on).
2. check product information, i.e. that the order can be filled.
3. if all the customer information checks out OK and the order can be filled, then you need to enter all the necessary information into the database, generate the order number, and return it to the customer.

You may need to have a special case for a returning customer, where the customer data is already stored in the database. Presumably this option will require a password.

After the order has been succesfully processed, you may display a message on your applet: "Thank you for your order!" or something like this. You may use a label to do it.

Processing a registration form is very similar. Again, please keep in mind security considerations.

Please feel free to add graphics to your GUI, you can display it using labels (see examples in java.sun.com JLabel tutorial. However, your programs will be mostly graded on the functionality, convenience of use, and the quality of code.