Project 20-2: Manage customer data

Console

Welcome to the Customer Manager

COMMAND MENU

list - List all customers

add - Add a customer

del - Delete a customer

help - Show this menu

exit - Exit this application

Enter a command: list

CUSTOMER LIST

frankjones@yahoo.com Frank Jones

johnsmith@hotmail.com John Smith

seagreen@levi.com Cynthia Green

wendyk@warners.com Wendy Kowolski

Enter a command: add

Enter customer email address: test@test.com

Enter first name: Mick

Enter last name: Stipe

Mick Stipe was added to the database.

Enter a command: list

CUSTOMER LIST

frankjones@yahoo.com Frank Jones

johnsmith@hotmail.com John Smith

seagreen@levi.com Cynthia Green

test@test.com Mick Stipe

wendyk@warners.com Wendy Kowolski

Enter a command: del

Enter email for customer to delete: test@test.com

Mick Stipe was deleted from the database.

Enter a command: list

CUSTOMER LIST

frankjones@yahoo.com Frank Jones

johnsmith@hotmail.com John Smith

seagreen@levi.com Cynthia Green

wendyk@warners.com Wendy Kowolski

Enter a command: exit

Bye!

Project 20-2: Manage customer data (cont.)

Operation

This application begins by displaying a menu with five choices: list, add, del, help, and exit.

If the user enters list, the application displays the customer data.

If the user enters add, the application prompts the user to enter data for a customer and saves that data.

If the user enters del, the application prompts the user for an email address and deletes the corresponding customer.

If the user enters help, the application displays the menu again.

If the user enters exit, the application displays a goodbye message and exits.

Specifications

Create a table in the mma database described in chapter 19 to store the necessary data. To do that, you can use the SQL script stored in the create_customer_table.sql file thats supplied. If this script isnt supplied, you can create your own SQL script.

Create a class named Customer that stores data for the users email address, first name, and last name.

Create a class named CustomerDB that contains the methods necessary to get an array list of Customer objects, to get a Customer object for the customer with a specified email address, to add a row to store the data in a Customer object, and to delete a row for the specified email address.

Create a CustomerApp class that works as shown in the console output. This class should use the Customer and CustomerDB classes to work with the customer data.

Use the Console class described in chapter 8 or a variation of it to get the users entries.

Use the StringUtil class described in chapter 9 to use spaces to align the columns of data.

Possible enhancements

Add an update command that lets the user update an existing customer. This command should prompt the user to enter the email address for a customer. Then, it should let the user update the first name and last name for the customer.

Add a method to the Console class that uses string parsing techniques to validate the email address. At the least, you can check to make sure that this string contains some text, followed by an @ sign, followed by some more text, followed by a period, followed by some more text. For example, x@x.x would be valid while xxx or x@x would not.

//Provided to create mma database: product-- create and select the database DROP DATABASE IF EXISTS mma; CREATE DATABASE mma; USE mma;

-- create the Product table CREATE TABLE Product ( ProductID INT PRIMARY KEY AUTO_INCREMENT, Code VARCHAR(10) NOT NULL UNIQUE, Description VARCHAR(255) NOT NULL, ListPrice DECIMAL(10,2) NOT NULL );

-- insert some rows into the Product table INSERT INTO Product VALUES (1, 'java', 'Murach''s Java Programming', '57.50'), (2, 'jsp', 'Murach''s Java Servlets and JSP', '57.50'), (3, 'mysql', 'Murach''s MySQL', '54.50'), (4, 'android', 'Murach''s Android Programming', '57.50'), (5, 'html5', 'Murach''s HTML5 and CSS3', '54.50'), (6, 'oracle', 'Murach''s Oracle and PL/SQL', '54.50'), (7, 'javascript', 'Murach''s JavaScript and jQuery', '54.50');

-- create a user and grant privileges to that user GRANT SELECT, INSERT, DELETE, UPDATE ON mma.* TO mma_user@localhost IDENTIFIED BY 'sesame';