Assignment 4 JSP, Servlet, and MVC - Database Integration This assignment is to be completed individually. No group work is allowed. This assignment is intended to familiarize you with using Databases to persist the data in your web application. Assignment Description: In this assignment, you will replace the hard-coded database with an actual database which entails some modifications to your previous JSP/Servlet MVC web application, according to the following specifications: Correct any errors identified or partial/missing functionality from the previous assignment. All structure, design, and content requirements from previous assignments are mandatory, unless explicitly updated in this assignment description. Use JavaBeans to implement the business layer of the application (model). Use JSP pages to present the view to the browser. Use Servlet pages to control the flow of the application. Functionality that does not follow the assignment specifications will not receive credit. Database Creation In addition to the standard WAR file for this assignment, you will submit a text file (plain text, NOT Word or RTF or PDF or any other fancy document format). This file will contain all of the SQL statements that you use to (1) create, and (2) populate your database. Your script will look very similar to the first part of the create_databases.sql script used to create the murach database used in the textbook exercises. (See the example table creation and data insert statements there for the murach database User and Download tables). Your database script file must be called: hw4_create_db.sql Create a database in MySQL to hold your application data. Create the necessary tables and fields to store the information needed in the application (e.g., users, items, offers, feedback). Database Population Your database should be populated with data according to the same requirements from the previous assignments (at least one user, at least 2 categories of item, with at least 3 items per category). Within the same script file for database and table creation (hw4_create_db.sql), add statements at the end of the script to insert your data items into each of the two tables (Item, User). Running the hw4_create_db.sql script should re-create / reset your database to its initial state with initial data (this means that at any point we can revert back to beginning). Code Changes/Additions Update your User JavaBean to accommodate the additional data fields for user ID and password. In the following descriptions, data types representing User or Item fields (code, name, etc) are representative. You may need to adjust some of the methods signatures, depending on the data types you have previously chosen for those fields in your JavaBeans. The bean types of Item and User should remain the same. For the following updates, you may use any of the approaches presented in the course to implement the database connectivity (single connection, or connection pooling with tomcat-dbcp). ItemDB - Refactor class ItemDB to get items from the database: addItem(String itemCode, String itemName, int categoryCode, String catalogCategory, String description, String imageUrl) - creates an item with the provided values and calls addItem(Item item) addItem(Item item) - method adds an item with the attribute values from the provided Item Bean to the database getAllItems() - this method returns an ArrayList of all the items in the items table from the database getItem(int itemCode) - this method returns a Item Bean for the provided item code Prepared Statements are preferred. UserDB - Refactor the class UserDB to get users from the database: addUser(String firstName, String lastName, String email, String address1, String address2, String city, String state, String zipcode, String country - this method adds a user with the provided values as the user attributes. Note: That the userId is not provided as this should be auto incremented to ensure each user ID is unique. addUser (User user) - this method adds a user with the attribute values from the provided User Bean getAllUsers() - this method returns an ArrayList of all the users in the User table getUser(String userID) - this method returns a User Bean for the provided user ID Prepared Statements are preferred. OfferDB create the class OfferDB to store and get offer details into and from the database: addOffer(String/int/ userID, String itemCodeOwn, String itemCodeWant, String/int/ itemStatus) this method adds an offer to the database. The userID here refers to the user that is making the offer and itemCodeOwn is the itemCode that this user owns and itemCodeWant is the item code they would like to get. updateOffer(String/int/ offerID, String/int/ itemStatus) this method updates the status of the offer in the database. FeedbackDB create the class FeedbackDB to store and retrieve the feedback users give for items and other users from the database: addOfferFeedback(int offerID, int userID1, userID2, int rating) this method adds a feedback that user with userID1 is giving for user with userID2. Both users have completed a swap. Using the offerID we can verify and link this to an offer. The corresponding database table would use the offerID and userID1 as primary keys. addItemFeedback(String itemCode, int userID, int rating) this method adds a feedback that user with userID is giving for item with itemCode. The corresponding database table would use the userID and itemCode as primary keys. As needed, you may also include support classes or configuration file definitions such as the examples from the textbook, particularly if you are using connection pooling: DBUtil helper class managing prepared statements, etc. (if used) ConnectionPool helper class managing the connection pool. The data source resource should be set up in the xml, the connection pool in the context.xml.