CSCI213
Autumn Session, 2007

Assignment 4: JDBC and networking

Before starting this assignment you should work through the demonstration code and associated exercises.

• Aims
• Objectives
• Tasks
• JDBC part
• Networking part
• Submission

Aims

Objectives

On completion of this assignment, you should be able to:

• use JDBC to add data to tables in a database, and retrieve selected data from the database;
• write simple client-server applications that use socket level connections for data exchange;
• explain the role of java.sql classes such as Connection, Statement, and ResultSet, and explain how a Java program can use JDBC to access a database;
• explain the use of java.net.Socket and java.net.ServerSocket in client-server applications, and explain how serial and threaded server applications work.

The programs that you are to deploy will re-use the GUI code that you wrote for the part of Assignment 3 that involved viewing of patient record data. If you did not successfully complete Assignment 3, you may use GUI code written by a friend. All such use must be acknowledged.

Deployment; SQL tutorials

It is intended that this assignment be completed usiong the University Oracle database. However, if you wish, you can use your own computer system and database, provided you properly document details in your report.

Linux users will have the Postgres database system installed as part of their Linux set-up. You need to find the JDBC database drivers for Postgres (there should be a Java archive - .jar - file somewhere in the Postgres directory). You arrange that these driver files be include on your classpath when running your programs.

Most Windows users will have Microsoft Access. This can be employed as your database via ODBC and the Sun jdbc-odbc drivers (these are included in JDK). You first create a database in Access. You then use the Control-Panel/Administrative Tools/ "DataSources" control to associate an ODBC name with this Access database. Where my example code showing the setting up of an Oracle driver has code like:

public static final String dbDriverName = "oracle.jdbc.driver.OracleDriver";
public static final String dbURL = "jdbc:oracle:thin:@wraith:1521:csci";

private static final String dbDriverName = "sun.jdbc.odbc.JdbcOdbcDriver";
private static final String dbURL = "jdbc:odbc:MyODBCAccessDB";

where MyODBCAccessDB is actually the ODBC name that you assigned to your Access database. (The sun.jdbc.odbc classes are included in the standard releases of Java.)

The MySQL database can be used with both Windows and Linux and is the most popular for personal systems. The Apache Derby project is another open-source database system.

If you have a fast Internet access link at home, and a policy that allows unlimited downloads, you can pick up Oracle or DB2 (IBM) databases for free. (These downloads exceed 600Mbyte; they are developer editions but are really pretty complete). Find them at the Oracle and IBM (DeveloperWorks) sites. Microsoft's site has a developer version of SQLServer available for free download.

The School's Oracle database is firewalled. You cannot use it from off-campus.

Only a minimal amount of SQL knowledge is required - enough to define a table, insert an extra row, and submit a query. The lecture notes and demonstration examples should suffice. There are a number of SQL tutorials at the WDVL site.

Tasks

For this part you adapt the program that you wrote for Assignment 3. The data on patients are now to be stored in a table on your Oracle (or other) database account.

As part of this exercise, you will first write a little Java program that creates the datatable (by submitting a create table patients ( identifier number(10), name varchar(32), ..., hormone3 number(10,2)) SQL statment), and which then reads the patients data file from Assignment 3 using the data to populate the rows of your table.

Your main program will use the same GUI interface as that which you constructed for assignment 3. The data on patients are no longer loaded at startup. Instead, each "search" request for a subset of the patients will involve database accesses.

Your program should open a database connection at startup. The connection will be closed when the program terminates.

The "info" panel is similar to that in Assignment 3. It allows a user to select the fields that are to be shown in the table-view, to place constraints on gender and age of patients of interest, and to specify a sort criterion.

The gender and age constraints are to be used to construct the SQL query. It is simplest to make the SQL request retrieve complete rows and use these data to fill in the fields of PatientRecord objects. The collection of selected records can be sorted using comparators as before. (You can incorporate sort criteria and selective column-retrieval in your SQL request. You are not required to generate such more elaborate requests.)

Traditionally, there has been extensive use of simple two-tier client-server systems where the 2nd tier is the database engine. The program completed in the last part is an example of this genre.

Newer systems are more typically implemented with a multi-tier (generally three-tier) architecture.

The client does not interact directly with the database. Instead, it interacts with a "middleware" system. It is the "middleware" component that contains code to invoke database operations.

There are a number of reasons why three tier systems are preferred, including:

Security

