New: important clarification for Assignment 6
Note (11/20/09): a few new details (mostly about CLASSPATH) have been added to the JDBC Notes document.
Description
This Project will implement a cooperating client-server database system for both the Server and the Client. Base your Server program on the Server program supplied on this Web site. Write your own applet client. Its GUI design may be simple, but it should be clear, nicely organized, and should not require excessive scrolling, etc.
See the list for the port number for your server here.
The application server program should connect to the mysql database (name given at the end of this assignment). Each programming team will have its own database to manipulate. See the course Website for database names. Each database contains a single table whose name is cust, so this is the table name you should specify in all of your SQL queries (e.g., select * from cust where name = 'nameString'). The cust table has 4 columns. These are:
Column Number Column Name
Data Type
1
name
char(20)
2
ssn
char(10)
3
address
char(40)
4
code
int
The ssn column has a unique primary index on it and thus all values in this column must be unique. All database names (table name, column names) are lowercase and must be coded as such in SQL statements.
These databases might not be empty so your first transaction type to implement might be GETALL (see below).
Your client-server pair should perform the following operations on the database.
ADD a new customer. The user has entered information into 4 TextFields on the client applet and has pressed an add button. The client should check that all fields are filled in before attempting to add. If one or more fields are blank, display an error message (in a Label) and do not send an add request to the server. The server program should insert this new information into the database and inform the client of its success. The client applet should display a message in a JLabel indicating the result of the transaction. (Note that attempting to insert a record with a duplicate ssn will raise an SQLException.)
UPDATE a customer. The user has entered an ssn, and one or more of the other fields (name, address, or code) is not blank. When the user presses the update button, the client should verify that ssn and at least one field are non-blank, and send the information in all the fields to the server. The server program should change the information for all (and only) non-blank fields for this ssn and report to the client on its success or failure. The client applet should display a message in a JLabel indicating the result of the transaction. The server should not do this by reading the existing record and using its contents to "complete" the update info in the event that the update info is incomplete. Rather it should prepare a custom insert command based on what is present. In this case, there are only a few possible combinations (6) so this task is not too complex.
SEARCH for a customer by SSN. The user has entered an ssn into the ssn TextField and has pressed the search button. The server should search for the record with the customer's ssn and return the information back to the client for display in the TextFields. If the customer is not found, have the server program notify the client of this and the client can display a "Not Found" message in its Label.
DELETE a customer. The user has provided an ssn in the ssn TextField (either by directly entering it or by doing a prior search) and now has pressed the delete button. The server should attempt to delete the record with that customer's ssn and should inform the client of its success or failure. The client applet should display a message in a label indicating the result of the transaction.
GETALL - when the user presses this button, the program should retrieve and display all the database records in a JTextArea. This JTextArea should be wide enough to display the data in one record, and must support vertical scrolling (see the JScrollPane class). These records need not be neatly formatted - just dump the raw information, one record per line.
Development and Testing Details
See JDBC Notes for specific Driver loader notes for mysql on turning.
Development and testing of client-server apps is somewhat awkward. To test a new version of the server, you will have to
When testing the client applet on a WWW browser, you may have to do one or more of the following:
to test a new version. It can save you a lot of work if you put something visibly different in a new version of an applet so you're always sure which version you are actually running. For example a single letter prefix on a Button caption will serve this purpose for testing.
Application Protocol:
Use Object Serialization to send messages back and forth between the client and the server. To minimize communications, include a transaction field in the objects sent from the client to the server to indicate add, update, delete, search, or list (as opposed to sending a message indicating the transaction type followed by a separate message with the transaction details). You will need to devise a way for the server to send multiple records from a GetAll request and for the Applet to retrieve that number of records.
When mysql was installed, certain default installation options were put into effect. One of these is that the mSQL server (the program that receives requests from the driver and sends back the results) is listening on port 3306. We left this option in effect, so this is the port your Java application server program should connect to; e.g.,
String url = "jdbc:mysql://courses:3306/JavaCustXX";
where NN = your assigned database number. See the course Web site for Database names.
You have to make sure that your CLASSPATH environment variable is set in order to successfully run the server.1.) to use the classes from Java 1.6
2.) to use the classes from the the driver classes .jar file
3.) to use the classes from your directory where your java
application resides
So, edit the .bashrc file that is on your root directory, to include the following
single line:export CLASSPATH=.:/usr/share/java/*
Note: the initial . before the colon is for the current directory (or working directory) where your applet and server are located.
Be very careful that you type this correctly.
Due Date
You will need to demonstrate the system to your instructor in his/her office when it is ready to be graded. You should start the server before you come to demo the system. Your instructor will tell you the exact final deadline.