Project 20-1: Manage a list of countries

JAVA (NetBeans) with MySQL Database

Console

==========================================

Welcome to the Country Manager

1 - List countries

2 - Add a country

3 - Exit

Enter menu number: 1

India

Japan

Mexico

Spain

United States

1 - List countries

2 - Add a country

3 - Exit

Enter menu number: 2

Enter country: France

France has been added.

1 - List countries

2 - Add a country

3 - Exit

Enter menu number: 1

France

India

Japan

Mexico

Spain

United States

1 - List countries

2 - Add a country

3 - Exit

Enter menu number: 3

Goodbye!

==========================================

Operation

The application begins by displaying a menu with three menu items.

If the user chooses the first item, the application displays a list of countries that are stored in a database.

If the user chooses the second item, the application prompts the user to enter a country and then it adds that country to the database.

If the user chooses the third item, 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_country_table.sql file thats supplied. If this script isnt supplied, you can create your own SQL script.

Create a class named CountryDB that contains two methods: one that allows you to read a list of countries and another method that allows you to add a country to the list. For example:

public ArrayList getCountries()

public boolean addCountry(String country)

Create a class named CountryApp that displays the menu and responds to the users choices.

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

Possible enhancement

Modify the application so it allows the user to delete a country from the database.

//Create_mma_database.sql

-- 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';

//create_country_table.sql

-- create_country_table.sql

USE mma;

DROP DATABASE IF EXISTS Country;

CREATE TABLE Country (ID NUMBER PRIMARY KEY, Name VARCHAR(100));

INSERT INTO Country VALUES(1, 'India');

INSERT INTO Country VALUES(2, 'Japan');

INSERT INTO Country VALUES(3, 'Mexico');

INSERT INTO Country VALUES(4, 'Spain');

INSERT INTO Country VALUES(5, 'United States');

select * from Country;