If you look at the for your two-tier program, you will see that it contains the "user name" and "password" for a database account. This information is available on all systems that run the client - with the inevitable effect that that database account is compromised. Different clients can be easily written to access the same tables and change the data that are present.

Complexity of client

Of course the example is only a toy; nevertheless its code includes GUI components, and the database code. In this example, there are no real business processing steps to be performed; more typically, the code would require functions ("business logic") that validate requests and filter responses. In more realistic applications, the total amount of code can become large leading to bloated, slow to load, clumsy clients.

Ease of update

It is quite common for organizations to have to change details of the business logic. If this business logic is in the clients, then the organization must ensure that all users of the client update their copies of the program. Often there are problems resulting from users running outdated versions of clients.

In a three tier system, you have a clearer partitioning of responsibilities:

• Client: predominantly user interaction, plus simple data exchange with middleware component;
• Middleware: business logic, database access code, and often additional functionality related to handling multiple clients, balancing loads etc.
• Database: data storage and retrieval.

In real systems the "middleware" component can be elaborate. This is where you find "transaction monitors" like BEA's Tuxedo product, IBM's Websphere product range, "CORBA" servers from Iona or Inprise, systems employing "Enterprise Java Beans", and Java RMI servers. Communications between client and middleware component utilize rich communication protocols like RMI or CORBA's IIOP.

In this assignment, we are looking for something much simpler! The GUI program is split with code for the user interaction remaining in the client part while the database code moves to a "middlware server" program. Communications between client and server use "sockets" and data streams (ObjectStreams) with the lower levels of communication being handled by the TCP/IP libraries.

The client program:

1. Is given the hostname and port number of the server program as command line arguments
2. Opens a connection to the server, wrapping the socket I/O streams in ObjectInput and ObjectOutput streams (if there is a failure in the attempt to open the streamed connection, the program terminates after printing an error message)
3. Creates its GUI
4. Request data (defining age and gender constraints, and other information if attempting more elaborate SQL queries) are taken from the Info panel and combined into a request that is sent to the server
5. The response should either be an error/exception or a collection (possibly empty) of PatientRecor objects that satisfy the SQL search criteria. Any error response should be displayed in the messages panel. If not already sorted, the collection of Patient records should be sorted. The selected fields from the patient records should then be displayed in the "tables" panel.

The server program should

1. be based on the simple serial server model
2. should own a connection to the database that is created when the program starts and which is then kept open
3. should handle client requests in a function that parses the data in each request, constructs and runs a SQL search request, and uses the retrieved ResultSet to fill in a collection of Patient record objects.

Submission

The due date for submission will be announced in lectures; the date will probably be around the end of week 10 of session (currently set for May 11th).

As usual, you will submit a report on your work - formatted as a PDF file. This should contain two main "chapters" - one for the two tier application that directly acesses the database, and one for the three-tier version with client-server-database.

The "chapter" on the two-tier part of the assignment should have:

1. A listing of the little program that creates the patients data table (using a "create table ..." SQL statement submitted via JDBC), and which then populates the table.
2. Listings of the new/modified classes for the 2-tier program.
   • The main driver code, setting up the database connection and creating the GUI and data classes should be listed.
   • The "Info" panel class will have been changed, so it will need to be listed.
   • The "data object" class, that works with a collection of patient records, will have been changed. It now prepares and runs SQL queries and uses the result data to populate the collection of records that are to be shown in the GUI table-panel. This class should be listed.
   • Other classes, like that handling the tab-pane structure, the table-panel etc are unchanged and do not need to be listed.
3. Some summary information should be included that shows the structure of the project and its libaries.
4. Screen shots, and tracers (including tracers showing the SQL queries run) should be included as evidence that the program runs correctly.

The "chapter" on the two-tier part of the assignment should have:

1. Complete listings of all server-side code.
2. Listings of the client-side code that has been changed.
   • The main driver program now establishes a network connection to the server rather than a direct connection to the database.
   • The code handling a search request composes request details in a message that is sent to the server.
     The server response should be a collection of patient records for sorting and display.
   All "dead-code" (code relating to direct use of the database) should have been stripped from the client.
   
3. Some summary information should be included that shows the structure of the project and its libaries. A project should only include those libraries that are actually required.
4. Screen shots, and tracers (including tracers showing the SQL queries run) should be included as evidence that the program runs correctly.

Working on banshee, you use the turnin command to submit your file:

turnin -c csci213 -a 4  Report.pdf