This is a three-tier web based application that uses servlets and JSP technology running on a Tomcat container/server to access and maintain a persistent MySQL database using JDBC
Detailed Description: I utilized the suppliers/parts/jobs/shipments database named project4 as the back-end for my web application. The schema consists of four tables:
suppliers (snum, sname, status, city) – information about suppliers
parts (pnum, pname, color, weight, city) – information about parts
jobs (jnum, jname, numworkers, city) – information about jobs
The shipments table uses a composite key (snum, pnum, jnum), with referential integrity enforced via foreign keys. This ensures no shipment exists unless the referenced supplier, part, and job already exist.
The first tier of my application is an HTML landing page that authenticates users through a servlet. This servlet validates user credentials against entries in a credentialsDB database that contains a usercredentials table. If authentication fails, access is denied. If successful, users are redirected to one of four JSP-based front-ends:
Root-level users – can execute arbitrary SQL commands (restricted to queries, insert, update, replace, and delete).
Client-level users – similar to root, but with more limited privileges.
Data-entry users – enter new records through forms; commands are issued via PreparedStatement in the background.
Accountant-level users – select and run reports, which execute remote stored procedures via the CallableStatement interface.
The root-level and client-level pages provide a simple form with three buttons: Execute Command, Reset Form, and Clear Results. The data-entry pages allow only form submissions with two buttons: one for submitting and one for clearing. The accountant-level page provides a selection menu for running stored procedure reports. I implemented four properties files to handle database connections: one each for root, client, data-entry, and accountant users.
In the second tier, I implemented servlets to handle SQL commands. For root-level users and data-entry users, I also implemented business logic on the server side: whenever a shipment record is inserted or updated with a quantity ≥ 100, the status of every supplier who has any shipment with quantity ≥ 100 is increased by 5. This logic runs in the servlet on the Tomcat server, not in the database (i.e., no triggers).
The client-level servlet handles SQL commands the same way as the root-level servlet but does not include the business logic, since client-level users cannot perform updates on the database.