Please, this assignment is about Database integration, we have worked on html web page creation where we create html web pages, then we move on to the MVC application with servlet and JSP, then Session tracking and now we are on Database integration.

Since our projects are stage projects, our current work consists of upadating the privious data.

Please, here is my itemDB: file we were asked to use this itemDB to get items from the database

* To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package dataBase;

import JavaBeans.Item; import java.io.Serializable; import java.util.ArrayList /** * * @author ESI */ ; public class ItemDB implements Serializable { /* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ //This method returns the DB items public static ArrayList getItems() { ArrayList items = new ArrayList<>(); items.add(new Item("Mix-fashion", "Bracelet", "Images/Mix-fashion.png")); items.add(new Item("Diamond Yellow Gold Braelet", "Bracelets", "Images/Diamond Yellow Gold Bracelet.jpg")); items.add(new Item("Diamomd Fashion Earrings", "Earrings", "Images/Diamond Fashion Earrings.jpg")); items.add(new Item("Extraordinary Diamond Earring", "Earrings", "Images/Diamond Fashion Earrings.jpg")); return items; } private Object itemID;

Here is my UserDB file:

package Database;

import JavaBeans.Item; import JavaBeans.SwapOffer; import JavaBeans.User; import JavaBeans.UserItem; import java.io.Serializable; import java.util.ArrayList;

public class UserDB implements Serializable { public static ArrayList getUsers() { // Create two users and add UserItems to their profiles User user1 = new User("Essi", "Kpoedzu"); User user2 = new User("Jane", "Doe"); user1.addUserItem(ItemDB.getItemById("Mix-fashion"), UserItem.AVAILABLE); user1.addUserItem(ItemDB.getItemById("Diamond Fashion Earring"), UserItem.PENDING); user1.addUserItem(ItemDB.getItemById("Diamond Yellow Gold Bracelet"), UserItem.AVAILABLE); user2.addUserItem(ItemDB.getItemById("Antique French Diamond Bracelet"), UserItem.PENDING); user2.addUserItem(ItemDB.getItemById("Extraoridinary Diamond Earring"), UserItem.AVAILABLE); user2.addUserItem(ItemDB.getItemById("Diamond Earring"), UserItem.PENDING); // Create two SwapOffers: one from the first user, the other from the second createSwapOffer(user1, user2, ItemDB.getItemById("Mix-fashion"), ItemDB.getItemById("Antiqu French Diamond Bracelet")); createSwapOffer(user2, user1, ItemDB.getItemById("Diamond Earring"), ItemDB.getItemById("Diamond Fashion Earring")); // Add the two users to a list and return the list of users ArrayList users = new ArrayList<>(2); users.add(user1); users.add(user2); return users; } // This is in place of a getUserById() method public static User getUserByFirstName(String name) { User user = null; for (User u: UserDB.getUsers()) if (u.getFirstName().equals(name)) user = u; return user; } // Method to create swap offers between two users public static void createSwapOffer(User requester, User receiver, Item requesterItem, Item receiverItem) { /* Creating a swap offer adds the offer in both users' SwapOffer lists. * SwapOffer objects contain (other User, item owned, item to swap for, status of offer, and whether or not the offer originated from the user) */ requester.addSwapOffer(new SwapOffer(receiver, requesterItem, receiverItem, true)); receiver.addSwapOffer(new SwapOffer(requester, receiverItem, requesterItem, false)); }

public static Object getUser() { throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates. } }

Java Track Assignment 4 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. Changes from Assignment 2 Use the ItemDB java class provided to retrieve items form the database. Make the necessary changes throughout the project to use this class to retrieve items from the database. Changes from Assignment 3 Use the UsersDB java class provided to retrieve users form the database. Make the necessary changes throughout the project to use this class to retrieve the user information from the database Optional Extra Credit (10 Points) Similar to assignment three you have an opportunity to earn some extra bonus points by doing some research. As weve discussed before there are many approaches and technologies to web-based application development. Assignments 4 focuses on data persistence. Doing some research, youll find other technologies/approaches that can be used to persist data in the MVC design architecture. Choose one that interests you the most and provide a short description, some resources and examples of this technology, advantages/disadvantages and any other information you think is relevant to our course. What to submit using Canvas (Email submissions will NOT be accepted): HW4.zip - An archive of the entire web application (project) stored in a standard ZIP File, you must ensure that all the files are included as part of the archive. hw4_create_db.sql SQL script file to create and populate your Item and User tables. HW4Info.pdf PDF document with the following assignment information: Screenshot of the applications home page displayed in your browser. Explanation of additional features, if any. Explanation of status, stopping point, and issues if incomplete. Discuss the easy and challenging parts of the assignment. How did you overcome all or some of the challenges? Note: Do not include the info PDF or the database script in the project folder ZIP file. You must submit them separately. (optional) HW4Bonus.pdf PDF document for the bonus part of this assignment